Voith's CODE

November 24th, 2023: The installer recognizes the latest versions of HCL Notes. Plus minor fixes

Previous versions of Notes Import Export Library had a problem recognizing the latest versions of the HCL Notes client. That has now been fixed. Remember, IEL is still only for 32-bit versions of HCL Notes clients, meaning that you won't be able to use IEL if you use the 64-bit version of the HCL Notes client.

Also, previous versions of IEL had a problem with the file name, if you didn't specify the file extension at the same time. Now you can enter just the file name if you want to :-)

Aug 4th, 2020: Important fix to Notes Import Export Library if you have percent-symbols in column-names

If you had an Excel-spreadsheet with column-headers containing percent-symbols (like for example "Whatever %" or "% Complete"), IEL would have problems saving and loading Import- or Export Specification files. If you didn't save specification file, the import- or export, worked as designed.

The symptom of problem with percent-symbols, was that IEL couldn't map the fields properly when you loaded the specification files.

May 7th, 2020: Important fix to Notes Import Export Library if you work with time/date fields

Thanks to a customer which reported erroneous behavior when importing dates from an Excel spreadsheet, I was able to track down a subtle bug which only occurred when the Windows' Region was set to "English (United States)". The new release has been tested on IBM Notes client 9.0.1 and HCL Notes client 11.0.1, and on platforms Windows 7 x64 and Windows 10 x64.

May 5th, 2020: Minor fix in the installer to detect HCL Notes 11 properly

The IEL installer contained a small bug which fooled the IEL installer to not detect HCL Notes 11 properly. The symptom was that no Notes client installations were listed during installation, like this;


It should (... and after today's fix) look like this;




January 2nd, 2020: New version of Notes Import Export Library (IEL). HCL Notes version 11 support

IEL should now install and work fine with the 32-bit version of the HCL Notes Version 11 client.

March 17th, 2018: New version of Notes Import Export Library (IEL). Several new features, bug fixes and enhancements

New features:

• Much better formula editor. The old one was very basic, and it couldn't word wrap the content. This made it pretty awkward to edit anything but very short formulas. The new editor word wrap the content, and allow formula to be contain multiple rows. Remember that you can resize the dialog box too.
  
• Formulas can now reference source fields by using the formula
  
  $(Field:<source field name>)
  
  Very useful for the next new feature
  
• New formula @VCGETCRC32(blablabla) ... which generate a CRC32 value from the text you supply. Very handy if you want to combine the source field content by using multiple $(Field:<fieldname>). I use this to create "import keys" of records that don't have obvious keys :-)
  
• New variable that can be used during imports; $(CURIMPORTRECNO) will be replaced with the current import record number.
  
• Speaking of variables, you can use a whole range of variables in your formulas too.

Fixes:

• Fixed a bug during import with keys that uses a formula
  
• Fixed a bug with commas during import
  
• Fixed a bug during import with "Compute with Form" enabled. If you have logging turned on, you will now see much better details about what really happend.
  
• Fixed a bug in formula dialog box which only occured on Windows 10 x64. Windows has changed the way the resizing worked a little bit from Windows 7 to 10, and that unfortunately made IEL crash (!)

Enhancements:

• Import Export Library knows to how log some events to a file. It has for a long time, but it is somewhat difficult to turn the logging on or off since the log-switches are stored in the registry. One later enhancement is that you can use variables for the log file name too.
  
• Better logging of imports when keys are used. If the log is on during import, and you have the highest loglevel set (which is 5) you will see details about the key that has been inserted or updated into Notes. Makes it easier to identify the records.

Hope you enjoy this version!

January 6th, 2017: Notes Import Export Library (IEL) now supports update of existing Notes documents from your imported Excel or CSV documents!

In order to update any existing documents, IEL must know something about what target field you want to use as key. Simply right-click any of your target fields and choose the Set as key-menu


IEL also needs to know which Notes view you want to use as lookup view for the selected keys. As soon as you choose Set as key, IEL will enumerate all existing views in the target database. Note that this operation takes some seconds. The worst I've seen has been up to a minute, but for most normal databases, the enumeration is done within seconds.

When IEL knows your views, it displays a dialog box where you can choose which view to use as lookup view. To help you, the selection formula is also shown;



When you click OK, IEL will show you a small key as the field icon in the target fields list;


When IEL imports the records from either Excel or CSV, it will check the lookup view for any existing documents with the current key-field value. If a match is found, the target Notes document is updated.

Please note that you need to know something about your views to use this feature effectivly. The view must have at least one column sorted. IEL can at the current time, only use a single key column in the view. If you have multiple fields making up a formula field, the view key-column must have a combination of these fields.



September 2016: Have you ever wanted to import the newest Excel file types directly into IBM Notes? Look no further! Voith's CODE Notes Import Export Library (IEL) can do that! You can export too!

Newsflash September 2016: You can now also import and export Comma Separated Files (CSV) too!

With easy user interface and intuitive field mapping, you can easily import and export the newest Microsoft Excel 2007, 2010 and 2013 files (with file type xlsx) in addition to the older 93-2003 file type xls.


Above you see how some spreadsheet columns have been mapped to their corresponding fields in the Document-form.

Below you see an example on the CSV import;

Note how you also can fine tune what format you export to.

Why was IEL created?

IBM Notes itself have never had direct support for import- or export to Excel files. However, this could be circumvented since earlier versions of Excel you could save the content as a Lotus 1-2-3 workbook. IBM Notes can import 1-2-3 files. Starting from Excel 2007 this ability disappeared, and Notes users wanting to import- or export to Excel was left in a void.

Programmers could of course address this by creating LotusScript-solutions controlling Excel via COM. and several good solutions is out there. Try to search for CreateObject Excel Application LotusScript, and you will find them. A slight disadvantage with the COM-approach is that you actually need Excel installed, and funny things could happen if you tried to launch multiple import- or export tasks at the same time. In other words, easy at first sight, but kind of hard to control in a production environment.

Another programmatic approach can be via Lotus Symphony APIs (or even via the original Open Office APIs), since these APIs do a pretty good job reading and writing Office formats.

IEL was created because I wanted something up front able to import and export Excel without having to resolve to programming all the time.

Where do you find IEL when installed?

After IEL is installed, you will find new import- and export options in your standard Notes File -> Import/Export dialog boxes.

Below you see the two new import options Excel 2007+ for the newer xlsx files and Excel 97-2003 for the older xls files. In addition you see the CSV option too;


The same two new options exists when you exportfrom Notes.

In other words, IEL is a standard import/export library to IBM Notes.


Features:

• No need have Excel installed at all.
  That's right. IEL can both import and export Excel files without having Excel installed on the computer. If you do have Excel installed, IEL don't mind either.
  
• Easy, visual field mapping. Visually easy to see what fields maps together. Automatic conversion of field types if possible.
  
  
  Simply by double clicking on a field, you enable it for mapping, as shown with the red field above. By double clicking on a target field you map the two fields as show below;
  You may of course break any mapping by double clicking on either field in a existing mapping. You may also click on the toolbar button shown below;
  
  Got long list of field names? Easily locate any matching field is the other list by right clicking on a field name and choose "Locate similar field"
  
  IEL will instantly either find the exact matching field name in the other list. If not found it will attempt to find the most soundex-like field name automatically.
  
• Change field names in target with ease.
  
  Dont' like the target field name? Simply click once on the field name and rename it to whatever you would like.
  
  
  
• Add new target fields, not found in Notes or Excel.
  
  Right click anywhere in the target field list to get this context menu;
  
  
  
  The topmost menu let you add new fields. A new field dialog box pops up;
  
  You may specify a field type as well.
  
  When the field has been added it turns up in the target field list, like this;
  
  The new field can be mapped like any other field in the target field list.
  
• Any target list field can have a formula!
  
  Use standard Notes @-formulas together with special @VC-formulas. The formula will be evaluated on the document, so you can reference other fields etc. The current value is always treated as text and can be referenced as @VCThisValue. By the way, other special @VC-formulas exist too. Wonder what @VCLinkToDocument do? :-)
  
  Right-click on the target field you want to edit the formula for;
  
  
  A super-simple formula window pops up (yes, I will improve the editor in the future, but right now I figure that its important to just have an editor ...);
  
  Enter your formula.
  
  After a formula is edited, you can easily see which fields have a formula or not;
  
• Save you settings and reload them later.
  
  IEL has the ability to save your settings to file, and reload them back at any time. Use the menu File -> Save Settings -> Save to file ...
  
  
  
  The settings file contain all your current settings, including the file name you work with. This may be overridden when you reload the settings file.
  
  Note that you from November 2014 also can launch the import- or export files directly in Windows Explorer. This launches the user interface directly and the job starts immediately. This is super-nice if you have a persistent field mapping that you want to perform regularly.
  
• Full Unicode support. Filenames, field names, any content. Full conversion between Lotus' internal LMBCS and Unicode.
  
  You speak Greek? No problem, file names and content shouldn't be any problem at all. Field names in Notes adhere to the file name rules in Notes.
  
• Multithreaded design, meaning that you quickly can return to Notes and continue to work with other stuff, while IEL does its work. Any task can of course be aborted at any time.
  
  
  You can even have multiple import- or export tasks running at the same time.
  
• When IEL is finished, you will see a link to the newly exported file
  
  
  
  You may choose to disable the dialog box above, by checking the "Don't ask me again next time, just close"
  
• Preview before import- or export.  Sometimes it can be hard to know what will be imported- or exported. Why not preview the result before commencing a long job? You will see how potential formulas execute too;
  
  
  
• Reorder the output when exporting. Sometimes order do matter :-) Right-click on any field in the target column and then select the Reorder Fields ...
  
  
  
  This will open a dialog where you can move each field as you like:
  
  
  
  

General import features:

• Choose which form you want to import to. IEL tries to choose the default form for you;
  
  
  When you choose a form, the fields of the form are instantly displayed as target fields for mapping. Note that some fields, such as computed and computed for display type fields are omitted from the list since you can't write to them anyway.
  
• You don't have a form to import to? No problem, let IEL dynamically create all fields for you!
  
  
  
  
• Compute with form support!
  
  You may choose to perform "Compute with form" on each imported row, meaning that any computed field in the form is evaluated for each document.
  
  Note that this slows down the import somewhat.
  
• Choose which sheet you want to import from!
  
  
  Simply choose which sheet you want to get data from. This information is of course saved in the job files too.

Excel export features:

You have all the field mapping capabilities, combined with the ability to add new fields, rename target fields, and even use formulas.

Below you see the export dialog box of IEL. Pretty similar to the import dialog box regarding mapping, but now the Excel fields are the target fields while IBM Notes is the source fields.

• Export All documents, or Selected documents only.
  You can choose to export selected documents or all documents from the current view.
  
  
  
• Export columns from the selected view -or- fields from the first selected document.
  
  This gives you the ability to dump the view as you see it in Notes, -or- you can dump fields at will.
  
  The absolutely fastest way to export data, is to select All documents and columns from the selected view. IEL will then read view summary info as fast as possible.
  
• Quickly control the new column names in Excel.
  
  
  The default setting is Equal fields as IBM Notes, which means that the Excel column headers will be the same as the field names chosen for IBM Notes. The idea here is to quickly let you define the column headers in Excel without needing to add your own fields.
  
• Notes view format can be exported.
  
  Please note that IEL will try to export your format. Sometimes it looks good, and sometimes not. The more special columns you have (display as icons, display colors etc), the more twisted the Excel-result may look.
  
  Note that you can only select to export view format when you have chosen All documents in the view and Columns from the selected view. The options are grayed out if you have any other combination.
  
• Group the rows in Excel.
  
  A cool feature in Excel is that you can group rows and columns. By chosing this option in IEL, it will automatically group the Excel rows for you. Look at the Notes view below;
  
  
  
  When exported to Excel, it looks like this;
  
  
  Note how the data rows are grouped!
  
  You may only group rows if you export the All documents in the view, choose Columns from the selected view and Export Notes view format to Excel, like this;
  
  
  The reason for this is of course that IEL needs to see the whole view structure in order to be able to group your rows.
  
• Start Excel export from a Toolbar- or Actionbar button with @Formula or LotusScript!
  
  Starting from release 1.1.0.2476 (released 23OCT2012), you have a cool way of extending IEL. You can now kick off IEL by a single click on a toolbar button, or via an Actionbar button, and have IEL export the current view to Excel with it's "Export Notes View format to Excel" automatically turned on. This makes it super quick and easy to export any view. Note that this is an additional way of starting IEL, so you can off course starte IEL the normal way too, by using File -> Import or File -> Export menues.
  
  When you have finished this guide, you will have a nice toolbar like this;
  
  
  
  The first button launches the IEL dialog box in Export mode from your current view, so you still can choose which fields to export etc.
  
  The second button instantly exports all documents in view to an Excel file, without bringing up the dialog box. In other words, you get what you see in Notes instantly to Excel!
  
  The third button do the same as for the second button, but now you export the selected documents instead.
  
  Head over here to see how the toolbar and the buttons are built
  
  

Comma Separated Files (CSV) import features:

From September 2016 IEL can also import CSV files, with all the easy and power as for Excel files. Below you see an example of the CSV import dialog box:



Note that you can choose the character set of the input CSV file. Got Unicode, no problem, or Kanjii? No problem either!

One thing IEL can't guess very well for you is whether your CSV file has headers or not as the first line in the file. Check the "First row in CSV file contain the headers" if that is the case.

Comma Separated Files (CSV) export features:



Again with the simple user interface, everything works the same way as for the Excel files. Again you can choose the character set of the output file, and you can choose to export the target field names as CSV column headers