• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8272
  • Last Modified:

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.
0
BroadAustralia
Asked:
BroadAustralia
  • 4
  • 3
1 Solution
 
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
-------------------------------

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

But I'll get you the solution.

Hope this helps,
Marko
0
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!

 
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
0
 
mbonaciCommented:
0
 
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!
0
 
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.
0
 
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now