#777 Exported CSV lose leading zeroes when imported in a spreadsheet software

[Sylvain Brunerie]

When exporting a set of rows as CSV, number-like values are not enclosed in quotes, regardless of the type of field.
A varchar value like 06700 will be written in the CSV as …,06700,….

Then, when using a spreadsheet software to import the CSV and modify it, the program usually interpret that value as a numeric one, and ignores the leading 0. When saving the file back to CSV, the leading zero will have disappeared.

Would it feel sensible to enclose all char/text values in quotes?
(It won’t necessarily fix it for all spreadsheet softwares, but at least in LibreOffice there’s is an option to take into account quoted values and treat them as text.)

[Stephan]

You can already set this in LibreOffice. During import, select all columns and define them as text. So leading zeros are preserved.

[Sylvain Brunerie]

Yes, but it’s quite unpractical and has to be done every time you open a CSV file, so it’s very much error-prone (whereas the "treat quoted values as text" option stays enabled for the following imports if it wasn’t already).
I just thought it might make sense to do that for text values only.

[Stephan]

whereas the "treat quoted values as text" option stays enabled for the following imports if it wasn’t already

In my experience this may or may not lead to disabling of number detection depending on the reading software and its version.

However, adding quotes should not lead to different handling of the cell data.

Usage of quotes is defined in RFC4180 section 2.5 and following. There is no reference in there to quote numbers differently than text. If your reading program makes a difference than it does not follow the specs.
https://tools.ietf.org/html/rfc4180#section-2

[Sylvain Brunerie]

Since CSV has (I think) no concept of numbers vs strings, it makes sense that the specification doesn’t specify anything in regard to quoted values depending on the type.
Still, the spreadsheet program is converting the CSV to its own format so to me it’s not going against the specs either.
But in any case, you’re right, I guess this shouldn’t be the CSV export’s concern, more the spreadsheet program’s.