Retain Leading Zero in CSV

[Former Member]

Hi,

We are exporting the output in CSV format but CSV is not showing leading zeros.

For example - if the value is 0002 then it appears as 2 in CSV.

Is there any method or trick to retain leading zeros.

Although, this can be done through excel macro(after the file is opened) but for that manual intervention is required.

Is there any way to run excel macro through web page ? I mean if we could automate the procedure of running macro.

so that user just clicks on the button "Export to CSV" on web page and he gets the output in Excel with leading zeros retained.

Regards,

Anil

[jcgood25 · Accepted solution]

If you test the query results in text/xml output what is the SQLDataType in the Column section of the xml? Is your column a number or a string type? Is Excel doing this because it sees the values in the csv as numerical type?

[Former Member · Accepted solution]

Hi Jeremy,

Although i am building a string in BLS and writing it into a csv file

but I tried to test your statement by simulating with test data

For one column of "VARCHAR2" datatype, the value is '0002'

when i test the query results with text/xml output, the SQLDataType="12"

and the value is 0002

but when i test the query results with text/csv output, the value is 2.

Regards,

Anil

[Former Member · Accepted solution]

Hi,

You can force a string type in Excel by adding the double-quote caracter at the beginning of the value.

For example, try to send value "0002 instead of 0002.

The drawback to this is that the double-quote will appear even if content type is something else than text/csv...

Hope it helps,

Tanguy

[Former Member · Accepted solution]

Hi Tanguy,

We have already tried the same with Single Quote..yeah the problem is single quote appears and business do not accept that.

Regards,

Anil

[Former Member · Accepted solution]

This is an Excel problem not an MII problem. We have the same issue and can open the csv file with notepad and it shows in full precision, but if you open it within Excel it trims the leading zeroes. Be careful not to open the file in Excel and then save it because that will remove the leading zeroes.

We process the CSV files with SAS or another statistical analysis tool and that shows the leading zeroes correctly, but not sure if that'll work for you.

[Former Member · Accepted solution]

Hi Anil,

If you want to retain the leading zeros you can use an equal sign in front of your double quoted values.

like that:

="001.445",="00005",="001.445"

That should do the trick.

Cheers,

Arnaud

[Former Member]

Hi Arnaud,

Thanks a lot... trick worked.

Regards,

Anil