5 ms·

> Use the right tool. Step one should be learn your tool, no matter the tool. There's several ways to avoid the issue in Excel. If someone working with Excel

by qayxc 5y ago

> Use the right tool. Step one should be learn your tool, no matter the tool. There's several ways to avoid the issue in Excel. If someone working with Excel isn't able to learn that (heck, a simple template would suffice), I have no hope the same demographic would have any success with R or Python.

pletnes 5y ago
As someone who has programmed in python for a decade, and no idea how to do anything right in Excel, I would beg to differ.
- qayxc 5y ago
  Do you use Excel on a daily basis for productive work as well? If not then your personal experience is irrelevant in this context.
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 ago
  If 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 ago
    Yeah, 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.
    - 5y ago

by qayxc 5y ago

pletnes 5y ago

As someone who has programmed in python for a decade, and no idea how to do anything right in Excel, I would beg to differ.

qayxc 5y ago
Do you use Excel on a daily basis for productive work as well? If not then your personal experience is irrelevant in this context.

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 ago
If 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 ago
  Yeah, 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.
  - 5y ago