BroadAustralia
asked on
Import CSV into Domino - works but my fields are enclosed with " - how to bypass these characters?
Hello All!
I am having to import CSV data into my Lotus Notes database ( 6.5.3 )
The sample import routine I found in the SandBox can import the data if it is solely delimited by commas, however my data is in the following format:
"BOB","Kogh","20 - 30","","","","2 Durham Street","","WEST END","QLD","4222","AUSTRAL IA","04111 1111",""," ",""
I am a newbie to Lotus Notes development can someone assist in getting my data in?
The agent initialize code is as follows:
-------------------------- ------
Sub Initialize
Dim session As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim fileNum As Integer, cells As Integer, k As Integer
Dim fileName As String
Dim InputStr As String, delimiter As String
Dim FieldArray As Variant
fileNum% = Freefile()
fileName$ = "c:\apps\names.csv" ' Location of your file
delimiter = "'" ' Delimiter of your file
cells = 4 ' How many cells + 1
k = 0
Open fileName$ For Input As fileNum%
Do While Not Eof(fileNum%)
Line Input #1, InputStr$
FieldArray = parseall(InputStr$, delimiter, cells)
response% = CreateDocument(FieldArray)
k = k + 1
Print "Document Created: " + Cstr(k)
Loop
Close fileNum%
End Sub
-------------------------- ------
With the current setup my data does come in but with " either side...
ie. "BOB" "Keogh"
I tried changing the delimiter but this created a bigger mess ;)
Here is the code for the parseall function:
-------------------------- -------
Function parseall(Initialstr As String, delimiter As String, cells As Integer) As Variant
CRcr$ = Chr(13)
IniVar$ = initialstr
numchars = Len(IniVar$)
Redim cols(cells)
Redim coldata(cells)
cols (0) = 0
For numcol = 1 To cells
prevcol = cols(numcol -1 )
cols (numcol) = Instr (cols(numcol - 1) + 1, IniVar$, Delimiter$)
StartCol = Cols(numcol - 1) + 1
If Cols (numcol) = 0 Then
endcol = numchar + 1
Else
endcol = cols(numcol)
End If
FieldLenght = EndCol - StartCol
If FieldLenght <= 0 Then
FieldLenght = 50
End If
ColData(numcol) = Trim$(Mid$(IniVar$, StartCol, FieldLenght))
CRPos% = Instr (1, ColData(numcol), CRcr$)
If (CRPos% >= 1) And (numcol <=2) Then
TempStr$ = ColData(numcol)
ColData(numcol) = Trim$(Mid$(IniVar$, StartCol, CRPos%-1))
End If
If cols (numcol) = 0 Then Exit For
Next
parseall = coldata()
End Function
-------------------------- -------
Is there a change I can make to the code to fix my problem?
Regards,
Broad.
I am having to import CSV data into my Lotus Notes database ( 6.5.3 )
The sample import routine I found in the SandBox can import the data if it is solely delimited by commas, however my data is in the following format:
"BOB","Kogh","20 - 30","","","","2 Durham Street","","WEST END","QLD","4222","AUSTRAL
I am a newbie to Lotus Notes development can someone assist in getting my data in?
The agent initialize code is as follows:
--------------------------
Sub Initialize
Dim session As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim fileNum As Integer, cells As Integer, k As Integer
Dim fileName As String
Dim InputStr As String, delimiter As String
Dim FieldArray As Variant
fileNum% = Freefile()
fileName$ = "c:\apps\names.csv" ' Location of your file
delimiter = "'" ' Delimiter of your file
cells = 4 ' How many cells + 1
k = 0
Open fileName$ For Input As fileNum%
Do While Not Eof(fileNum%)
Line Input #1, InputStr$
FieldArray = parseall(InputStr$, delimiter, cells)
response% = CreateDocument(FieldArray)
k = k + 1
Print "Document Created: " + Cstr(k)
Loop
Close fileNum%
End Sub
--------------------------
With the current setup my data does come in but with " either side...
ie. "BOB" "Keogh"
I tried changing the delimiter but this created a bigger mess ;)
Here is the code for the parseall function:
--------------------------
Function parseall(Initialstr As String, delimiter As String, cells As Integer) As Variant
CRcr$ = Chr(13)
IniVar$ = initialstr
numchars = Len(IniVar$)
Redim cols(cells)
Redim coldata(cells)
cols (0) = 0
For numcol = 1 To cells
prevcol = cols(numcol -1 )
cols (numcol) = Instr (cols(numcol - 1) + 1, IniVar$, Delimiter$)
StartCol = Cols(numcol - 1) + 1
If Cols (numcol) = 0 Then
endcol = numchar + 1
Else
endcol = cols(numcol)
End If
FieldLenght = EndCol - StartCol
If FieldLenght <= 0 Then
FieldLenght = 50
End If
ColData(numcol) = Trim$(Mid$(IniVar$, StartCol, FieldLenght))
CRPos% = Instr (1, ColData(numcol), CRcr$)
If (CRPos% >= 1) And (numcol <=2) Then
TempStr$ = ColData(numcol)
ColData(numcol) = Trim$(Mid$(IniVar$, StartCol, CRPos%-1))
End If
If cols (numcol) = 0 Then Exit For
Next
parseall = coldata()
End Function
--------------------------
Is there a change I can make to the code to fix my problem?
Regards,
Broad.
Hi BroadAustralia,
replace this line:
delimiter = "'" ' Delimiter of your file
with this one:
delimiter = ";" ' Delimiter of your file
and try then.
Hope this helps,
Marko
replace this line:
delimiter = "'" ' Delimiter of your file
with this one:
delimiter = ";" ' Delimiter of your file
and try then.
Hope this helps,
Marko
BroadAustralia,
sorry that wont do it. Didn't read carefuly.
But I'll get you the solution.
Hope this helps,
Marko
sorry that wont do it. Didn't read carefuly.
But I'll get you the solution.
Hope this helps,
Marko
BroadAustralia,
do you have your data in Excel?
If not you can try to import it (Data - Import external data - Import data; and then in "Files of type" choose Text files (*.txt, *.prn, *.csv, *.tab, *.asc) ),
and then export as csv without quotation marks.
Hope this helps,
Marko
do you have your data in Excel?
If not you can try to import it (Data - Import external data - Import data; and then in "Files of type" choose Text files (*.txt, *.prn, *.csv, *.tab, *.asc) ),
and then export as csv without quotation marks.
Hope this helps,
Marko
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I often use COL files myself, like mbonaci suggest. However, only for new imports. If existing documents may need updating, or if special handling is required, I too use Lotusscript.
You may want to modify the found values using
StrRight(..., """") and StrLeftBack(..., """")
i.e. IF the field starts / ends with a quote. Too often people end up getting empty values and/or errors on fields that no not contain those.
Several places appear suitable to adopt the change.
Easiest stop might be right after setting ColData(numcol).
Good luck!
You may want to modify the found values using
StrRight(..., """") and StrLeftBack(..., """")
i.e. IF the field starts / ends with a quote. Too often people end up getting empty values and/or errors on fields that no not contain those.
Several places appear suitable to adopt the change.
Easiest stop might be right after setting ColData(numcol).
Good luck!
ASKER
Hi Marko @ Crak
Thanks for your time! I ended up using the .COL file to import the data. It throws up an error when importing - < Delimiter "," for field 'Show' not found > -- as when the fields I am importing are empty there are just multiple commas.. ie ,,,,,
The data however appears OK. Is there a way to get around this without using Lotus Script? ( Maybe best to get creator of file to use inverted comma's even the fields empty... "","","" as opposed to ,,,,
Crak I will probably revisit this when I *need* to use Lotus Script for an import -- right now I am just happy to get the data in there ;)
Regards
Broad.
Thanks for your time! I ended up using the .COL file to import the data. It throws up an error when importing - < Delimiter "," for field 'Show' not found > -- as when the fields I am importing are empty there are just multiple commas.. ie ,,,,,
The data however appears OK. Is there a way to get around this without using Lotus Script? ( Maybe best to get creator of file to use inverted comma's even the fields empty... "","","" as opposed to ,,,,
Crak I will probably revisit this when I *need* to use Lotus Script for an import -- right now I am just happy to get the data in there ;)
Regards
Broad.
ASKER
Woops! I was looking at old sample sorry!
The fields are all surrounded by " 's but I get the error noted above - always on import of the last field for each record... ( as IT is missing the delimiter ).
Modified the COL file to not expect the delimiter on the last record and all sweet!
ID: type text until '","';
Show: type text; < last record
Thanks again
Broad.
The fields are all surrounded by " 's but I get the error noted above - always on import of the last field for each record... ( as IT is missing the delimiter ).
Modified the COL file to not expect the delimiter on the last record and all sweet!
ID: type text until '","';
Show: type text; < last record
Thanks again
Broad.
ASKER
By this I mean the delimiter specified in the code:
--------------------------
delimiter = "'" ' Delimiter of your file
--------------------------