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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

628 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