Hacker News Reader

Comment by autra | Hacker News Reader

> The documented way is prefixing with a ' character. It doesn't have the length issue either.

It is suggested in comments, but the author answered

> Yes, this prevents formula expansion... once. Unfortunately Excel's own CSV exporter doesn't write the ', so if the user saves the ‘safe’ file and then loads it again all the problems are back.

:-/

View on HN

That's it. My pet peeve issue with Excel/CSV is USA zip codes. Excel will happily eat leading zeros. There is a specific number format to correct that. If you export that file to CSV with the format set the CSV file will have 5 digits. If you reopen that CSV file in Excel it gobbles up the zeros all over again.

As someone mentioned elsewhere this is an issue with long numbers. Excel converts them to scientific notation. Reformat and export, all good. Reopen said file, back to scientific notation.

Really anything that relies on an escape character (') or a specific format gets lost on export to CSV. It exports correctly but there is simply no way to document these formats in a CSV file and have it be compatible with anything but Excel.

If you use Data -> From Text, and on Step 3 select all the columns and make them "Text", that will prevent Excel from mangling any of the data (stripping leading zeros, evaluating strings starting with = as formula etc.)

Not the best experience :(

Same with phone numbers. In most parts of the world, local numbers (not fully-qualified with country code), are written/dialed with a leading zero. Excel eats these and/or uses scientific notation!

Excel somehow has one of the worst CSV parsers I have ever used. You would think this is something that should be bread and butter for a spreadsheet app, but it does a surprisingly terrible job.

Sounds to me like the elephant in the room is using Excel in the first place, despite how entrenched it is.

So data entered safely into Excel, exported from Excel, and imported back into Excel... can inject code.

Amazing.

Subtly worse. Save the CSV with the `, then close it and load it again. Boom. Formula works again. (Just tested it.)

Technically it's exported and imported I suppose, but it makes no difference to the user.

Does that occur with the tab character?

Reply on news.ycombinator.com