|Date tip published:||07/10/2006|
|Description:||LotusScript has the ability to read and write text files. This tip covers how to import a CSV (comma separated values) file into Domino creating a new document for each line in the file.|
To learn more about LotusScript use the following links:
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:
- Get a free file handle (integer value)
- Open the file for input
- Loop through the file reading a line at a time until there are no more records
The first step in working with files is to get a file handle. This is simply an integer used to signify the file once it has been opened in LotusScript. This could be any integer value from 1 to 255. The programmer could simply assign a number but it is best to use the LotusScript FreeFile() function. This function will get the next available file handle number. To use it simply assign the returned integer value to a variable.
The next step is to open the file. This associates the actual file name on the hard drive to the file handle. Once this step is done then any reference to the file is done by the file handle. fileName is the actual path and file name of the text file to read. filenum is the integer value obtained from the FreeFile function.
Open fileName For Input As [#]filenum
The Input # statement in LotusScript is used to read in data from the text file. filenum is the integer value obtained from the FreeFile function. variableList is a list of variable names that are separated by commas. The Input # statement reads in a line at a time. The line is parsed according to the listed variables, with each new value assigned, in order, to each variable in the list. The values in the line are separated by commas, spaces, quotation marks or a combination of these. See the detailed help for the Input # statement at the end of this tip for a description of valid input file formats. There should be a variable for each separate value in the line.
Input #filenum , variableList
Technique: Import a CSV File using LotusScript
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"
Lines 1 to 17 declare and setup the variables.
|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|
|44.|| Msgbox "You imported " & counter & " records."|
|45.|| End If|
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.
Returns an unused file number.
FreeFile returns an Integer value.
Use FreeFile when you need a file number (to open a file), but you don't know what file numbers are currently available.
If no more file numbers are available, an error is generated.
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.
You can call the function as either FreeFile or FreeFile().