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.
- 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.
- 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.
- 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.)
- 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.
- Save the finished Microsoft Excel file as a Microsoft Excel Workbook (*.XLS), if it is not already in that format.
- 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.
- 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.
- Start Lotus Notes. Open the database where you want to import the data.
- 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.
- Press the Import button.
- 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>.
- 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.
TUTORIAL: HOW TO IMPORT DATA INTO LOTUS NOTES -- WITHOUT PROGRAMMING
Part 1: How to import up to 8,000 data records into Lotus Notes
Part 2: How to import up to 65,000 data records into Lotus Notes
Part 3: How to import an unlimited number of data records into Lotus Notes
|ABOUT THE AUTHOR:|
| Chuck Connell, Consultant
Chuck Connell is president of CHC-3 Consulting, which helps organizations with all aspects of Lotus Notes and Domino.