CSV File Anomaly?

HCL Domino Domino Forum
1

Hi,

I am using LotusScript to try and read a CSV file. One of the field values contains a comma, and hence the value is enclosed in double quotes.

The LotusScript is ignoring the double quotes just fine, but it is not treating the comma as part of the field value. It is separating the field value at the comma.

Does anybody know why?

Regards,

Charlie Zeaiter.

P.S. I am using Notes v6.0.2 CF1

For what it’s worth, here is the input file:

Sydney Uni, 0, 0, Australia

“Uni of Tech, Sydney”, 0, 0, Australia

And here is the Lotus Script:

Sub Initialize

Dim inputfilenum As Integer

inputfilenum = Freefile()

Open "c:\temp.txt" For Input As inputfilenum



Dim strCol1 As String

Dim strCol2 As String

Dim strCol3 As String

Dim strCol4 As String



Do While Not Eof (inputfilenum)

	Input #inputfilenum, strCol1, strCol2, strCol3, strCol4

	

	Messagebox "1: " + strCol1 _

	+ Chr(10) + "2: " + strCol2 _

	+ Chr(10) + "3: " + strCol3 _

	+ Chr(10) + "4: " + strCol4

	

Loop



Close #inputfilenum

End Sub

2

Subject: CSV File Anomaly?

Hi Charlie,

First of all you should make sure that all entries in you CSV are enclosed in double quotes, after that do it like this:

DIM ValArray as variant

dim myTextLine as string

.

.

.

Line Input inputfilenum, myTextLine

while not EOF(inputfilenum)

ValArray = split(myTexLine,{“,”})

'ValArray(0) contains strCol1,

'ValArray(1) contains strCol2 and so on

’ Do something with those values

line input #1, myTextLine

wend

Just typed this without testing, so please excuse any typing errors.

HTH,

Peter

3

Subject: RE: CSV File Anomaly?

Thanks Peter, you are right. If every entry in the CSV file are enclosed in double quotes then it will work just fine.

But how do I that? I have an Excel spreadsheet, and when I save it in a CSV format, it will only enclose the entries with a comma with double quotes.

Does anybody know how I can force Excel to save a spreadsheet so that every entry in a CSV file is within double quotes?

Thanks in advance.

4

Subject: CSV File Anomaly?

Hello Charlie,

We use LotusScript to upload comma delimited files alot and have had the problem you are facing. We found it is best to load the line into a single variable and then parse out the fields ourselves. This will give you control over what gets parsed out. For example if you find a quote you can continue to parse until you come across the end quote.