Earlier quoted context omitted.
Excel has exactly the same functionality.
Good, since when? Last used it 10ish years ago.
at least 20 years, probably more.
Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
121–130 of 143 posts
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#122PSA: if you use Excel as intended, you would just go File/New/Blank Worksheet, switch to the Data tab, then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue. But yes, just double-clicking the CSV from Explorer, using the legacy 'open this as a sheet' functionality, experiencing data loss and then complaining about it (and the state of Exc…
In Japan everyone passes around CSVs in shift-jis still for exactly this reason - the default opening action of Excel with CSVs. Its easy to say its people being dumb, but at this point I really wish excel just wasn't so confident in itself and actually asked you during the default open operation what you want to do.
Actually UTF-8 WITH BOM csv file also can be read/written like that since Excel 2016, but of course average users aren't knowledgeable as like programmers.
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#123Similarly, if your last name is "Null", sometimes it's just easier to just change your last name.
Don't change it to "None", though. That's just a curse for your grandchildren to deal with when Python eventually becomes the new old legacy system. "Nil" is probably safe, because the lisp curse will actually work in your favor.
I wonder would Norway change their country code to avoid confusing "No"
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#124Earlier quoted context omitted.
You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible
100k+ is pretty manageable in the Excel GUI. When you get above 1M then it starts getting dicey. I have a junior dev on my team that is very good at python. I only consider him "jr" in the language/tech he's assigned to work on daily, in python i consider him pretty solid. When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and ex…
I’m probably outdated on this, but old Excel format have an upper limit of 65536 rows and the new format has a limit of 1M rows. So that’s where I would estimate the limit of upper bound. Could be less, can’t really think it’s possible to have more.
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#125Earlier quoted context omitted.
How does this sound? Formulae are supported, but the cell (not column) must be set as the Formulae datatype. The first formula that I am supporting is SUM, as it is easy to code in SQL. What are the other most common formulae that you use? I'll only implement the integer / float formulae at first, but I'll add string manipulation later. > Also filtering is essential. Filtering, of could. I've already got a hidden col…
> Formulae are supported, but the cell (not column) must be set as the Formulae datatype As long as it's not tedious to set a few thousand that sounds great. > What are the other most common formulae that you use? I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text). I've also used lookups…
> As long as it's not tedious to set a few thousand that sounds great.
> SUM, COUNT and IF (if-then)
Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture:
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#126Earlier quoted context omitted.
> The problem is I would guess less than 1% of Excel users actually understand this functionality Do you have any recommendations for getting started with this?
To answer my own question: looks like all you need is Excel 2016 or better. https://powerquery.microsoft.com/en-us/excel/
Absolutely! I would add though that you really need to follow a tutorial/book to make everything make sense in it. I read a book by Rob Collie - but any well reviewed book on DAX should do it. The book “Supercharge Excel - When you learn DAX…” looks like it covers similar information but more up to date.
The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table.
PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#127Modern stupidity at its finest, a point of no return was achieved
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#128Just don't use excel.
Or anything with autocorrect and autoformat.
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#129Earlier quoted context omitted.
Please, keep adding to the "tons of other GUI features" list. I'm just started on a Python/Qt/Sqlite project for spreadsheet-like functionality backed by a strongly-typed database. The things that you mention are perfect features. I'd love to know more.
That’s awesome! Where can we subscribe for any announcement? ;) To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic. Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publicati…
For right now I've started a Git repo but not uploaded any code until I decide on an architecture.
https://github.com/dotancohen/structuresheet
I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.
Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)
#130Earlier quoted context omitted.
I really wish there was a better interchange format for tabular data. Criteria: - Plain text file - Supports formatting, cell types, etc - Does not support full spreadsheet features like formulas
I've been thinking about a project exactly to replace this sort of Excel misuse. The frontend should be about as Excel-like as possible, where you can just easily type (or import) as many rows and columns you like, but without the formulas and other real spreadsheet calculations. Instead, make it easy to add data types, validation for those types, foreign keys, etc, and store the whole thing in a database.
Yeah, that's pretty similar to what I want. The other feature I'd love is the ability to add "Formatting rows" which are rich text and are marked as not being part of the data. They're merely decorative. I find such formatting is often considered essential for client-facing spreadsheets (somewhat understandably so they know what's going on), but it makes it quite messy to extract data from the sheet.