5 ms·
>Excel hates CSV. It clearly means CSV must be doing something right. Use tabs as a delimiter and excel interops with the format as if natively.
by mcdonje 1y ago
>Excel hates CSV. It clearly means CSV must be doing something right.
Use tabs as a delimiter and excel interops with the format as if natively.
- tacker2000 1y agoThe problem is that nobody in the real world uses tabs. Everyone uses , or ; as delimiters and then uses either . or , for decimals, depending on the source. It shouldnt be so hard to auto-detect these different formats, but somehow in 2025, Excel still cannot do it.
- sfn42 1y agoYou don't need to auto-detect the format. The delimiter can be declared at the top of the file as for example sep=;
- yrro 1y agoBut now that's not CSV. It's CSV with some kind of ad-hoc header...
- sfn42 1y agoIt may not be part of any official CSV spec but Excel accepts it. I found that Excel would read my files much more reliably using the sep declaration, which is great when the target audience is less technical types.
- IAmBroom 1y agoI love you forever and a day. Thank you.
- sfn42 1y agoHappy to help! :)
- tacker2000 1y agoOk thats a nice tip, but to be fair when i download some CSV report off some website, i dont wont to open it to check the delimiter, then edit it and resave it. Often I am downloading dozens of such files at a time.
- sfn42 1y agoThe idea is that the program that creates the file adds that line at the top. If you're downloading CSV files from websites then ideally they should already have that line. If they don't then what you could do is create a simple script that just adds that line, and Excel will open the files without you having to hassle with making sure Excel interprets them correctly. Of course that's a bit more challenging if they use different separators, but you might be able to find an easy adaptation for your usecase like making a decision about which delimiter to declare based on the filename. Or you could try to analyze the header row to figure out which delimiter to use based on that.
- deleted 1y ago[deleted]
- pragmatic 1y agoPipe enters the chat. For whatever reason, pipe seems to be support common in health care data.
- gentooflux 1y agoUse tabs as a delimiter and it's not CSV anymore, that's TSV.
- mcdonje 1y agoThey're essentially the same format. Same with PSV. They're all DSVs. Most arguments for or against one apply to all. https://en.m.wikipedia.org/wiki/Delimiter-separated_values https://en.m.wikipedia.org/wiki/Delimiter-separated_values
- roelschroeven 1y agoIt still can't properly deal with CSVs that use different decimal separators than the UI setting in Excel / Windows. It's still too stupid to understand that UI localization and interoperation should never be mixed.
- IAmBroom 1y agoYes, again: Excel is not the ideal CSV tool. It is A CSV tool, readily available in the business world, that often works quite well. And your argument about comma separators is wrong; the string 1,234 in a CSV file SHOULD mean "two values: 1 and 234", regardless of the local decimal separator. The number one thousand two hundred thirty-four is represented as "1,234"
- roelschroeven 1y ago"And your argument about comma separators is wrong; the string 1,234 in a CSV file SHOULD mean "two values: 1 and 234", regardless of the local decimal separator." Yes, I agree, it SHOULD mean that, but that is NOT what Excel does when the decimal separator is set to "," in the regional settings. Excel wrongly thinks it should apply the comma as decimal separator, and reads that number as 1 unit and 234 thousands. Locale MUST NOT be used for data formats, but Excel does it anyway. This problem doesn't manifest itself when you're using a locale which matches the CSV's separators. Consider yourself lucky if you're in that situation.
- sevensor 1y agoI was writing a program a little while ago that put data on the clipboard for you to paste into Excel. I tried all manner of delimiters before I figured out that Excel really loves HTML tables. If you wrap everything in <tr> and <td>, and put it up on the clipboard, it pastes flawlessly.
- kelvinjps10 1y agoIsn't that tsv then?
- mcdonje 1y agoAnswered: https://news.ycombinator.com/item?id=45195713 https://news.ycombinator.com/item?id=45195713
- kelvinjps10 1y agoThanks for explanation