5 ms·

The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"

rsanders 6y ago
If CSV were being used just to exchange data with Excel, we probably wouldn't be using CSV. Many systems neither need nor know that ="01" should be treated as the string "01". If Excel were the only intended consumer, .xlsx would be a preferable file format. At least it's mostly unambiguous.
vertere 6y ago
Perhaps by "correct way" you meant "dodgy hack to make Excel happy and risk breaking more sensible implementations"? Excel may predate the RFC but AFAIK MS didn't invent or coin the term CSV, so you can't just say whatever Excel does is correct. The RFC is loose because of nonsense like this, it doesn't mean it was ever a good idea.
- _pmf_ 6y ago
  Do you want to be a useless sophist or develop software?
  - vertere 6y ago
    I sure don't want to have to deal with people putting `="01"` in CSV files.
    - altacc 6y ago
      We have multiple legacy systems where I work, communicating via batch csv files, some of which are authored & curated by staff in Excel. I can confirm that doing this would be a very bad thing indeed and make our systems grind to a halt.
  - dragonwriter 6y ago
    For CSV that isn’t solely targeting Excel, hacks around the way Excel works are useless.
  - edejong 6y ago
    I guess the HN audience has voted. The result is: hacking around Excel stupidities is itself a stupid idea. If you want Excel to understand your output, perhaps use a library which can write xlsx files.
  - OskarS 6y ago
    The reason you use CSV is that you want to use it with software that ISN'T Excel. Otherwise you'd just use .xlsx. No other software uses this convention, and it's not correct CSV.
  - deleted 6y ago
    [deleted]
- stjohnswarts 6y ago
  How in the world is Excel supposed to know which fields you want to be numbers and which to be strings? CSV doesn't have that info built in unless you surround the number with quotes and you select the right process. Excel isn't just a CSV importer, it reads all sorts of files, and it needs some help if you expect it to work. What ever happened to process and a sense of responsibility and craft in your work?
  - vertere 6y ago
    Choosing based on whether it's in quotes seems like a better solution than using an equals sign. Not everything follows that convention admittedly, but not everything understands the '=' either. Or it could just treat everything as text until a user tells it otherwise. But none of that is really the point. Because CSV files aren't just for importing into Excel. One of their main benefits is their portability. In other situations column types might be specified out of band, but even if not, putting equals signs before values is unconventional, so more likely to hurt than help. And in the cases it might help, i.e. when you only care about loading into Excel, then you have options other than CSV, rather than contorting CSV files for Excel's sake. > What ever happened to process and a sense of responsibility and craft in your work? I actually have no idea what you are on about. I'm talking about the "responsibility and craft" of not producing screwed up CSV files. Why do some people find that so offensive? Yes, it is not inconceivable that there could be some situation working with legacy systems where putting `="..."` in CSVs is, unfortunately, your best option. Sometimes you do have to put in a hack to get something done. But don't go around telling people (or yourself) that it is "the correct way".
terwey 6y ago
“Correct” is a strong word. It isn’t defined in https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180 so Excel should not try to be smart and add extensions only they support.
- sheetjs 6y ago
  Excel predates RFC4180 by nearly 20 years (RFC4180 is October 2005, Excel 1.0 was September 1985) and this behavior was already cemented when the RFC was written. As for the actual RFC, it's worth taking a read. Any sort of value interpretation is left up to the implementation, to the extent that Excel's behavior in interpreting formulae is 100% in compliance with the spec.
  - throwaway_pdp09 6y ago
    What spec? Anyway the RFC doesn't mandate any value interpretation IIRC.
  - dtech 6y ago
    CSV predates Excel, and other CSV implementations don't have this behavior
andreareina 6y ago
And now the csv parser (or downstream process) has to guess whether to interpret that as the raw string or as the eval'd value.
account42 6y ago
Not even Excel uses that syntax when exporting to CSV (at least by default).
giantDinosaur 6y ago
I have a list of companies I'd like you to consult for. Coincidentally, they're companies I'd like to work for, and I've quite enjoyed building proper database solutions which replace incredibly hacky/terrible Excel (or Excel adjacent) solutions.
dpwm 6y ago
I ran into this last week with a UK bank. I was offered a CSV file. What I got was a CSV file with excel formulae in it. I actually wanted a CSV file – preferably without having to resort to sed to strip out excel formulae.
zentiggr 6y ago
If you want to write a bespoke CSV generator for an application where you know for sure that the file is only ever going to go straight to an Excel instance, sure. For all the other uses in the world, that's a breaking change.

If CSV were being used just to exchange data with Excel, we probably wouldn't be using CSV. Many systems neither need nor know that ="01" should be treated as the string "01". If Excel were the only intended consumer, .xlsx would be a preferable file format. At least it's mostly unambiguous.

vertere 6y ago

Perhaps by "correct way" you meant "dodgy hack to make Excel happy and risk breaking more sensible implementations"? Excel may predate the RFC but AFAIK MS didn't invent or coin the term CSV, so you can't just say whatever Excel does is correct. The RFC is loose because of nonsense like this, it doesn't mean it was ever a good idea.

_pmf_ 6y ago
Do you want to be a useless sophist or develop software?
- vertere 6y ago
  I sure don't want to have to deal with people putting `="01"` in CSV files.
  - altacc 6y ago
    We have multiple legacy systems where I work, communicating via batch csv files, some of which are authored & curated by staff in Excel. I can confirm that doing this would be a very bad thing indeed and make our systems grind to a halt.
- dragonwriter 6y ago
  For CSV that isn’t solely targeting Excel, hacks around the way Excel works are useless.
- edejong 6y ago
  I guess the HN audience has voted. The result is: hacking around Excel stupidities is itself a stupid idea. If you want Excel to understand your output, perhaps use a library which can write xlsx files.
- OskarS 6y ago
  The reason you use CSV is that you want to use it with software that ISN'T Excel. Otherwise you'd just use .xlsx. No other software uses this convention, and it's not correct CSV.
- deleted 6y ago
  [deleted]
stjohnswarts 6y ago
How in the world is Excel supposed to know which fields you want to be numbers and which to be strings? CSV doesn't have that info built in unless you surround the number with quotes and you select the right process. Excel isn't just a CSV importer, it reads all sorts of files, and it needs some help if you expect it to work. What ever happened to process and a sense of responsibility and craft in your work?
- vertere 6y ago
  Choosing based on whether it's in quotes seems like a better solution than using an equals sign. Not everything follows that convention admittedly, but not everything understands the '=' either. Or it could just treat everything as text until a user tells it otherwise. But none of that is really the point. Because CSV files aren't just for importing into Excel. One of their main benefits is their portability. In other situations column types might be specified out of band, but even if not, putting equals signs before values is unconventional, so more likely to hurt than help. And in the cases it might help, i.e. when you only care about loading into Excel, then you have options other than CSV, rather than contorting CSV files for Excel's sake. > What ever happened to process and a sense of responsibility and craft in your work? I actually have no idea what you are on about. I'm talking about the "responsibility and craft" of not producing screwed up CSV files. Why do some people find that so offensive? Yes, it is not inconceivable that there could be some situation working with legacy systems where putting `="..."` in CSVs is, unfortunately, your best option. Sometimes you do have to put in a hack to get something done. But don't go around telling people (or yourself) that it is "the correct way".

terwey 6y ago

“Correct” is a strong word. It isn’t defined in https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180 so Excel should not try to be smart and add extensions only they support.

sheetjs 6y ago
Excel predates RFC4180 by nearly 20 years (RFC4180 is October 2005, Excel 1.0 was September 1985) and this behavior was already cemented when the RFC was written. As for the actual RFC, it's worth taking a read. Any sort of value interpretation is left up to the implementation, to the extent that Excel's behavior in interpreting formulae is 100% in compliance with the spec.
- throwaway_pdp09 6y ago
  What spec? Anyway the RFC doesn't mandate any value interpretation IIRC.
- dtech 6y ago
  CSV predates Excel, and other CSV implementations don't have this behavior

andreareina 6y ago

And now the csv parser (or downstream process) has to guess whether to interpret that as the raw string or as the eval'd value.

account42 6y ago

Not even Excel uses that syntax when exporting to CSV (at least by default).

giantDinosaur 6y ago

I have a list of companies I'd like you to consult for. Coincidentally, they're companies I'd like to work for, and I've quite enjoyed building proper database solutions which replace incredibly hacky/terrible Excel (or Excel adjacent) solutions.

dpwm 6y ago

I ran into this last week with a UK bank. I was offered a CSV file. What I got was a CSV file with excel formulae in it. I actually wanted a CSV file – preferably without having to resort to sed to strip out excel formulae.

zentiggr 6y ago

If you want to write a bespoke CSV generator for an application where you know for sure that the file is only ever going to go straight to an Excel instance, sure. For all the other uses in the world, that's a breaking change.