| 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.
| 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 |
 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.
Input # method
Reads data from a sequential file and assigns that data to variables.
Syntax
Input #fileNumber , variableList
Elements
fileNumber
The number assigned to the file when you opened it. A pound sign (#) sign must precede the file number.
variableList
A list of variables, separated by commas. The data read from the file is assigned to these variables. File data and its data types must match these variables and their data types.
variableList cannot include arrays, lists, variables of a user-defined data type, or object reference variables. It can include individual array elements, list elements, and members of a user-defined data type or user-defined class.
Usage
The following table shows how the Input # statement reads characters for various data types.
| variableList data type | How Input # reads characters |
| Numeric variable | The next non-space character in the file is assumed to begin a number. The next space, comma, or end-of-line character in the file ends the number. Blank lines and non-numeric values are translated to the number 0. |
String
variable | The next non-space character in the file is assumed to begin a string. Note these special conditions:
If that character is a double quotation mark ("), it is ignored; however, all characters following it (including commas, spaces, and newline characters) up to the next double quotation mark are read into the string variable.
If the first character is not a double quotation mark, the next space, comma, or end-of-line character ends the string.
Blank lines are translated to the empty string ("").
Note that tab is a non-space character. |
| Fixed-length string variable | LotusScript reads this according to its length. For example, LotusScript reads a variable declared as String *10 as 10 bytes. |
| Variant variable | The next non-space character in the file is assumed to begin the data.
If the data is:
Empty (a delimiting comma or blank line), LotusScript assigns the variable the EMPTY value.
The literal "#NULL#", LotusScript assigns the variable the NULL value.
A date/time literal, LotusScript assigns the variable the DataType 7 (Date/Time).
A whole number, LotusScript assigns the variable the Data Type 2 (integer) if the number is in the legal range for integer; the DataType 3 (Long) if the number is in the legal range for Long but not within the range for integer; and otherwise the DataType 5 (Double).
A number with a fractional part, LotusScript assigns the variable the DataType 5 (Double).
If none of the above applies, LotusScript assigns the variable the String type. |
If LotusScript encounters an EOF (end-of-file), input terminates and an error is generated.
LotusScript inserts "chr(10)" to represent the newline character in any multi-line string (for example, a string that you type in using vertical bars or braces). If you Print the string to a file, this newline character will be translated into the platform-specific newline character(s). If you Write the string to a file, no translation is done.
Note Newline does not mean either chr(10) or chr(13) on all platforms. Newline is the character or sequence of characters that is used to mark the end of a line. This may be chr(10), or chr(13), but it may also be something else, because the actual value of newline depends on the platform.
Note When reading a multiline string from a sequential file written by the Write # statement, use Input, not Line Input.
When reading record-oriented data, using a random file with the Get statement is easier and more efficient than using Input #. Use Get for reading record-oriented data (a random file); use Input # for reading text data (a sequential file). |
|
|