News for hackers. Stuff that matters.
Re:Microsoft Excel’s 35th birthday by Someone1234 on Friday October 02, 2020 @10:18AM (#24662847)
Excel remains one of my most used pieces of software, and one where I feel like any education/training has re-paid for itself multiple times over. The more you learn, the more productive you are (and sometimes more informed, thanks to data visualizations).

OneNote (desktop) and Excel are the only two pieces of Microsoft Office that I regularly continue to use. They're the only reason I license it.

Microsoft Word after I graduated rarely gets used (typesetting? I don't even print!), and while I understand the value of Microsoft Access, I feel like using it is investing effort into a dead-end (i.e. people want online/real-time reports, not locked into a desktop software). PowerPoint, at least where I work, is almost a meme (as in "Power Point meeting" is a pejorative implies waste of time meeting).

As to Excel: Hopefully it won't take another 35 years for them to fix CSV opening so it doesn't auto-format long numbers into scientific notion or dates, then cause unrecoverable data loss when you save. If you don't know what the data format is DON'T GUESS!

Re:Microsoft Excel’s 35th birthday by amichal on Friday October 02, 2020 @10:47AM (#24663162)
i recently got burned because at some point in the last few years Excel decided it would be better if it attempted to use localized thousands and decimal separators.

    item,cost,currency
    "Socks",1.234,USD
Represents socks costing about a dollar in countries that use '.' as a decimal (most) and about a thousand dollars in countries that use a ','. In locales using a decimal period '.' double clicking will open the CSV just fine. In locales using a ',' it comes in as text but the default settings for text to columns use localized number parsing but still let you choose a now ambiguous comma as a column separator. I'm 99% sure it didn't used to do this as we've been making CSVs for the same clients in the same regions (Netherlands) for years and only more recently have they had this trouble.

Edit: I've used Excel for at least 20yrs. I thought i new how to use it and how to search it's docs. This is the only article I've found online that mentions how to handle this as a end user. Near the bottom under "Converting International Number Formats" where it of course assumes you have decimal comma and need a decimal period.

https://www.howtogeek.com/407217/how-to-use-text-to-columns-...

I did also find a 20 minute youtube eventually

Re:Microsoft Excel’s 35th birthday by ndespres on Friday October 02, 2020 @11:14AM (#24663478)
I have long suspected that Excel's treatment of CSV files was intentionally antagonistic. For example, opening a CSV file will start an importer function where you must specify how fields are delimited within the file, and "comma" is not automatically selected, ever, despite "comma-separated" being literally named in the file extension.

Surely the folks programming it are not that short-sighted. It's just a punishment for not using their default format.

Re:Microsoft Excel’s 35th birthday by kalleboo on Friday October 02, 2020 @08:33PM (#24668442)
It also has really broken handling of text encodings.

I've taken to importing CSV files using Apple Numbers, and saving as Excel

(i use Numbers primarily, Excel is just when I need to be interoperable with someone else)

Re:Microsoft Excel’s 35th birthday by rightbyte on Friday October 02, 2020 @12:58PM (#24664581)
Well I would give Excel some cred for acctually following local number conventions. It the beginning there was no internet and interoperability issue.

Usually it is the us-en way or the highway.

Re:Microsoft Excel’s 35th birthday by zokier on Friday October 02, 2020 @11:26AM (#24663586)
Modern excel is better at this because it has this "powerquery" thing integrated to csv import which allows bit more control how data is imported. The classic csv import is really bad though. It is bit hidden feature, you have to click "transform data" button when importing the file

Here is sort of some docs on it https://support.microsoft.com/en-gb/office/import-or-export-... (section "Import a text file by connecting to it (Power Query)")

Re:Microsoft Excel’s 35th birthday by WorldMaker on Friday October 02, 2020 @01:58PM (#24665178)
The classic "Text File Import Wizard", going back decades, is even easier than "PowerQuery" for the common and basic "set the type for each column so that Text is Text and not General" but it's now hidden and you have to search for it. (Admittedly, a lot of people had to search for it even when it was on the Ribbon.)
Re:Microsoft Excel’s 35th birthday by amichal on Friday October 02, 2020 @11:51AM (#24663868)
I've always wanted to investigate if there is a current way to do this on a URL without resorting to scripting but have never really gotten the energy up to do it. Being able to send users to https://www.example.com/foo/your_data.csv and have that send the right headers for caching, refresh and authentication so they can use Excel as a mini BI front end without screwing around with ODBC or similar etc would be a nice way to answer the "integration" question for clients that are too small to have a IT department to help them with database connections, firewalls etc.
Re:Microsoft Excel’s 35th birthday by cosmie on Friday October 02, 2020 @02:24PM (#24665473)
Excel has an entire ETL engine called PowerQuery tucked up under the hood. Originally part of SQL Server Analysis Services, it got strapped into Excel about a decade ago, and more recently tons of other products[1].

The Web[2] connector is what you're after. It can consume a variety of formats, including csv, xml, and json. It supports HTTP Basic Auth pretty well, but if you want to put the data source behind oauth it can get tricky (it technically supports it but depends on how novel the implementation is).

Credentials sit with the (Excel) client, so if the file gets shared with a new user it'll prompt them for authentication details when they first attempt to refresh it. It'd be pretty easy to set up a template/turnkey workbook you can just hand over to new clients with everything all set up and all they have to do is enter their credentials when prompted. Only caveat is Excel for Mac - it only gained Power Query about a year ago, and porting it over is a very involved[3] process including an entire .NET Core rewrite and factoring out Windows dependencies from legacy cruft. The Web connector is one of the many that haven't made it over to the Mac yet, so any clients using a Mac would still need an alternative.

[1] https://docs.microsoft.com/en-us/power-query/power-query-wha...

[2] https://docs.microsoft.com/en-us/power-query/connectors/web

[3] https://devblogs.microsoft.com/dotnet/using-net-core-to-prov...

Re:Microsoft Excel’s 35th birthday by amichal on Friday October 02, 2020 @03:03PM (#24665929)
Thank you for this!. It's why i continue to read HN.
Re:Microsoft Excel’s 35th birthday by cosmie on Friday October 02, 2020 @04:18PM (#24666623)
My pleasure! Feel free to reach out, you have any questions (email in profile).

I'm not an expert on it, but I am a technical person currently working on the business side of the IT fence. And I frequently do (data-engineering heavy) consulting for clients who are similarly on the business side of IT. So none of the nifty ETL tools, data storage capabilities, or computing environments I have available when I'm on the IT side.

Power Query is a godsend in that scenario. It's a well featured ETL engine with integrations for a variety of systems, services, and databases (including generic JDBC/ODBC support). And has the ability to use direct HTTP calls when that's more appropriate/useful.

It also stores the data internally in a highly-compressed and optimized columnar store, independent of the "Excel data" on sheets. Which you can then either sync to a worksheet, or leave it in a state where the raw data isn't visible but can be accessed through a pivot table connected to it. So you can abuse it for far heavier work than you'd expect to be able to do in Excel.

And it's already there, sitting on virtually every business person's computer everywhere. Completely sidesteps the security, IT, procurement, and legal hassles you have to jump through to get a proper system. Not to mention user training - you can architect things in a way where all users have to do is maybe tweak a cell or two, then hit the Refresh button that's in the Ribbon. So the complexity of "learning something new" is completely absorbed on your side, and you're free of pesky support questions and hassles since you isolated them from being the new stuff (also making it harder for "accidental/I didn't press anything!" changes from users).

It's not perfect by any means and has a number of warts, usually falling short of more purpose-built solutions when those are options. But it's still pretty solid, and on balance has saved me far more frustration than it's caused. And without having to dip into the dreaded world of macros and VBA.

Re:Microsoft Excel’s 35th birthday by texasbigdata on Friday October 02, 2020 @11:57AM (#24663924)
I believe this exists. Under data, “get data”, then the url/http feature. You can also manually recursively limit a list of urls but by that point you’re privacy going beyond what the tool should be used for.

https://youtu.be/cLWCUgCYl5Y

Re:Microsoft Excel’s 35th birthday by dfox on Friday October 02, 2020 @10:57AM (#24663294)
Excel's notion of what is "CSV" and syntax of its formula language is locale dependent for as long as I care about that. Notably, for locales that use ',' as decimal point excel uses semicolons as separators in both its "C"SV format and formulas.

What changed in last few version is that excel started to distinguish whether you are "opening" an CSV file (eg. by double clicking it) or "importing" it and the former tends to lead to various kinds of random breakage in presence of the aforementioned (mis-)feature.

Re:Microsoft Excel’s 35th birthday by markus92 on Friday October 02, 2020 @10:46AM (#24663143)
Also stop using locale settings for importing CSVs, or at least make a setting for it. I had to change my PC's locale to English (US) just to decently import CSV files in Excel
Re:Microsoft Excel’s 35th birthday by xnx on Friday October 02, 2020 @10:34AM (#24663021)
> As to Excel: Hopefully it won't take another 35 years for them to fix CSV opening so it doesn't auto-format long numbers into scientific notion or dates, then cause unrecoverable data loss when you save. If you don't know what the data format is DON'T GUESS!

Amen! Also removing the leading zeros from US ZIP codes.

Re:Microsoft Excel’s 35th birthday by TeMPOraL on Friday October 02, 2020 @10:39AM (#24663062)
Would also say corrupting gene names, but in this case the scientists literally decided to rename genes to not conflict with Excel's treatment of date-like strings.
Re:Microsoft Excel’s 35th birthday by hateful on Friday October 02, 2020 @10:42AM (#24663093)
My first job in 2000 was converting files from the "PC" so they can be used on the Mainframe - and this was one of our biggest issues. Some things never change. Used to rename csv files to anything but csv to import them so you'd get the wizard.

Hey, but luckily they removed the import option recently! /sarcasm

Re:Microsoft Excel’s 35th birthday by n4r9 on Friday October 02, 2020 @01:20PM (#24664803)
> Hopefully it won't take another 35 years for them to fix CSV opening so it doesn't auto-format long numbers into scientific notion or dates, then cause unrecoverable data loss when you save.

This has been a major issue when working with Australian address data, where the street number was quite often a range e.g. "1-12". Excel would auto-convert to "12-Jan" upon opening the file, and save it as that if you clicked save, and there was no workaround I could find. I eventually told the tester to use Libre Office Calc instead.

Re:Microsoft Excel’s 35th birthday by nojito on Friday October 02, 2020 @02:37PM (#24665623)
>Hopefully it won't take another 35 years for them to fix CSV opening so it doesn't auto-format long numbers into scientific notion or dates

Connect to the CSV using Powerquery in Excel. Solves 100% of the problems of CSVs.

Re:Microsoft Excel’s 35th birthday by kyriakos on Friday October 02, 2020 @02:22PM (#24665452)
Add Outlook to the list and it sums up all the useful Office applications in 2020.
Re:Microsoft Excel’s 35th birthday by ricohboy on Friday October 02, 2020 @02:53PM (#24665820)
Second the disagree. As someone who supports users on Outlook on a daily basis, my anecdata is that it’s a buggy, abused, flaming dumpster fire

Perhaps it’s users who misuse it (think document management with 100gb+ inboxes) but a lot of times the solution for Outlook issues is to reinstall the whole Office suite since it’s just kinda one package now

Re:Microsoft Excel’s 35th birthday by Someone1234 on Friday October 02, 2020 @02:31PM (#24665547)
Respectfully disagree. Desktop Outlook is largely abandon-ware at this point, with no real development or improvements taking place for over ten years and Microsoft's efforts being almost exclusively focused on their online/webmail offers (e.g. Outlook.com/Office 365).
Re:Microsoft Excel’s 35th birthday by ludditeparttime on Friday October 02, 2020 @02:51PM (#24665782)
> no real development or improvements taking place for over ten years

You write this like it is a bad? Outlook does what it needs to do, send and receive email. I personally am glad that the changes which affect me have been minor. Email is a solved problem, no need for change for change sake.

Have you used Outlook365 on Desktop recently?

Re:Microsoft Excel’s 35th birthday by ricohboy on Friday October 02, 2020 @02:54PM (#24665836)
They change and break Outlook regularly, and the only communication channel is a random Microsoft employee on a support forum

I wish they would leave it alone but they are changing things for change’s sake

Re:Microsoft Excel’s 35th birthday by jankiehodgpodge on Friday October 02, 2020 @04:34PM (#24666756)
Not that I use it but Outlook in Office 365 is radically different. Has full integration with M365 groups etc.
Re:Microsoft Excel’s 35th birthday by agumonkey on Friday October 02, 2020 @02:19PM (#24665425)
I used to say, MS will be remembered for NT5 and Excel