this post was submitted on 24 Oct 2023
212 points (95.3% liked)
Technology
59143 readers
2313 users here now
This is a most excellent place for technology news and articles.
Our Rules
- Follow the lemmy.world rules.
- Only tech related content.
- Be excellent to each another!
- Mod approved content bots can post up to 10 articles per day.
- Threads asking for personal tech support may be deleted.
- Politics threads may be removed.
- No memes allowed as posts, OK to post as comments.
- Only approved bots from the list below, to ask if your bot can be added please contact us.
- Check for duplicates before posting, duplicates may be removed
Approved Bots
founded 1 year ago
MODERATORS
I have a spreadsheet that has been going for 20 years, across 2 companies, and hundreds of people.
Thousands and thousands of lines, going back like an ice core sample. Zooming out is wild.
"What's this layer?"
"Oh that's when Terry worked here"
Nowhere here is it implied that they were using it instead of a database. They could run into this issue whether or not they were properly using Excel for what it is for.
It's a well known phenomenon. The title of the article even alludes to it.
What about the title?
Read the article. IMHO scientists were having problems because they were using it for a task it is ill suited for. The kind of program that is designed for storing large amounts of data and preventing accidental corruption of it is a database.
I think scientists would use Excel more for finding out information with their data, not for storing it. You know like looking for trends and plotting graphs and whatnot.
I think they use it for both. They end up using it as a database because they're familiar with it from doing data analysis. I've also heard of much more egregious examples, like a school using a giant Excel spreadsheet to track the applications of potential students. Need to track up to three references for each student? Make three of each column related to a reference! It's a really gross way of storing denormalized data without any of the ACID properties databases guarantee.
Microsoft fixed something and made their products better!? I don't believe you.
I wonder if this will fix any of my issues with excel molesting my data. It will either round my part numbers from a BoM making it useless or removing preceding zeros which breaks our inventory importing.
yes, those two "autofixes" are "fixed" now. (it's a opt-in setting)
I thought setting a column to string only was already possible.
You could already, after the fact, and per cell.
If you're importing 100,000 rows in, the damage to your data is already done by the time you're at the step where you can set the field to not convert.
It is, I've used that to prevent automatic removal of leading zeroes when reading the values of bytes.
Based on the article it seems like it's just a matter of not having to spend the time (and mental overhead) of doing that for all required columns and never slipping up on it (now just set and forget).
So did i. Format cell(s) > data type (or something) then just seleect text, or custom or whatever.
What does excel and incels have in common? Confusing things for a date.
My dad uses excel too.
Omfg! The amount of time I screamed at Microsoft because I keep forgetting that excel changes the date format for some fucking reason and doesn’t tell you!
This is the best summary I could come up with:
Microsoft recently published a blog highlighting new Excel updates that allow users to disable Automatic Data Conversion.
This comes as good news for the scientists, because in recent years they had to rename quite a few human gene names—since Excel was converting them to dates.
There was no option to disable this automatic conversion, and that ended up affecting hundreds of scientific papers.
It led to scientists taking hours out to manually fix the errors and restore the data.
As the name suggests, this checkbox displays a warning message when you’re opening a .csv or .txt file with any of the optional automatic data conversions used.
Just keep in mind that one of the ‘known issues’ with the update that Microsoft flagged in its blog is that the new conversion option doesn’t work when you’re running macros.
The original article contains 299 words, the summary contains 137 words. Saved 54%. I'm a bot and I'm open source!
I worked in a company where excel files were used as a database and then read through JavaScript to be processed...
At least it's not a giant macro
Took long enough. Now let's see if they can give an option to turn off Lotus 1-2-3 compatibility stuff like single quotes starting a cell being hidden as markup.
I had this problem all the time in a former job.