Export Data to CSV

HCL Domino Domino Forum
1

Hi Lotus Domino Gurus,

I am in the process of exporting data to CSV file. The data is getting successfully populated to the respective cells, however there is one column that has huge data in it.

The underlying code for this field is split based on , delimiter.

arr = Split(doc.ProblemDescription(0), “,”)

For jp = 0 To UBound(arr)

problemdesc = problemdesc & arr(jp)

Next

The code used to write data in csv file is Write #filenum, fieldvalue1, fieldvalue2, etc. Instead of Print statement I used Write.

On commenting above code other cells got adjusted, still there appears some data mismatch here and there.

The field problem description contains comma (,) & double quotes etc values.

Is this due to this? How can we handle this?

Please help, many thanks in advance.

Thanks & Regards,

Pervela Mallik Ram Sharma

2

Subject: Export Data to CSV

Yes, the quotes in the field would do that. There is no way for Excel (I assume you are opening the CSV file in Excel) to know what quote is part of the cell content and which is used as a delimiter.

You could replace the quotes in the content with another string (for example ") or even differenct character, perhasp single quote.

3

Subject: RE: Export Data to CSV

Excel can understand Commas (and other correctly written CSV handlers) and " if the CSV is properly formatted.

So a value of say

Hello “gorgeous, wonderful” World

would be:

“Hello ““gorgeous, wonderful”” World”

in the CSV file.

The comma is not treated as a separator as it is within paired double quotes. and the double double quotes are converted to a single double quote as they are within the paired double quotes

So just do a replace(SourceString,{“},{”"}) on your string before printing/writing

4

Subject: RE: Export Data to CSV

See, I learned something new. :slight_smile:

5

Subject: RE: Export Data to CSV

Thanks Karl Henry and Carl,

We are opening this in Lotus Symphony as MS-Office is not installed.

Thanks & Regards,

Pervela Mallik Ram Sharma

6

Subject: And?

Does symphony not read the CSV correctly?

post a few lines of your CSV file.

7

Subject: RE: And?

Hi Carl,

Thanks your code help me a lot. Now the output displays in the desired format.

Replace(field, {“}, {”"})

Thanks a lot.

Thanks & Regards,

P. Mallik Ram Sharma

8

Subject: Possibly use other delimeters

Generally when I’m pushing delimited data out to a file, I’ll use something that is highly likely not to be in the data set; for instance a tilde ~ or veritial pipe |. Generally the receiving apps can parse based on any delimiter.