I have a working notes application that i want to import some data into. Usually the user logs into the application & keys in his data, but now he has sent me a CD containing his data. How can i load the data from the CD into the notes application?
Subject: Using a column descriptor file (COL ) to map a source file to a Notes view
APPLICATION DESIGN
Using a column descriptor file (COL ) to map a source file to a Notes view
To import many files that have an identical structure into a view, create a column format descriptor (COL) file. A COL file is like a map that defines which data from the source file goes into which Notes field. Use a COL file only with tabular text files and worksheet files.
A COL file is an ASCII text file that contains a series of column definition statements, followed by a section containing formulas that control the contents of fields in the Notes view. You write one statement for each column in the source file. This statement describes how to format and map the columns in the source file to columns in the Notes view. For best results, use exactly the same field names in the COL file and in the Notes view.
Mapping a column to a field in a view
Enter the name of the column in the source file. The name should be identical to the name of the corresponding field in a form. After you enter the column name, you can provide an optional statement that specifies the data type of the data. You must, however, provide statements that define the format of the source data – that is, the delimiter or fixed position of the column.
If you use field names in the COL file that are not in the Notes view, Notes imports the data for those fields, but the data won’t appear in the form or view until you create Notes field names that exactly match those in the COL file. For example, if the COL file uses the field name “Phone” and the Notes view uses “PhoneNumber,” data for the “Phone” field is present in the view but is not displayed. To correct this problem, change the name of the existing Notes field to “Phone,” create a synonym for the existing field, or create a new Notes field called “Phone,” and refer to the field in the view you’re using.
Specifying the data type of a column
When you import a file, Notes automatically determines the data type of each column, based on the appearance of the data. For example, if the column in the source file contains data in the form 7/19/97, Notes imports the data as dates.
As part of a field definition, you can provide an optional TYPE expression, followed by the appropriate data type: Text, Number, or Datetime. For example, to import the contents of a field called DateOfBirth as a date, enter the following field definition into the COL file:
DateOfBirth: TYPE DATETIME
The data in the source column must fit the data type you specify. If Notes encounters data that doesn’t match the data type you specify, Notes imports the data as the type it most resembles.
Using formulas
The formulas that you write in a COL file describe how to modify the incoming values for display in the view. These formulas, which follow the same rules as view selection formulas, may:
Use @If formulas to test incoming values and display a specific value in the view, depending on the result of the formula.
Use text manipulation functions to modify the appearance of the incoming value – for example, to concatenate values or trim excess space characters from incoming values.
Enter default values in fields.
See Also
Writing a COL file for a tabular text file
Writing a COL file for a worksheet
Creating a column descriptor (COL) file
Writing formulas for COL files
Subject: Free Lotus Notes/Domino Import Utility
We have a Lotus Notes / Domino Import utility you can freely download from our website for this very purpose. The URL is:Import Utility for Notes
It’s easy to use, well documented and we support it.
Hope it helps,
Alex
Subject: importing data into notes
What kind of data is it? What type of file? Does it need to be parsed into fields or should all of the data in each file be dumped into one field on a document? All the details you can think of will be useful in helping find a solution to your problem.
Subject: RE: importing data into notes
the data is all ascii text and is comma delimited. each row should be considered a new item and each column a field.
Subject: RE: importing data into notes
Ah, very straightforward:
Create your “Import” button or agent (if you need it scheduled) with this code (or call it from a script library)
I use a profile document with 2 fields of type text to contain the number of columns and the filename (pdoc contains this profile document). You also hardcode these values, if you prefer. The references to the NotesLog can be removed. I use them for auditing purposes and error trapping to a separate database.
Sub Click(Source As Button)
Dim session As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim pdoc As NotesDocument
Dim clog As NotesLog
Dim fileNum As Integer, cells As Integer, k As Integer
Dim InputStr As String, delimiter As String
Dim FieldArray As Variant
Dim cHeader, rHeader As String
Dim view As NotesView
Dim hview As NotesView
Dim collection As NotesViewEntryCollection
Dim entry As NotesViewEntry
Set db = session.CurrentDatabase
Set cLog = New NotesLog _
( "Script in " + _
db.Title + " on " + db.Server )
On Error Goto ERRHANDLER
Call cLog.OpenNotesLog( "dsys/steinmart", "badchecklog.nsf" )
Call cLog.LogAction("Database variables Initialized.")
Set pdoc = db.GetProfileDocument("settings")
Set view = db.GetView("tempdocs3")
Set hview = db.GetView("(Bad Checks Header Information)") 'this is the lookup view for header info
Set collection = view.AllEntries
Call cLog.LogAction("Database objects initialized.")
fileNum% = Freefile()
Set fname = pdoc.GetFirstItem("filename")
Set cellcount = pdoc.GetFirstItem("numcols")
delimiter = "," ' Delimiter of your file
cells = Cint(Cstr(cellcount.values(0))) + 1 ' How many cells + 1
filename$ = Cstr(fname.values(0))
k = 0
Call cLog.LogAction("Spreadsheet variables set from profile document.")
'Delete all existing documents before running the creation process.
%REM
Set entry = collection.GetFirstEntry()
While Not(entry Is Nothing)
Set doc = entry.Document
doc.Remove(True)
Set entry = collection.GetNextEntry(entry)
Wend
%END REM
'Today's Date
n$ = Cstr(Today)
'Print n$
wdn% = Weekday(n$)
Select Case wdn%
Case 1 : d$ = "Sunday"
Case 2 : d$ = "Monday"
Case 3 : d$ = "Tuesday"
Case 4 :d$ = "Wednesday"
Case 5 : d$ = "Thursday"
Case 6 : d$ = "Friday"
Case Else : d$ = "Saturday"
End Select
look$ = d$ + " " + n$
'Check and see if the header doc needs to be created. Create if so.
Set checkdoc = hview.GetAllDocumentsByKey(look$, True)
If (checkdoc.Count = 0) Then
Call cLog.LogAction("No header document was found for this date. This is the first Run for the day. Creating a header document.")
Set doc = db.CreateDocument
doc.form = "tempHeader"
doc.cHeader = "Check Returns Last 30 Days" ' Column 4
doc.rHeader = look$ ' Column 9
Call doc.ComputeWithForm(True, False)
Call doc.Save(True, False)
Call cLog.LogAction("Header document was successfully created.")
End If
'Import data into the body documents
Open fileName$ For Input As fileNum%
Call cLog.LogAction("Obtained a handle to the file name.")
Do While Not Eof(fileNum%)
Line Input #1, InputStr$
FieldArray = parseall(InputStr$, delimiter, cells)
response% = CreateDocument(FieldArray, look$, n$)
k = k + 1
'Print "Document Created: " + Cstr(k)
Loop
Goto Done
ERRHandler:
Print Cstr(Err) + " " + Error$ + " " + Cstr(lerr)
Call cLog.LogAction( Cstr(Err) + " " + Error$ + " " + Cstr(Erl))
Resume Done
Done:
If k > 1 Then
Call cLog.LogAction(Cstr(k) + " data were successfully imported!!")
Print Cstr(k) + " data were successfully imported!!", mb_ok, "Import Data Completed."
End If
Call cLog.Close()
Close fileNum%
End Sub