Re:Autocorrect errors in Excel still creating genomics headache
by da_chicken on Tuesday August 17, 2021 @03:56PM
(#28214121)
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.
Re:Autocorrect errors in Excel still creating genomics headache
by dragonwriter on Tuesday August 17, 2021 @04:03PM
(#28214203)
> 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.
Re:Autocorrect errors in Excel still creating genomics headache
by da_chicken on Tuesday August 17, 2021 @07:33PM
(#28216005)
Not 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.
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.