I downloaded data in text format, than converted to Excel, than saved it to wk4. However, wk4 only allow upload up to 8124 documents. .123 is not a recognised format. I tried .csv or .txt, but the splitting is not correct, i.e data has been truncated wrongly when it is being uploaded. So , any better suggestion?
Very much appreciated.
I don’t want to split it to several upload if possible, i.e another work around is to just split to few .wk4 documents.
Thank you.
Subject: Uploading 38000 documents in one go
If you have a CSV file you should be able to create a new document, read in the CSV values and save the document with LS. This would also leave you with a reusable function in the furture if you ever had to import data again.
Shawn
Subject: RE: Uploading 38000 documents in one go
The problem is that I did tried tabular and structured text, the former will truncate the column with different values, and the latter will give me lots of errors value not recognised (i suspect it is uploading field by field instead via view). So, that means I still need to know lotus script code for this one , yeah? thought I can just use the import function. Since it may take me longer time to work out the scripts, so I may just break the file into several files , this may have to suffice for time being. tq, Shawn.
Subject: RE: Uploading 38000 documents in one go
Hi Will,
CSV is your best bet as it is so common and easy to work with.
Take your Excel File and make the first row the names of the fields you want to import into in the lotus notes document.
Next export the excel spreadsheet as csv. There is no limit on how many rows a CSV file can take. I suggest you turn off the delimit text values with " parts of the export if it is on by default.
If you have speech marks, commas, semi colons etc in your excel data you might want to choose a delimiter that is not in the data.
I used to work for the Statistics Depart of the Nz Govt and they often used the tilde (~) as their delimiter as it is not normally found in text. THey did all their importing fromCSV files and believe me they had far more than a few thousand imports to do at certain times of the year.
The following LS agent / function will work for you… You call it as follows …
One trick to rmeember … do not put funny chars or spaces in the field names as Lotus notes will barf…
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim importCount As Long
Set db = s.CurrentDatabase
importCount = ImportCSVToDB( "c:\temp\TestImport.csv", "," , db , "MyFormName", True)
Msgbox "I imported " & Cstr( importCount ) & " documents."
Exit Sub
End Sub
< Function>
Function ImportCSVToDB( fileName As String , delimiter As String , db As NotesDatabase, formName As String, firstLineIsFieldNames As Boolean ) As Long
’ Imports CSV from and makes each line a new document with a formName of
’ If is true then uses the first line of data as field names to create in the document
’ else uses Field_1, Field_2, Field_3…
’ is the seperator character or string (normally a comma or semi colon
’ Returns zero for an error else the number of lines imported
On Error Goto errHandle
Dim doc As NotesDocument
Dim fieldNamesList List As String
Dim textFields As Variant
Dim thisRow As String
Dim rowCount As Long
Dim maxColNum As Integer
Dim colCount As Integer
Dim fileNum% 'Filehandle
' Start off with sanity checks
If delimiter = "" Then delimiter = ","
If fileName = "" Then
Error 32767, "No file name given provided"
End If
If Dir$( fileName ) = "" Then
Error 32766, "The file, '" & fileName & "' does not exist."
End If
' Open the CSV and get the first line
fileNum% = Freefile
Open fileName For Input As #fileNum%
Line Input #fileNum, thisRow
If thisRow = "" Then
Error 32765, "The file called " & fileName & " is empty"
End If
' Split the line of text into values using the delimiter
textFields = Split( thisRow, delimiter )
maxColNum = Ubound( textFields )
' Create the field names array / list
For colCount = 0 To maxColNum
If firstLineIsFieldNames Then
fieldNamesList( colCount ) = textFields( colcount )
Else 'There are no field names in the first row
fieldNamesList( colCount ) = "FIELD_" & Cstr( colcount )
End If
Next colCount
If firstLineIsFieldNames Then
Line Input #fileNum, thisRow
If thisRow = "" Then
Error 32764, "The file called " & fileName & " only contains field names and no data"
End If
End If
' Now to create the documents
Do
rowCount = rowCount + 1
textFields = Split( thisRow, delimiter )
' Check there isn't more data than fields defined in the row
If Ubound( textFields ) > maxColNum Then
Error 32760, "Row number " & Cstr( rowCount ) & " has too many fields in it. "
End If
If rowCount Mod 100 = 0 Then Print "Exporting Row "; rowCount
Set doc = New NotesDocument ( db )
doc.Form = formName
For colCount = 0 To maxColNum
On Error Goto docSave
Call doc.ReplaceItemValue( fieldNamesList( colCount ) , textFields( colCount ) )
Next
docSave:
If Err > 0 Then
Print "There was an error exporting value number ";colCount + 1 ; " in row " ; rowCount
Beep
Err = 0 'get rid of the error
End If
Call doc.Save( True, False)
Set doc = Nothing
Line Input #fileNum, thisRow
Loop Until Eof( fileNum% )
' Export the last row
rowCount = rowCount + 1
textFields = Split( thisRow, delimiter )
' Check there isn't more data than fields defined in the row
If Ubound( textFields ) > maxColNum Then
Error 32760, "Row number " & Cstr( rowCount ) & " has too many fields in it. "
End If
If rowCount Mod 100 = 0 Then Print "Exporting Row "; rowCount
Set doc = New NotesDocument ( db )
doc.Form = formName
For colCount = 0 To maxColNum
On Error Goto docSave2
Call doc.ReplaceItemValue( fieldNamesList( colCount ) , textFields( colCount ) )
Next
docSave2:
If Err > 0 Then
Print "There was an error exporting value number ";colCount + 1 ; " in row " ; rowCount
Beep
Err = 0 'get rid of the error
End If
Call doc.Save( True, False)
Set doc = Nothing
Print "Finished exporting " ; rowCount ; " documents. "
ImportCSVToDB = rowCount
stdExit:
'Clean up all sorts of things here
Close fileNum%
Exit Function
errHandle:
Msgbox "An error occurred." & Chr$(13) & "[ImportCSVToDoc](" & Cstr( Erl ) & Chr$(13) & Error$, 48, "ERROR"
ImportCSVToDB = 0
Goto stdExit
Exit Function
End Function
Subject: RE: Uploading 38000 documents in one go
Tqvm, Shane, really appreciates it, especially your detail explanation and script given.
Subject: RE: Uploading 38000 documents in one go
Tqvm, Shane, really appreciates it, especially your detail explanation and script given.
De Nada, I have written so many scripts similar to that for Stats NZ that I can rattle them off in my sleep.
Take Car,
shane
Subject: Uploading 38000 documents in one go
Look in the Sandbox for Importing MS Excel omtp Notes. I’ve used it a lot and it will upload any number of documents.
Subject: RE: Uploading 38000 documents in one go
Tqvm Sam, it is very neat!!! Just curious, would you happen to came across a faster way of exporting view, but not value of fields, but name of field assigned to respective columns? Problem is that I have created quite a long view, would really appreciates if you happen to know a faster way of doing this…
tq in advance.