Link to home
Start Free TrialLog in
Avatar of BroadAustralia
BroadAustraliaFlag for Australia

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","AUSTRALIA","041111111","","",""

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.
Avatar of BroadAustralia
BroadAustralia
Flag of Australia image

ASKER

>> I tried changing the delimiter but this created a bigger mess ;)


By this I mean the delimiter specified in the code:
-------------------------------
     delimiter = "'" ' Delimiter of your file
-------------------------------

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
BroadAustralia,
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
ASKER CERTIFIED SOLUTION
Avatar of mbonaci
mbonaci
Flag of Croatia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CRAK
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!
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.
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.