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.
LVL 1
BroadAustraliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BroadAustraliaAuthor Commented:
>> 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
-------------------------------

mbonaciCommented:
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
mbonaciCommented:
BroadAustralia,
sorry that wont do it. Didn't read carefuly.

But I'll get you the solution.

Hope this helps,
Marko
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

mbonaciCommented:
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
mbonaciCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CRAKCommented:
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!
BroadAustraliaAuthor Commented:
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.
BroadAustraliaAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.