Beginner LotusScript for Notes Domino 7
Intermediate LotusScript for Notes and Domino 7
Advanced LotusScript for Notes Domino 7
Notes Domino 7 LotusScript Package

Import Data from a File using LotusScript

Using CSV (comma separated values) files can be a good way to import data into Domino. A CSV file is simply a text file with multiple lines. Each line in the file is analogous to a record. The values in the line are separated by commas, spaces, quotation marks or a combination of these. LotusScript has the ability to read from and write to text files. This tip covers how to import a CSV file into Domino to create a new document in the Domino database for each line in the file. The code could be used in a scheduled agent or can be initiated by a user.

Working with Files in LotusScript

There are three steps in reading files in LotusScript:

1. Get a free file handle (integer value)
2. Open the file for input
3. Loop through the file reading a line at a time until there are no more records

filenum% = FreeFile()

Open fileName For Input As [#]filenum

Input #filenum , variableList

This code walkthrough is from the Import Parts agent that is used in the following demonstration. This code will import parts records for a golf club company. The following is a sample line of data from the PARTS.TXT file that is used as the import file for the demonstration:

GSdeluxLL,"Golf Set Complete Deluxe Ladies Left",825.00,Ladies,Left,"09/13/2005 11:43:51 AM","Golf Set Irons Ladies Left,Golf Set Woods Ladies Left"

1.	Sub Initialize
2.	Dim session As New NotesSession
3.	Dim db As NotesDatabase
4.	Set db = session.CurrentDatabase
5.	Dim uiws As New NotesUIWorkspace
6.	Dim fileCSV As Variant
7.	'Declare variables to hold data
8.	Dim PartNum As String
9.	Dim PartName As String
10.	Dim Price As Double
11.	Dim Orientation As String
12.	Dim Sex As String
13.	Dim LastModDate As String
14.	Dim ComponentList As String
15.	Dim counter As Integer
16.	Dim doc As NotesDocument
17.	counter =0

Line 19 uses the Freefile function to get a file handle. This variable will be used later in the Open and Input # statements.

18.	'setup file number
19.	filenum% = Freefile()

Line 21 prompts the user for a file to import. On line 23, the return value from the OpenFileDialog is tested to make sure the user selected a file and did not cancel the dialog. The file is opened on line 24. Note that the OpenFileDialog function returns an array since it can return multiple file names. The first parameter of the OpenFileDIalog function can be set to FALSE to prevent the user from selecting multiple files.

20.	'Ask user for file location
21.	fileCSV = uiws.OpenFileDialog(False, "Choose the CSV file","*.txt" ,"c:\temp")
22.	'If the user chose a file then process
23.	If Not Isempty(fileCSV ) Then
24.	Open fileCSV(0) For Input As filenum%

Line 25 starts a loop which is repeated for each line in the file until there are no more lines left to process. The EOF function returns TRUE when the end of the file has been reached. On line 27, the current line is read and its values are assigned to the variables which are listed in this code line. Line 29 creates a new Notes document in the database and then lines 30 to 39 writes the variables to the document. Note that line 38 sets the form field so the resulting document is formatted and displayed using the "Part" form. Line 41 saves the document. Note that if your operating system's language settings are set to a non U.S. date, than you may get errors on line 37 due to the date conversion. You can remove the date conversion (Cdat) if needed to get around this issue and import this value as a string instead of a date value into the Notes field.

25.	Do Until EOF(filenum%)
26.	'Read a line of data
27.	Input #filenum%, PartNum, PartName, Price, Sex, Orientation, LastModdate, ComponentList
28.	'Create Notes document and write values to it
29.	Set doc = db.CreateDocument
30.	With doc
31.	.PartName = PartName
32.	.PartNum = PartNum
33.	.Price = Price
34.	.Sex = Sex
35.	.Orientation = Orientation
36.	.PartsList = Split(ComponentList, ",")
37.	.LastMod = Cdat(LastModDate)
38.	.Form = "Part"
39.	End With
40.	'save document
41.	Call doc.save(False, False)
42.	counter = counter +1
43.	Loop
44.	Msgbox "You imported " & counter & " records."
45.	End If
46.	End Sub

Demonstration Demonstration: Import a CSV File using LotusScript Follow these steps to demonstrate a CSV file import using LotusScript: Below is a sample CSV file to import. Right-click this file and select Save Target As... from its context menu. Save this file to the c:\temp directory. Below is the Read From CSV File database with the code to import the data from the parts.txt file. Right-click this file and select Save Target As... from its context menu. Save this file to your Notes data directory. In the Notes client, open the Read From CSV File database and select Actions | Import Parts from the menu. To review the code, open the Read From CSV File database in Domino Designer and edit the design of the Import Parts agent.

Syntax from Lotus Domino Designer 7 Help
The following table shows the full reference syntax of the FreeFile function and the Open and Input # statements.

FreeFile Open Input # Open Statement Opens a file, enabling access to it for reading or writing data. Syntax Open fileName [ For { Random | Input | Output | Append | Binary } ] [ Access { Read | Read Write | Write } ] [ { Shared | Lock Read | Lock Read Write | Lock Write } ] As [#]fileNumber [ Len = recLen ] [Charset = MIMECharsetName] This statement must appear on one line, unless you use an underscore ( _ ) for line continuation. Elements fileName A string expression indicating the file to open. fileName may include a complete path. If you do not specify a drive and a directory, LotusScript looks for the file in the default directory on the default drive. If you specify a drive but no directory, LotusScript looks for the file in the default directory of the specified drive. On platforms without drive letters, the default directory is used. If you specify a fileName that does not exist, LotusScript generates an error if the mode is Input; for all other modes, LotusScript creates the file and opens it. For mode - Optional. Specifies the file's mode; the default is Random. Random Default mode. Designates random access mode; that is, the file is accessible by record number. Use the Get and Put statements to read and write the file. If you omit the Access clause, LotusScript makes three attempts to open the file, using Read Write access, then Write access, and finally Read access. If all three attempts fail, an error is generated. Input Designates sequential input mode. Use the Input and Input # statements to read the file. If the mode conflicts with the Access type, LotusScript generates an error. For example, you can't open a file in Input mode with Write access. Output Designates sequential output mode. Use the Write # and Print # statements to write to the file. If the mode conflicts with the Access type, LotusScript generates an error. For example, you can't open a file in Output mode with Read access. Append Designates sequential output mode, beginning at the current end-of-file. If the mode conflicts with the Access type, LotusScript generates an error. For example, you can’t open a file in Append mode with Read access. Unless you use the Seek statement to move to a file position other than the end of the file, the Print # and Write # statements append text to the end of the file. Binary Designates binary file mode. Use the Get and Put statements to read and write the file. If you omit the Access clause, LotusScript makes three attempts to open the file, using Read Write access, then Write access, and finally Read access. If all three attempts fail, an error is generated. Access operations Optional. Specifies what operations can be performed on the file. An error is generated if the access type conflicts with the file mode specified in the For clause. Read Default access type for Input mode. Only read operations are permitted. Read Write Default access type for Random mode. Both read and write operations are permitted. Write Default access type for Output, Append, and Binary modes. Only write operations are permitted. Lock type Optional. The default is Shared. Determines how the open file can be shared when accessed over a network by other processes, including processes owned by other users. Under Windows 3.1, you must run SHARE.EXE to enable the locking feature if you are using MS-DOS version 3.1 or later. Lock is not supported for earlier versions of MS-DOS. Shared Default locking type. No file locking is performed. Any process on any machine on the network can read from or write to the file. Lock Read Prevents other processes from reading the file, although they can write to it. The lock is applied only if read access has not already been granted to another process. Lock Read Write Prevents other processes from reading and writing to the file. The lock is applied only if read or write access has not already been granted to another process. If a lock is already in place, it must be released before opening a file with Lock Read Write. Lock Write Prevents other processes from writing to the file, although they can read from it. The lock is applied only if write access has not already been granted to another process. fileNumber An integer expression with a value between 1 and 255, inclusive. This number is associated with the file when you open the file. Other file-manipulation commands use this number to refer to the file. recLen Optional. Designates the record length; use an integer expression with a value between 1 and 32767, inclusive. For a Random file, recLen is the record length for the file (all records in a single file must have the same length). The default record length is 128 bytes. For a sequential (Input, Output, or Append) file, recLen is the number of characters to be read from the file into an internal buffer, or assigned to an internal buffer before it is written to the file. This need not correspond to a record size, because the records in a sequential file can vary in size. A larger buffer uses more memory but provides faster file I/O. The default buffer size is 512 bytes. For a Binary file, recLen is ignored. MIMECharsetName Note This element is new with Domino Release 6. Optional. Designates the character set to use for sequential file I/O. If no character set is provided, file I/O is done in the platform code page with the following exceptions: If a UTF-16 or UTF-8 byte order mark (BOM) is detected at the beginning of the file, the file I/O is done in the code page specified by the BOM. For existing OS/400 (iSeries) files, if no UTF-16 or UTF-8 BOM is detected, the file's CCSID (character set) attribute determines the code page. See MIME charset names for a list of valid MIME charset values. Usage If a file is already open in Binary, Random, or Input mode, you can open a copy of the file using a different file number, without closing the open file. If a file is already open in Append or Output mode, you must close it before opening it with a different file number. LotusScript limits the number of open files to 255. Depending on your operating system environment and the Lotus software you are running, the actual number of files that you can open may be 15 or less. See your product documentation for details.