Sometimes it's easier to click a checkbox in Excel than rewrite genetics
October 23, 2023 1:46 PM Subscribe
This little "feature" took several years off my life once when I was having to deal with a shitty zillion-line Excel that included case numbers for cases that had been brought in violation of the law and needed to have their judgments vacated (the case number being single most essential piece of identifying information). Case numbers are also mixed numbers and letters in varying formats. Well...you can guess what happened.
posted by praemunire at 1:59 PM on October 23, 2023 [6 favorites]
posted by praemunire at 1:59 PM on October 23, 2023 [6 favorites]
Q: How many Excel developers does it take to change a light bulb?
A: Jan 1, 1970.
The joke is from a mastadon post I can no longer find.
posted by ecco at 2:04 PM on October 23, 2023 [62 favorites]
A: Jan 1, 1970.
The joke is from a mastadon post I can no longer find.
posted by ecco at 2:04 PM on October 23, 2023 [62 favorites]
This is why our Dark Lord invented CSV, Python and Pandas.
posted by signal at 2:04 PM on October 23, 2023 [11 favorites]
posted by signal at 2:04 PM on October 23, 2023 [11 favorites]
Well, hell, what am I supposed to rant fulminously about in my metadata and research-data management courses NOW?!
posted by humbug at 2:10 PM on October 23, 2023 [6 favorites]
posted by humbug at 2:10 PM on October 23, 2023 [6 favorites]
How about a third option that mimics functionality Excel already has when importing space-delimited data: Prompt for type conversion rather than doing it automatically. If I were forced to use Excel for that kind of stuff, I'd hate to be limited to "Get Excel tooling at the risk of your data being corrupted" or "Don't get your data corrupted but lose Excel tooling".
posted by Ickster at 2:16 PM on October 23, 2023 [2 favorites]
posted by Ickster at 2:16 PM on October 23, 2023 [2 favorites]
I hesitate to even attempt to calculate the number of hours I have spent in my career as a systems administrator for a college bookstore fighting Excel's insistance for formatting ISBNs (a 13 digit number) to scientific notation.
posted by cmdnc0 at 2:21 PM on October 23, 2023 [18 favorites]
posted by cmdnc0 at 2:21 PM on October 23, 2023 [18 favorites]
Well, hell, what am I supposed to rant fulminously about in my metadata and research-data management courses NOW?!
You mean you don't come pre-equipped with a long list of data hygiene things to yell about while waving your arms that involve you as a cautionary example?
posted by GCU Sweet and Full of Grace at 2:22 PM on October 23, 2023 [5 favorites]
You mean you don't come pre-equipped with a long list of data hygiene things to yell about while waving your arms that involve you as a cautionary example?
posted by GCU Sweet and Full of Grace at 2:22 PM on October 23, 2023 [5 favorites]
And they told us excel wasn't a database
posted by Carillon at 2:27 PM on October 23, 2023 [2 favorites]
posted by Carillon at 2:27 PM on October 23, 2023 [2 favorites]
Q: How many Excel developers does it take to change a light bulb?
A: Jan 1, 1970.
That sounds more like a Unix joke. Wouldn't the punchline of the Excel joke be "Jan 1, 1904"?
posted by clawsoon at 2:29 PM on October 23, 2023 [10 favorites]
A: Jan 1, 1970.
That sounds more like a Unix joke. Wouldn't the punchline of the Excel joke be "Jan 1, 1904"?
posted by clawsoon at 2:29 PM on October 23, 2023 [10 favorites]
One simple change they could make which would massively improve this area would be to make double clicking on a .CSV file have the same behaviour as opening it with File > Open.
posted by Lanark at 2:36 PM on October 23, 2023 [2 favorites]
posted by Lanark at 2:36 PM on October 23, 2023 [2 favorites]
Q: How many CSV developers does it take to change a light bulb?
A: An empty table containing the columns "Jan 1" and "1970"
posted by credulous at 2:39 PM on October 23, 2023 [9 favorites]
A: An empty table containing the columns "Jan 1" and "1970"
posted by credulous at 2:39 PM on October 23, 2023 [9 favorites]
is Access still a thing?
Let's talk about databases, and how Access was not...
I recently tried to create an OpenOffice spreadsheet for pretend betting on NFL games. OMFG. The hoops I had to jump through. Should have just written it in Delphi. What was I thinking?
posted by Windopaene at 2:52 PM on October 23, 2023 [2 favorites]
Let's talk about databases, and how Access was not...
I recently tried to create an OpenOffice spreadsheet for pretend betting on NFL games. OMFG. The hoops I had to jump through. Should have just written it in Delphi. What was I thinking?
posted by Windopaene at 2:52 PM on October 23, 2023 [2 favorites]
Oh great so now some copies of Excel will still silently corrupt data. And others won't. And it will depend on which small point release of Excel you have installed and whether you checked something one of the 100+ settings. Ugh.
Although I can't tell, maybe the setting is saved with a file. So if I load someone else's spreadsheet on my Excel it will be broken (or not) depending on however they happened to have configured their settings. That may be marginally better.
posted by Nelson at 2:55 PM on October 23, 2023 [4 favorites]
Although I can't tell, maybe the setting is saved with a file. So if I load someone else's spreadsheet on my Excel it will be broken (or not) depending on however they happened to have configured their settings. That may be marginally better.
posted by Nelson at 2:55 PM on October 23, 2023 [4 favorites]
Optimist: the glass is half full.
Pessimist: the glass is half empty.
Excel: the glass is January 2nd.
posted by Pickman's Next Top Model at 3:00 PM on October 23, 2023 [35 favorites]
Pessimist: the glass is half empty.
Excel: the glass is January 2nd.
posted by Pickman's Next Top Model at 3:00 PM on October 23, 2023 [35 favorites]
How is a socially awkward person like Microsoft Excel? There's always the chance they'll misinterpret something as a date.
posted by adekllny at 3:20 PM on October 23, 2023 [33 favorites]
posted by adekllny at 3:20 PM on October 23, 2023 [33 favorites]
I've seen that one as the Venn diagram overlap between incel and excel.
posted by joannemerriam at 3:44 PM on October 23, 2023 [10 favorites]
posted by joannemerriam at 3:44 PM on October 23, 2023 [10 favorites]
That sounds more like a Unix joke. Wouldn't the punchline of the Excel joke be "Jan 1, 1904"?
My looks like Jan 0, 1900.
(Office 365)
posted by MtDewd at 4:56 PM on October 23, 2023 [1 favorite]
My looks like Jan 0, 1900.
(Office 365)
posted by MtDewd at 4:56 PM on October 23, 2023 [1 favorite]
The truly horrific thing about the automatic conversion was that it's locale dependent. Not only would you get garbage, but coworkers in other places would get different garbage.
> Optimist: the glass is half full.
> Pessimist: the glass is half empty.
> Excel: the glass is January 2nd.
Excel on a British computer: the glass is 1st February.
posted by automatronic at 4:57 PM on October 23, 2023 [13 favorites]
> Optimist: the glass is half full.
> Pessimist: the glass is half empty.
> Excel: the glass is January 2nd.
Excel on a British computer: the glass is 1st February.
posted by automatronic at 4:57 PM on October 23, 2023 [13 favorites]
Running data with raw hand-built Python was stupid, they said. It was a waste of time they said.
posted by officer_fred at 5:08 PM on October 23, 2023 [7 favorites]
posted by officer_fred at 5:08 PM on October 23, 2023 [7 favorites]
Which versions of excel is this for?
I discovered that only the subscription based version (office 365?) has many of the newer features, including auto-save and any ai-powered tools.
posted by rebent at 5:15 PM on October 23, 2023 [3 favorites]
I discovered that only the subscription based version (office 365?) has many of the newer features, including auto-save and any ai-powered tools.
posted by rebent at 5:15 PM on October 23, 2023 [3 favorites]
only the subscription based version (office 365?) has many of the newer features...
That doesn't sound like Microsoft at all *eyeroll*
This looks less like a fix and more like yet another opportunity to corrupt data by opening a file on a device with different settings. That Office 365 auto-saves by default makes this even more fun.
posted by dg at 5:37 PM on October 23, 2023 [3 favorites]
That doesn't sound like Microsoft at all *eyeroll*
This looks less like a fix and more like yet another opportunity to corrupt data by opening a file on a device with different settings. That Office 365 auto-saves by default makes this even more fun.
posted by dg at 5:37 PM on October 23, 2023 [3 favorites]
Well, hell, what am I supposed to rant fulminously about in my metadata and research-data management courses NOW?!
nosql abominations by web devs.
posted by j_curiouser at 8:13 PM on October 23, 2023 [4 favorites]
nosql abominations by web devs.
posted by j_curiouser at 8:13 PM on October 23, 2023 [4 favorites]
If the Great Paperclip in the Sky will permit me to edit my own jokes:
Excel 1, Science 0
Excel Jan 1 1970, Science 0
posted by They sucked his brains out! at 8:16 PM on October 23, 2023 [3 favorites]
Excel 1, Science 0
Excel Jan 1 1970, Science 0
posted by They sucked his brains out! at 8:16 PM on October 23, 2023 [3 favorites]
>> That sounds more like a Unix joke. Wouldn't the punchline of the Excel joke be "Jan 1, 1904"?
> My looks like Jan 0, 1900.
Excel dates can be relative to one of two different epochs.
posted by trotz dem alten drachen at 2:47 AM on October 24, 2023 [2 favorites]
> My looks like Jan 0, 1900.
Excel dates can be relative to one of two different epochs.
posted by trotz dem alten drachen at 2:47 AM on October 24, 2023 [2 favorites]
Maybe don't use Excel for things it's not intended for.
posted by GallonOfAlan at 2:50 AM on October 24, 2023
posted by GallonOfAlan at 2:50 AM on October 24, 2023
Fully half of the comments are people saying "ugh, like it's so hard to just type ' in front of your data if it bothers you so much!". I can't even.
posted by Karmakaze at 5:17 AM on October 24, 2023 [2 favorites]
posted by Karmakaze at 5:17 AM on October 24, 2023 [2 favorites]
If you're like me, and I know you are, you'll want to know about the method titled "PowerShell script to open CSVs directly from Windows Explorer" from Superuser Stack Overflow user nixda's answer to the question, "How can I set Excel to always import all columns of CSV files as Text?" Once set up, you will have the option to "Send To > Excel as Text" in the Explorer context menu.
posted by ob1quixote at 5:44 AM on October 24, 2023 [5 favorites]
posted by ob1quixote at 5:44 AM on October 24, 2023 [5 favorites]
I spent 7 years working as a software engineer at an outfit that had Excel as a significant part of their workflow. We would daydream about not having to do backflips to make Excel not F up our metadata. We called it "verbatim mode."
Very glad to see this emerge as an option. I've forwarded this to several of my old colleagues, hoping it eventually can strip out some of the scary hacks we implemented years ago.
posted by mcstayinskool at 5:57 AM on October 24, 2023 [2 favorites]
Very glad to see this emerge as an option. I've forwarded this to several of my old colleagues, hoping it eventually can strip out some of the scary hacks we implemented years ago.
posted by mcstayinskool at 5:57 AM on October 24, 2023 [2 favorites]
This is why our Dark Lord invented CSV, Python and Pandas.
Running data with raw hand-built Python was stupid, they said. It was a waste of time they said.
...you know, Pandas ALSO will take liberties with your mixed/non-numeric data if you're not careful. You can set column data types, but the default is that Pandas knows better than you do what your data are.
posted by mcstayinskool at 6:00 AM on October 24, 2023 [4 favorites]
Running data with raw hand-built Python was stupid, they said. It was a waste of time they said.
...you know, Pandas ALSO will take liberties with your mixed/non-numeric data if you're not careful. You can set column data types, but the default is that Pandas knows better than you do what your data are.
posted by mcstayinskool at 6:00 AM on October 24, 2023 [4 favorites]
Maybe don't use Excel for things it's not intended for.
posted by GallonOfAlan at 2:50 AM on October 24 [+] [!]
good luck showing any work product to your management, ever
posted by eustatic at 9:38 AM on October 24, 2023 [13 favorites]
posted by GallonOfAlan at 2:50 AM on October 24 [+] [!]
good luck showing any work product to your management, ever
posted by eustatic at 9:38 AM on October 24, 2023 [13 favorites]
If anyone is still reading this thread.... never import CSVs into Excel. Even if you're forced to use Excel. Just import using LibreOffice, then use 'Save As' to save it to an .xlsx, then go back to your regularly scheduled workflow. Libre Office has the good sense to always notice you're trying to import ambiguous data and shows a sensible dialog that lets *you* choose the delimiters (and the column data type), and even has a PREVIEW window.
posted by cfraenkel at 10:45 AM on October 24, 2023 [5 favorites]
posted by cfraenkel at 10:45 AM on October 24, 2023 [5 favorites]
Without wanting to turn this into a Excel support thread, you can specify the separator in a CSV file by adding a line at the beginning of the file that starts with "sep" - without the quotes - and then followed by the character you want as the separator. That's what I have to do at my work - where installing any extra software or utilities, let alone Libra Office, is not an option.
posted by vac2003 at 1:48 PM on October 24, 2023 [2 favorites]
posted by vac2003 at 1:48 PM on October 24, 2023 [2 favorites]
> Libra Office
which comes between Virgo Office and Scorpio Office
posted by fantabulous timewaster at 3:13 PM on October 24, 2023 [6 favorites]
which comes between Virgo Office and Scorpio Office
posted by fantabulous timewaster at 3:13 PM on October 24, 2023 [6 favorites]
GallonOfAlan: "Maybe don't use Excel for things it's not intended for."
I thought Excel as intended for working with tables of data.
posted by signal at 6:17 PM on October 24, 2023 [2 favorites]
I thought Excel as intended for working with tables of data.
posted by signal at 6:17 PM on October 24, 2023 [2 favorites]
I thought Excel as intended for working with tables of data.
I thought it was for summoning capitalist demons.
posted by clawsoon at 7:28 PM on October 24, 2023 [2 favorites]
I thought it was for summoning capitalist demons.
posted by clawsoon at 7:28 PM on October 24, 2023 [2 favorites]
you know, Pandas ALSO will take liberties with your mixed/non-numeric data if you're not careful
Oh so very true. Probably better in Pandas 2.0 because of the under-the-covers implementation changes...but this is what drove me to Pola.rs which is far more sensible and has a better API to boot.
posted by mmascolino at 7:35 PM on October 24, 2023
Oh so very true. Probably better in Pandas 2.0 because of the under-the-covers implementation changes...but this is what drove me to Pola.rs which is far more sensible and has a better API to boot.
posted by mmascolino at 7:35 PM on October 24, 2023
I thought it was for summoning capitalist demons.
I thought that was how we got Excel in the first place.
posted by dg at 8:17 PM on October 24, 2023 [2 favorites]
I thought that was how we got Excel in the first place.
posted by dg at 8:17 PM on October 24, 2023 [2 favorites]
Great, so now that this is done, can Excel be made to acknowledge ISO8601/W3CDTF (I'd be happy either way) as a valid date format?
posted by humbug at 6:55 AM on October 25, 2023 [1 favorite]
posted by humbug at 6:55 AM on October 25, 2023 [1 favorite]
...you know, Pandas ALSO will take liberties with your mixed/non-numeric data if you're not careful. You can set column data types, but the default is that Pandas knows better than you do what your data are.
True and great point, but Python and, more importantly, Numpy will almost always complain if you try to work with data from Pandas dataframes in a type that is another than what it thinks it is. So you're more likely to catch that kind of problem with Pandas, whereas with Excel you'd get no warning, and — even worse, as biologists have found — unfixable data errors would propagate downstream to whomever was consuming your Excel work.
posted by They sucked his brains out! at 12:28 AM on October 27, 2023 [1 favorite]
True and great point, but Python and, more importantly, Numpy will almost always complain if you try to work with data from Pandas dataframes in a type that is another than what it thinks it is. So you're more likely to catch that kind of problem with Pandas, whereas with Excel you'd get no warning, and — even worse, as biologists have found — unfixable data errors would propagate downstream to whomever was consuming your Excel work.
posted by They sucked his brains out! at 12:28 AM on October 27, 2023 [1 favorite]
can Excel be made to acknowledge ISO8601
Control Panel > Region > Change date, time or number formats
There, choose for Short date the yyyy-MM-dd format, and done! [x]
Not every application will use the short date format, but Excel does.
posted by Lanark at 2:14 AM on October 27, 2023
Control Panel > Region > Change date, time or number formats
There, choose for Short date the yyyy-MM-dd format, and done! [x]
Not every application will use the short date format, but Excel does.
posted by Lanark at 2:14 AM on October 27, 2023
As the link notes, "Keep in mind that this will also change the way Windows displays the date.". That's an operating system setting, not an Excel setting. Presumably it doesn't work if you send the Excel file to someone who hasn't reconfigured their operating system. Does Excel really not have the ability to choose ISO date format as the display for dates? That's crazy to me! Google Sheets has no problem with it.
With explicit functions Excel can turn ISO text into dates (serial numbers since the epoch) and dates back into text. See TEXT and DATEVALUE. But I don't know what the automagic type conversion stuff does when given an input like 2023-10-27. I like to imagine it creates the integer 1986.
posted by Nelson at 7:20 AM on October 27, 2023
With explicit functions Excel can turn ISO text into dates (serial numbers since the epoch) and dates back into text. See TEXT and DATEVALUE. But I don't know what the automagic type conversion stuff does when given an input like 2023-10-27. I like to imagine it creates the integer 1986.
posted by Nelson at 7:20 AM on October 27, 2023
Automangle, surely?
posted by polytope subirb enby-of-piano-dice at 7:54 AM on October 27, 2023
posted by polytope subirb enby-of-piano-dice at 7:54 AM on October 27, 2023
« Older This was immensely frustrating and dehumanizing | Hats are cakes, and handbags are toasters Newer »
This thread has been archived and is closed to new comments
posted by njohnson23 at 1:51 PM on October 23, 2023 [6 favorites]