Menu

Notes Import Export Library

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

A picture named M2
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;

A picture named M3

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

A picture named M4
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.

A picture named M5
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;
A picture named M6
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.
A picture named M7
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;
A picture named M8

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.

    A picture named M9
    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;
    A picture named M10
    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;
    A picture named M11
    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"
    A picture named M12
    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.

    A picture named M13
  • Add new target fields, not found in Notes or Excel.

    Right click anywhere in the target field list to get this context menu;

    A picture named M14

    The topmost menu let you add new fields. A new field dialog box pops up;
    A picture named M15
    You may specify a field type as well.

    When the field has been added it turns up in the target field list, like this;
    A picture named M16
    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 picture named M17

    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 ...);
    A picture named M18
    Enter your formula.

    After a formula is edited, you can easily see which fields have a formula or not;
    A picture named M19
  • 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 ...

    A picture named M20

    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.

    A picture named M21
    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

    A picture named M22

    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;

    A picture named M23
  • Reorder the output when exporting. Sometimes order do matter :-) Right-click on any field in the target column and then select the Reorder Fields ...

    A picture named M24

    This will open a dialog where you can move each field as you like:

    A picture named M25



    General import features:
  • Choose which form you want to import to. IEL tries to choose the default form for you;

    A picture named M26
    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!

    A picture named M27

  • 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.
    A picture named M28
    Note that this slows down the import somewhat.
  • Choose which sheet you want to import from!

    A picture named M29
    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.

    A picture named M30
  • Export All documents, or Selected documents only.
    You can choose to export selected documents or all documents from the current view.

    A picture named M31
  • 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.

    A picture named M32
    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;

    A picture named M33

    When exported to Excel, it looks like this;

    A picture named M34
    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;

    A picture named M35
    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;

    A picture named M36

    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:

A picture named M37

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:

A picture named M38

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

Notes Import Export Library

Download IEL

Purchase IEL

Click here for more information about IEL prices and here for general licensing options!