5 ms·
> There's several ways to avoid the issue in Excel. No, not really. There are several ways to help reduce the issue, but none of them eliminate what it does.
by da_chicken 5y ago
> There's several ways to avoid the issue in Excel.
No, not really. There are several ways to help reduce the issue, but none of them eliminate what it does.
Example that comes to mind is the data file for College Board's SAT test. The data formats for student reports for schools come in two formats: PDF (one page per student), CSV, and fixed-width. That is the comprehensive list of your options. College Board doesn't care about you as a customer. They're too big. Any request you submit will be black holed.
Some of the columns in the file indicate a range, usually in the format "X-Y". Excel will try to coerce that into a date, if it's valid.
Other columns indicate a ratio, expressed as "X/Y". Excel will coerce that into a date, if it's valid.
Other columns indicate an ID number, expressed as a large, fixed-digit number, zero-padded. Excel will coerce that into an integer, or, if it's too long, into scientific notation discarding digits.
It doesn't matter how you format the CSV. Excel will do the above.
Here's an example CSV:
ID,Range,Ratio
12345678901234567890,8-9,"7/15"
"00000000000000012345","9-10",21/35
I open that with Excel and immediately save it as a CSV. I look at the file in a text editor and I see:
ID,Range,Ratio
1.23457E+19,9-Aug,15-Jul
12345,10-Sep,21/35
Do you have any idea how fun it is to explain to teachers and school administrators what happened here?
The correct way to work with this data file is: Do not, under any circumstances, open it with Excel if you expect to use it for anything else.
The problem is, there are very few applications that work well with CSV files. I know of CsvEd and Delimit. There are several text editors with a CSV column mode that makes the file look like a table (with varying degrees of success). All of these vary between "godawful" and "a complete nightmare" in terms of performance and usability compared to Excel.
- occamrazor 5y agoIf you open the csv file with Excel you get those errors. If instead one uses Get&Transform (aka Power Query) then it is possible to specify the type of each column.
- dragonwriter 5y ago> It doesn't matter how you format the CSV. Excel will do the above. Only if you open the file, so that Exel assumes defaults for formatting. If you import the CSV (Data -> From Text in the current UI) you can specify the format with the Import Text Wizard as described on the College Board instructions for using the file. Except... Unfortunately, the College Board has outdated instructions on their website; Excel used to offer the Import Text Wizard on opening a csv or text file, rather than making default assumptions, and the College Board instructions page, while it does tell you to use that Import Text Wizard and provide all the details of what to plug into that wizard, tells you to open the file to get it, which bypasses the wizard. Things like this is why clerical and other low-level positions involving excel test candidates on proficiency with specific versions of excel. Higher-level workers are expected to be able to figure out these kind of changes themselves, though (or consume lower-level staff time, if they are in management.
- da_chicken 5y agoYeah, that's also obnoxious. The Data From Text wizard lets you easily import the data... but if you do that then the first row no longer defaults to headers. So if you open it that way and want to filter or sort the data, your headers will disappear. You have to go into full blown Power Query to do both prevent reformatting and use column headers, and you have to reassign the data types on a much more complex interface. We have now changed a 1 second automated process that anybody could do, into a 2 minute manual process that requires knowledge of data types. We've gone from a non-technical task to a highly technical task. To open a file without completely munging the data. It also will sometimes do weird things like exclusive lock the file on disk until you close every open window of Excel because it creates data connections to the file.
- dragonwriter 5y ago> The Data From Text wizard lets you easily import the data... but if you do that then the first row no longer defaults to headers. So if you open it that way and want to filter or sort the data, your headers will disappear. You have to go into full blown Power Query to do both prevent reformatting and use column headers You don't, because while the wizard doesn't default to headers, the first screen of the wizard has a checkbox for it, so the option is there without Power Query.
- da_chicken 5y agoNot on my Excel 2019. You pick "scan first 200" and it sets first rows to headers. You set it to all text, and it disables first row headers. There is no checkbox in the wizard.