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.
- 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 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.
- 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 Excel file as an Excel Workbook (*.XLS), if it is not already in this format.
- 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.
- 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.
- 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.
- Start Lotus Notes. Open the database where you want to import the data.
- 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.
- Press the Import button.
- 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.
- 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>.
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.