Clarity

Hello All,

I wrote a gel script to create a CSV file which uses 'comma' as delimiter. This file will then be saved in a custom object through xog for the users to access. So, the output should be 'pretty' (data displayed in separate columns). All is well but data splits when there is a 'comma' within the data. I used other delimiters (tab, pipe and caret) to generate the CSV file  but the output by default isn't well formatted unless I change the excel delimiter setting in my local machine. (http://www.howtogeek.com/howto/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/)

Is there a way to escape 'comma' in the data while writing to a CSV with delimiter as 'comma'?

or is there a way to display formatted output while using other delimiters?

I am sure there must be way already. Thanks in Advance.

Hi Krupa,

There has been some discussion on this, see if this helps Delimiter types

Regards

Suman Pramanik

Hi Suman,

I did check that already. The solution there mentions in changing the local system settings to update excel delimiter from comma to pipe. (Same link I posted above)

Since, I am displaying the file in clarity, I want the file to be formatted by default (data displayed in separate columns).

Thanks,

Krupa

Hi Krupa,

How are you displaying the file in Clarity? can you please explain that.

Regards

Suman Pramanik

Create the CSV file and save it as an attachment in a custom object instance through xog.

Try wrapping text fields in your CSV output with double-quotes.

E.g.:

1, 2, 3, "I am text", "so am I, I think", 6

I think that should result in 6 columns in Excel, containing:

1

2

3

I am text

so am I, I think

6

So for data fields that contain a comma, you should just be able to wrap them in a double quote.

Per the Common Format and MIME Type for Comma-Separated Values (CSV) Files:

https://www.ietf.org/rfc/rfc4180.txt

Fields containing line breaks (CRLF), double quotes, and commas   should be enclosed in double-quotes.

V/r,

Gene

Thank you Nick and Gene ! It worked !

I did try wrapping it in double quotes before when writing it to the file, it didn't work. Now, I modified the sql code to wrap the data in double quotes. It worked.