Excel is fundamentally broken.
-
even better! Now you can embed this in excel itself:
Excel JavaScript object model in Office Add-ins - Office Add-ins
Learn the key object types in the Excel JavaScript APIs and how to use them to build add-ins for Excel.
Make an app out of it, sell it, become rich and famous (or something)
-
@anotherusername said in Excel is fundamentally broken.:
When saving as a CSV, yes. But when saving as XLSX, no... that cell style is preserved. So once my script has converted it, there should be no more issues with Excel changing stuff. You get the text with no modification.
oh, so this is a csv-neutering device that takes Scary Internet CSV and makes it safe. ...why doesn't Excel do this by default on import of csv? Who writes Excel formulas in csv?
-
@aygeeplus said in Excel is fundamentally broken.:
oh, so this is a csv-neutering device that takes Scary Internet CSV and makes it safe. ...why doesn't Excel do this by default on import of csv? Who writes Excel formulas in csv?
People with nefarious intentions, perhaps? Thwarting those seems to be the chief idea behind @anotherusername’s script.
-
@gurth Exactly. Excel should be doing this right now. If it breaks your workflow your workflow is stupid.
-
@aygeeplus @gurth actually the main point of the script was fixing Excel's stupidity described in the OP:
@gordonjcp said in Excel is fundamentally broken.:
One column contains a value consisting of the shelf and port number of a connection, which looks like "3-2" or "5-44". There appears to be no way to tell Excel to treat that as a text stringm, and not try to format it as a date.
But that's okay! We have paid a shitload of money for Microsoft support! What do they have to say? Paraphrasing slightly "lol it looks like a date zomg don't make it look like a date".
If it also fixes the security flaw (combination, really) where Excel interprets formulas in CSV files and Excel formulas can run arbitrary code (if it's already located somewhere on the hard disk), that's just a bonus.
-
@anotherusername I got to be "that guy" but does this code have a license? I might use it for a project I have.
-
@blakeyrat The license is "if I'm ever in town, I'd appreciate a beer", and I make no promises with regards to support if it's not working.
-
@anotherusername Cool thanks
-
@anotherusername said in Excel is fundamentally broken.:
if I'm ever in town, I'd appreciate a beer
a.k.a. The Beerware license
-
@anotherusername said in Excel is fundamentally broken.:
@blakeyrat The license is "if I'm ever in town, I'd appreciate a beer", and I make no promises with regards to support if it's not working.
TIL that beerware is apparently an established thing.
EDIT:
by TimeBandit.
-
@timebandit said in Excel is fundamentally broken.:
@anotherusername said in Excel is fundamentally broken.:
if I'm ever in town, I'd appreciate a beer
a.k.a. The Beerware license
Hmm. I might have to update the license for my open source project... (MIT right now)
-
- Learn C#
- Use the Office .NET APIs (yes I know that's not the name) to create the XLSX file
- Decide you can write a better spreadsheet than Excel
- Write a better spreadsheet
- Profit!
-
@slapout1 The problem there is step 4 takes at least $5,000,000 to execute on. At least.
-
@blakeyrat Not only do I agree, I think that's probably at least an order of magnitude too low. Yes, Excel has problems, but it has such massive adoption because it can do an incredible range of things. (As I know you know; I'm replying to you, but in fervent agreement, not in disputation.) There's a reason "do it in Excel!" is a meme around here.
(ETA: And then you have the problem of trying to get market share - without being free, if you want to achieve the Profit! step.)
-
@scarlet_manuka You'd slim down the audience for your Excel replacement. You wouldn't (and shouldn't) actually try to REPLACE Excel because that's impossible. But yeah, it'd be expensive either way.
-
google did a decent job, however its online only and probably with less functionality than excel 1.0. The collaboration is actually better than office365, who decides to shit itself regularly as soon as 2 people are editing an excel sheet.
Other fun fact, excel sheets can have concurrent write by multiple users activated if they are saved on the file system but not in Sharepoint or one Drive (https://support.office.com/en-us/article/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534) where you need to co-author...
-
@anotherusername said in Excel is fundamentally broken.:
If it also fixes the security flaw (combination, really) where Excel interprets formulas in CSV files and Excel formulas can run arbitrary code (if it's already located somewhere on the hard disk), that's just a bonus.
Well, you have access to CMD, and with CMD, you can write arbitrary bytes to file, and then you can use CMD to run this file if it forms valid EXE. So the "somewhere on the hard disk" might be the CSV file you're opening.
-
@blakeyrat said in Excel is fundamentally broken.:
The problem there is step 4 takes at least $5,000,000 to execute on. At least.
If you want a reasonable prototype for $5M, you'll have to outsource the work to countries like Bulgaria or Morocco. (Or India, if you don't care about quality at all.) For the US or one of the richer EU countries, you're definitely an order of magnitude off.
-
@dfdub you can hire top Polish developers for $60k/year, all taxes included. $40k for a good senior. $5 million can buy you 100 man-years of high quality engineering force. Enough to get things started.
-
@gąska said in Excel is fundamentally broken.:
@anotherusername said in Excel is fundamentally broken.:
If it also fixes the security flaw (combination, really) where Excel interprets formulas in CSV files and Excel formulas can run arbitrary code (if it's already located somewhere on the hard disk), that's just a bonus.
Well, you have access to CMD, and with CMD, you can write arbitrary bytes to file, and then you can use CMD to run this file if it forms valid EXE. So the "somewhere on the hard disk" might be the CSV file you're opening.
/me boggles
-
@blakeyrat If I had to I'd write Science Excel, with a pandas backend or something.
It'll never ever reformat datey things into dates unless you ask it to, and it already does pretty much all the science stuff.
-
@aygeeplus said in Excel is fundamentally broken.:
It'll never ever reformat datey things into dates unless you ask it to
Spreadsheets guessing what a value represents when it’s typed in is a very useful thing, because it saves a lot of effort. Problems only happen whey it gets it wrong, like if you do actually want a cell say “01-06” instead of having that parsed into “1 June 2018”, but I suspect that’s going to be a minority of all cases. What Excel (and probably other spreadsheets) needs, though, as this thread shows, is a way to tell it to not parse things from a file, and keep that intact on saves.
-
@gurth the problem is that when Excel detects a date, it changes the cell value to some weird fraction so that when you change the cell format to text or number, the original text is lost anyway.
-
@gurth said in Excel is fundamentally broken.:
Problems only happen whey it gets it wrong, like if you do actually want a cell say “01-06” instead of having that parsed into “1 June 2018”, but I suspect that’s going to be a minority of all cases.
formatting 01-06 into any kind of date is out and out wrong. It's either the 1st of june or the 6th of january or a range 1-6, and Excel can't know that. Even dumber, Excel writes its guess to file, over your real data.
Excel is great for toy spreadsheets where you type in all the data yourself. Excel is not a data analysis tool.
I've got a bee in my bonnet about this because Excel(used by another person) corrupted about 20 thousand dollars in data at my old job.
-
@gurth said in Excel is fundamentally broken.:
What Excel (and probably other spreadsheets) needs, though, as this thread shows, is a way to tell it to not parse things from a file, and keep that intact on saves.
It does. As mentioned above, use the Text Import Wizard. (The suggested way was a little clunky, just rename the .csv to .txt. The wizard kicks off automatically for .txt.) If you need to import a certain file repeatedly, you can record the Text Import Wizard to a macro so you don't have to futz around shift-clicking the columns and setting them to Text each time.
I have a big data migration process that spits out about 30 CSVs for reconciliation. Importing them all into a workbook with multiple tabs, no manipulation of the data, nicely formatted as a table takes about ten seconds. Sure, the migration process could spit out an XLSX instead. But it's a relatively simple PowerShell script and I'd like to keep it that way. (Though @anotherusername's script tempts me, as horrifying as it is.)
-
@aygeeplus said in Excel is fundamentally broken.:
formatting 01-06 into any kind of date is out and out wrong. It's either the 1st of june or the 6th of january or a range 1-6, and Excel can't know that.
Return → 
→ doubleclick on A1 → 
-
@gurth said in Excel is fundamentally broken.:
@aygeeplus said in Excel is fundamentally broken.:
formatting 01-06 into any kind of date is out and out wrong. It's either the 1st of june or the 6th of january or a range 1-6, and Excel can't know that.
Return → → doubleclick on A1 → Now tell it to format as number or text...
-
@tsaukpaetra said in Excel is fundamentally broken.:
Now tell it to format as number or text...
43252. Oh dear…
-
@gurth said in Excel is fundamentally broken.:
@aygeeplus said in Excel is fundamentally broken.:
formatting 01-06 into any kind of date is out and out wrong. It's either the 1st of june or the 6th of january or a range 1-6, and Excel can't know that.
Return → → doubleclick on A1 → what are you trying to show me here? I know Excel does this.
-
@aygeeplus You said:
formatting 01-06 into any kind of date is out and out wrong
and I’m showing that’s exactly what it does.
-
@gurth and it's wrong that it does it
-
@gurth What am I missing here?
Are you trying to disagree with me or provide evidence?
-
@aygeeplus Seems to me you're both agreeing violently.
-
@aygeeplus said in Excel is fundamentally broken.:
@gurth What am I missing here?
You’re probably making the common assumption that anyone on a forum who replies to you and isn’t obviously agreeing with what you said, is disagreeing :)
Are you trying to disagree with me or provide evidence?
Neither, really. Just showing that Excel does exactly what you said it shouldn’t do, perhaps because you didn’t make clear from your post that you know that it does, and/or for others who didn’t know. (Apple Numbers does the same thing, by the way, but hides it even better because the cell keeps showing
01-06even though it now contains a date.)
-
@gurth You would make a terrible hype man.
For the record, I meant 'shouldn't do that' like 'that is the wrong behavior' not like 'that is a bug'
-
@hoviwan said in Excel is fundamentally broken.:
- Create a false .csv file with the same number of columns. put text data in the first row, and save.
- With a blank worksheet in Excel, us the Data-Get & Transform date-From Text/CSV function to import the false .csv file. It will import as text.
- Save as a worksheet & close.
- Save the real .csv file over the false .csv file
- Re-open the worksheet, click on a data field, select Refresh in the Table Tools Ribbon.
- Automate, as desired.
I don't care if this works, this is terrible. Upvoting.
OP, if you're still having this problem, it's about time to consider parsing the csv with a sane parser, like maybe ostermiller , and then output it with Apache POI to excel, being sure to type the cell as strongly to string as possible (use the old terrible API which has to care because the format is terrible).
Then excel will do the exact same thing on display as it's doing currently, apparently, since somehow your excel (installation?) even translates string formula results and also the import wizard behaves differently than I recall.
-
@gribnit did you see the thing I posted?
https://what.thedailywtf.com/post/1354799
It both works, and it is terrible.
-
@anotherusername I have now seen it. Yes, that, modulo language and libraries. Glad it worked. Yes, you are probably less sane than before.
-
@AyGeePlus said in Excel is fundamentally broken.:
@Gurth said in Excel is fundamentally broken.:
Problems only happen whey it gets it wrong, like if you do actually want a cell say “01-06” instead of having that parsed into “1 June 2018”, but I suspect that’s going to be a minority of all cases.
formatting 01-06 into any kind of date is out and out wrong. It's either the 1st of june or the 6th of january or a range 1-6, and Excel can't know that. Even dumber, Excel writes its guess to file, over your real data.
Excel is great for toy spreadsheets where you type in all the data yourself. Excel is not a data analysis tool.
I've got a bee in my bonnet about this because Excel(used by another person) corrupted about 20 thousand dollars in data at my old job.
IIRC, Excel uses your computer's system localization settings, because why would you ever have data in any different format? It's not like any companies have international offices that send each other data or anything.
-
Had to mess with this recently, and this got necro'd so...
Sample of a column definition for tables in our webapp. Yes, backend is PHP, shut up:
'ringTime' => [ 'name' => $this->tr('table_header.ring_time'), // Header text, localized 'column' => 'ringTime', // Column in the view to apply sorting to as needed 'icon' => 'fa fa-bell', // Icon to render in the header, optional 'display_column' => 'ringTime', // Actual column in the view to display, optional, actually redundant in this particular case 'formatAs' => 'string' // GODDAMNIT EXCEL, IT'S 00:00:30, NOT 30! (Yes, this is just for exports and nothing else) ]I don't even want to comment on the madness I'm doing to make it actually parse the fucking dates when I want it to...
// Convert to Excel's special snowflake format $dateTimeFormat = $this->dateTimeFormatter->getPattern(); $dateTimeFormat = preg_replace('/([^y])y([^y])/', '$1yyyy$2', $dateTimeFormat); $dateTimeFormat = preg_replace('/(\W)a(\W|$)/', '$1AM/PM$2', $dateTimeFormat); $this->dateTimeFormatter->setPattern($dateTimeFormat); $timeFormat = $this->timeFormatter->getPattern(); $timeFormat = preg_replace('/(\W)a(\W|$)/', '$1AM/PM$2', $timeFormat); $this->timeFormatter->setPattern($timeFormat);Kill me now...
-
@onyx said in Excel is fundamentally broken.:
Kill me now...
but ... then you'll miss the historic final!
-
@luhmann said in Excel is fundamentally broken.:
@onyx said in Excel is fundamentally broken.:
Kill me now...
but ... then you'll miss the historic final!
No worries, I'd haunt them!
-
@gąska said in Excel is fundamentally broken.:
@dfdub you can hire top Polish developers for $60k/year, all taxes included. $40k for a good senior. $5 million can buy you 100 man-years of high quality engineering force. Enough to get things started.
I still doubt you'd get a Better Excel, without the bugs out of such a project. A good start is possible though, but history is littered with "decent starters for excel replacements" that didn't.
You'd also need an army of proper testers, designers and whatnot, apart from just programmers. And if you have too many programmers, it'll take longer and be worse.
-
@carnage I didn't say it would certainly result in better Excel. I just said 5 millions is enough budget for a prototype.
-
@djls45 said in Excel is fundamentally broken.:
IIRC, Excel uses your computer's system localization settings, because why would you ever have data in any different format? It's not like any companies have international offices that send each other data or anything.
As long as it's an Excel file, it handles the conversion automatically -- the saved file is consistent; only the displayed formats change.
Beerware - Wikipedia
