Import CSV into Domino - works but my fields are enclosed with " -  how to bypass these characters?

Posted on 2005-12-14
Last Modified: 2013-12-18
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)
      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
                  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    
      parseall = coldata()
End Function

Is there a change I can make to the code to fix my problem?

Question by:BroadAustralia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3

Author Comment

ID: 15487263
>> 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

LVL 22

Expert Comment

ID: 15489001
Hi BroadAustralia,

replace this line:
    delimiter = "'"   ' Delimiter of your file
with this one:
    delimiter = ";"   ' Delimiter of your file

and try then.

Hope this helps,
LVL 22

Expert Comment

ID: 15489036
sorry that wont do it. Didn't read carefuly.

But I'll get you the solution.

Hope this helps,
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 22

Expert Comment

ID: 15489082
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,
LVL 22

Accepted Solution

mbonaci earned 500 total points
ID: 15489097
LVL 13

Expert Comment

ID: 15493308
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!

Author Comment

ID: 15509325
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 ;)



Author Comment

ID: 15509384
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


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Lotus Notes 8.0 Forward emails for 30 days. 8 115
Domino not switching to TLS 1.0 4 814
VBA Lotus notes 2 203
Calculated single value Text Fields display with scroll arrows 7 58
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question