This and that...

This and that...

How to import data into Lotus Notes -- without programming

Notes ProgramingPosted by Martin Tue, February 17, 2009 18:46:10

This is the easiest way to import data into Lotus Notes, in my opinion. I have used it many times and it consistently maintains good data fidelity -- meaning that each data item appears in Lotus Notes as you want it to. Date fields come through as proper Lotus Notes dates, numbers as number type, etc. Surprisingly, this technique is based on a product from the Evil Empire and an obsolete file format.

1 Export the data from its current source to Microsoft Excel. Almost every data storage program has some method of exporting data to Excel. Sometimes this feature is built in, as a single button or wizard. If the feature does not appear directly in your source program, you can export the data to a format that Microsoft Excel can read. This can be comma-separated or tab-separated text with fixed-width columns. You then open these files with Microsoft Excel, and are presented with an import wizard that works nicely.

2. Examine and manipulate the data in Microsoft Excel. Make sure you have all the rows (records) and columns (fields) you want. Often the source program exports columns you don't need, so you can just delete those columns completely. You can also delete blank rows, rows that contain garbled data, or data records you don't care about. If your Lotus Notes application contains additional fields, perhaps computed from other columns, you can create those new columns in Microsoft Excel.

3. Insert a top row in the Microsoft Excel spreadsheet that contains the exact names of the Lotus Notes fields for each column of data. Make the row left-justified with a text data format. Do this by selecting the entire row (click on the row number), then pull down Format -> Cells -> Number=Text and Alignment=Left. (If the row is not left-justified, it may not be recognized as a header row later in the process.)

4. Look for columns that appear to be numbers but are actually textual data. The most common are area codes, Zip codes, and customer numbers. You want these fields to be imported to Lotus Notes as text. Storing them as number data within Lotus Notes is incorrect and leads to headaches later. Change these columns to text by selecting the entire column (click on the column name) then pull down Format -> Cells -> Number=Text.

5. Save the finished Microsoft Excel file as a Microsoft Excel Workbook (*.XLS), if it is not already in that format.

6. Save the file again, but this time as a 1-2-3 version 1 spreadsheet. Do this with File -> Save As -> Save As Type = WK1 (1-2-3). Notice that there are other save choices for 1-2-3, such as WK3 and WK1 FMT. Use the one stated here; I have tested it and know that it works. During the save, you will be warned that some features of the spreadsheet may not be compatible with the WK1 file format. Ignore this warning and click "Yes" to continue the save.

7. Close the Microsoft Excel sheet. You will be asked if you want to save the changes you made to <filename>.WK1. This time, answer "No." You already have two copies of the sheet -- one in .XLS and one in WK1. If you answer "Yes" here, you may overwrite your .XLS file.

8. Start Lotus Notes. Open the database where you want to import the data.

9. Use the Lotus Notes command File -> Import. Select the WK1 file you just created. The import type will automatically change to Lotus 1-2-3.

10. Press the Import button.

11. Set the options: Import As = Main Document, Using Form = <your form>, Column Format = Defined by the WKS Title, Limit What is Imported = <blank>, Calculate Fields on Form = <disabled>.

12 Press OK.

This method may appear long-winded, but it is really quite easy. After using it a couple times, you can import almost any data from almost any source very quickly.

Warning: There is a hard limit of 8,192 rows for 1-2-3 version WK1 spreadsheets. You have a header row, so this leaves 8,191 rows for data. If you exceed this limit, Microsoft Excel will simply cut off output to the WK1 after 8,192 lines -- without telling you. I have made this mistake, and luckily noticed the error before my customer did.

And, just to clarify, you do not need the 1-2-3 software at all. You are just using the old 1-2-3 file format for the data exchange.

This method allows for extra control of the import process and works for up to 65,000 records. The key idea is that you use a small control file (.COL) that tells Lotus Notes how to import a tab-separated text file.


1. Export the data from its current source to Microsoft Excel. Almost every data storage program has some method of exporting data to Excel. Sometimes this feature is built in, as a single button or wizard. If the feature does not appear directly in your source program, you can export the data to a format that Microsoft Excel can read. This can be comma-separated or tab-separated text with fixed-width columns. You then open these files with Microsoft Excel, and are presented with an import wizard that works nicely.

2. Examine and manipulate the data in Microsoft Excel. Make sure you have all the rows (records) and columns (fields) you want. Often the source program exports columns you don't need, so you can just delete those columns completely. You can also delete blank rows, rows that contain garbled data, or data records you don't care about. If your Lotus Notes application contains additional fields, perhaps computed from other columns, you can create those new columns in Microsoft Excel.

3. Insert a top row in the Microsoft Excel spreadsheet that contains the names of the Lotus Notes fields for each column of data. You will not really use this header row during the import, but it will be helpful to have it later to remind yourself what you did.

4. Look for columns that appear to be numbers but are actually textual data. The most common are area codes, Zip codes, and customer numbers. You want these fields to be imported to Lotus Notes as text. Storing them as number data within Lotus Notes is incorrect and leads to headaches later. Change these columns to text by selecting the entire column (click on the column name) then pull down Format -> Cells -> Number=Text.

5. Save the finished Excel file as an Excel Workbook (*.XLS), if it is not already in this format.

6. Save the file again, but this time as a tab-delimited text file. Do this with File -> Save As -> Save As Type = Text (Tab Delimited). During the save, you will be warned that some features of the Excel spreadsheet may not be compatible with text format. Ignore this warning and click "Yes" to continue the save.

7. Close the Microsoft Excel spreadsheet. You will be asked if you want to save the changes you made to <filename>.TXT. This time, answer "No." You already have two copies of the sheet -- one in .XLS and one in .TXT.

8. Create a column description file that tells Lotus Notes the format of the .TXT file. Column files have a .COL extension and are created with a plain-text editor, such as Notepad. An example is below.

9. Start Lotus Notes. Open the database where you want to import the data.

10. Use the Lotus Notes command File -> Import. Select the .TXT file you created from Microsoft Excel. The import type will automatically change to Tabular Text.

11. Press the Import button.

12. Set the options: Import As = Main Document, Using Form = <your form>, Use Format File = Enabled, Format File = <your format>.COL, Header Line Count = 1, Footer = 0, Line Per Page = 0, Calculate Fields on Form = Disabled.

13 Press OK.

Again, this looks harder than it is. After creating a few .COL files, you can copy/modify them from previous jobs.

Example .COL file for importing tab-delimited text into Lotus Notes:

;Comment line.
CaseNumber: TYPE TEXT UNTIL "<tab>";
SlipDate: TYPE DATETIME UNTIL "<tab>";
SlipHours: TYPE NUMBER UNTIL "<tab>";
BillingCode: TYPE TEXT UNTIL "";

Notice that each line states the data type of the field. Where you see <tab> in the example, enter a real TAB character, by pressing the TAB key as you create the .COL file.

Warning: Look carefully in the .TXT file to make sure each line ends immediately after the last field of data. Sometimes, Microsoft Excel thinks there are empty columns and inserts extra tab characters to the right of your data in each row. If your file contains these extra tabs, just change the last field definition in the .COL file from null string to <tab>.

How to import an unlimited number of data records into Lotus Notes

With the method I explained in Part 2, the limit of 65,000 records is imposed by Microsoft Excel, rather than Lotus Notes. So if you skip the Microsoft Excel step, there is effectively no limit on the number of records you can import to Lotus Notes from a .TXT file. I have tested 175,000 records.

For imports greater than 65,000 records, simply create a tab-separated .TXT file directly from your source program, and then perform the import to Lotus Notes as described in Part 2.

If your source program cannot create tab-separated output, you might try a different separator, such as vertical bar (|). Just edit the .COL file and replace <tab> with the new separator.

When you go directly from the source program to a .TXT file, you miss the opportunity to examine and clean the data with Microsoft Excel. But this may be necessary in order to perform very large imports.

You can write some simple @-function agents later within Lotus Notes to fix data problems when the import is finished. You can even put the @-functions directly into the .COL file.

For advanced tricks that you can do with a .COL file, such as @-function post-processing of imported data, see Domino Designer Help -> Index -> COL Files.