Lotus Notes provides a variety of methods to import data into databasesmigrating existing documents between databases, and importing from an ASCII text file or spreadsheet.
This section details how to load information into a database from a spreadsheet. When you understand the process to import from a spreadsheet, a similar process can be applied to ASCII text files. Later in this chapter, youll also learn how to transfer documents between databases, including how to automatically archive documents.
Importing data from a spreadsheet is relatively straightforward but does require some planning to ensure that the information maps to the overall design of the database.
How It Works
Importing data into a Lotus Notes database requires a properly formatted spreadsheet where each row equates to a document and each column maps to a field on the document. A separate column must be created for each field on the form. The first row in the spreadsheet must contain the corresponding Lotus Domino field name. This is called a "Title Row," and all data in each column will be mapped to the corresponding field name in this row (see Figure 18.1).
Figure 18.1. Example spreadsheet used to load data into a Domino database
Using this approach, the data columns can be listed in any order because the information is mapped to the associated field name on the document. In other words, the sequence of the columns does not need to match the field tab order of the actual Domino design form.
However, the cell properties associated with each column in the spreadsheet should be set to match the field type defined on the form. For example, in Figure 18.1, the first and last name columns are set to "text", the employee hire date is set to "date", and the total number of years of experience is set to a number.
Note
If the field name in the spreadsheet does not match a field name in the form, the import process will create a new field (even if the field is not actually displayed on the form). In other words, the data will be present on the document but not visible via the form. To view the data, you can add the field to the form or locate the field using the document properties dialog.
In most cases, creating, formatting, and mapping the spreadsheet to the Notes database design will be the most time-consuming aspect of the import. After it is properly formatted, the data can be imported in a few short steps.
Implementation
Follow these steps to import data into a Lotus Notes database application. This process illustrates how to import "main" documents into a database as opposed to "response" documents.
Step 1.
Create the database, including forms and views, into which data will be imported. Identify all key fields on the form that will be used during the data import.
Step 2.
Generate the spreadsheet. Create a separate column for each data field. Put the field name in the first row for each column that contains data. This is the "Title Row" that specifies the target field to hold all data in each column. Put the data in all subsequent rows.
Step 3.
Format each column in the spreadsheet. For each column in the spreadsheet, select the column and set the column properties to match that of the corresponding fieldtext, date, time, or number.
This is a critical step in the import process. If the cell type in the spreadsheet and field type in the form do not match, strange results may occur in the database. Information may not display properly in the fields, and computed fields may not generate the correct results.
Step 4.
Save the spreadsheet. Lotus Notes expects the spreadsheet to be in the Lotus 1-2-3 format (e.g., DataLoad.wk4). If using Microsoft Excel, be sure to utilize the Save As function to make a copy of the spreadsheet in the Lotus 1-2-3 format before importing the file.
Step 5.
Import the data. Launch the Lotus Notes client and open the target database. Select the File > Import menu options. This will display the configuration dialog window that defines the data import specifications.
Tip
You may want to use a copy of the database when testing the data import. This can be especially beneficial when loading data into a database that already contains documents. This way you can verify the data imports without affecting existing data or having to search through the database to sort out the old and new documents.
Change the file type to Lotus 1-2-3. Then locate and select the spreadsheet file (see Figure 18.2). Click the Import button to continue with the import configuration process.
Figure 18.2. Sample dialog used to select a file for import
Select Main Document(s) as the document import type and select the form to be used. Also change the Column Format value to Defined by the WKS title (see Figure 18.3). This signifies that the first row in the spreadsheet will be used to determine the destination field for each column in the imported file.
Figure 18.3. Import property settings for Lotus 1-2-3 data files
Step 6.
Optionally, select Calculate fields on form during document import if the form contains computed fields, Input Translation, or Input Validation formulas. When selected, all computed fields on the form are calculated after the database has been added to the form prior to saving the document.
Tip
Be sure that the spreadsheet includes all required fields and that they are in the correct format if the form includes validation. Otherwise, documents may not be created during the data import when the Calculate fields on form during document import option is checked. If this option is not selected and data is imported, the data validation will not be triggered until the document is saved by a user in the Lotus Notes client, and depending on the design of the database, this could force the user to enter data in order to close the document. Either way, be sure that the spreadsheet contains all required data fields and that the information is formatted correctly based on the design of the database application, forms, and views.
Step 7.
Select OK to begin the data import process. Afterward, verify that the data imported correctly by checking the data in the various views and by inspecting the document data with the form you selected during the import process.
You may also want to open the document properties dialog and check the field value for each field on the form. This will ensure that the data types are correct. To inspect the field properties, select the File > Document Properties menu options from a view or a document. Switch to the second tab and scroll through each field to verify that the Data Type value is correct (see Figure 18.4).
Tip
Try the sample database and data import file provided on the companion Web site. Create a new copy of the Dataload.nsf database on the local workstation. Then use the DataLoad.wk4 file to try the import process.
Figure 18.4. Data Type associated with a particular field