3 ms·
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 e
by smhenderson 9y ago
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.
- ajanuary 9y agoIf 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 :(
- ewheeler 9y agoSame 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!
- jandrese 9y agoExcel 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.