6 ms·
I absolutely hate Excel. It's not the number crunching that's the problem. Database -> Excel, Excel -> Database issues are almost unavoidable. In 2 months of wo
by baak 14y ago
I absolutely hate Excel. It's not the number crunching that's the problem. Database -> Excel, Excel -> Database issues are almost unavoidable. In 2 months of working with SSIS packages, I encountered every one of these problems (I'm not the original author of this rant):
"Anyone who has worked with a database in a professional capacity for more than 20 minutes should have a list of at least 10 reasons why Excel is a monster. These probably include:
1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)
2. Dates of any kind
3. Serial numbers that have leading 0's (see #1)
4. The JET database driver for Excel. One large WTF.
5. SQL Server Integration Services Excel datasource. WTF squared.
6. The f-ing "just put an apostrophe" workaround. WTF.
6. a. The equally effective "format as text before you paste" workaround. Gives the illusion of working, only to break later.
7. Save as CSV, then reopen the CSV in Excel. Lots of magical things happen there.
8. While on the topic, CSV files, which are a whole WTF on their own.
9. The Jet database driver's "type guess rows" registry entry. WTF factorial.
The root of all this: Excel makes things that look like tables, and tables are useful for data. There is no other program that is as widespread AND makes things that look like tables, so people use Excel to make tables of data. And it's in fact really, really bad at that. It was designed for ad-hoc numerical analysis and got appropriated as a database loading and reporting tool.
I think it's actually damaged the GNP of whole nations, this Excel program. It'd be interesting to know how badly."
- smackfu 14y agoReally, the main problem is that Excel (which has data types) is trying to support CSV (which has no data types) as a pseudo-native format. If Excel forced CSV files through the import wizard, and you could override a data type for each column, it would solve most of the issues. Instead, each column is implicitly treated as Auto and that fails in a lot of cases.
- flatfilefan 14y agolast time I looked there was exactly such a wizard with the override functionality
- mbetter 14y agoThere is, it just isn't invoked for a .csv file. As someone who basically lives in Excel for 40 hours a week, I find my quality of life to be much improved when I keep my text files tab delimited.
- EEGuy 14y ago+0001
- smackfu 14y agoYeah, it works great... but only if your csv file has a .txt extension and you select that is a delimited file.
- deleted 14y ago[deleted]
- deleted 14y ago[deleted]
- deleted 14y ago[deleted]
- deleted 14y ago[deleted]
- deleted 14y ago[deleted]
- deleted 14y ago[deleted]
- kyllo 14y agoThe leading zeroes problem is a constant thorn in my side. You basically have to never, ever open a CSV file in Excel.
- baak 14y agoYep. Opening a CSV file in excel: http://support.microsoft.com/kb/215591 http://support.microsoft.com/kb/215591 What a nightmare. Do they not realize how many DB tables start with 'ID'? And CSV is too common a format to just completely ignore. It's not always up to us.
- zeidrich 14y agoApplies To: - Microsoft Excel 2004 for Mac - Microsoft Excel 2001 for Mac - Microsoft Excel X for Mac Sure, it sucks that it was there at all, but the most recent mentioned is a 9 year Mac version of the software (which is written by a different team than does the Windows Office anyways).
- pfg 14y agoI believe I ran into the same problem about a year ago using Office 2010 (on Windows).
- jackalope 14y agoYes! This ridiculous bug is still out there and is why I never use 'ID' when designing my schemas. Seriously, why can't reserved words be designed to be so uncommon, you'll never have a conflict? If I see another 'klass' object in Python or email broken because someone started a sentence with 'From' I'm going to cry.
- baak 14y agoI definitely had this issue with Office 2007 during my internship a few years ago.
- wazoox 14y ago
- alushta 14y agoSerial numbers and postal codes should be entered as text and not doubles. The user should have prefixed the text with '. It's not Excel's fault that the user doesn't know how to use it.
- JumpCrisscross 14y ago"The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)" Excel, by default, treats any numerical object as a number. Numbers don't have significant leading zeroes. You can change the default data type, i.e. "format", to text or even postal code to preserve leading zeroes.
- MichaelGG 14y agoI find that does not always seem to work. We get CSVs with telephone numbers, and depending on the length, they end up being shown using E notation, even after selecting text formatting.
- avenger123 14y agoWhy not run the CSV file through a custom made app that "cleans" each line in a proper format that will easily be imported into Excel, taking into account Excel's quirks. The app then spits out another CSV that Excel is able to properly import. I find this solution a good compromise, although it may not be possible to do it.
- pseut 14y agoIs this what you do? Because if someone sends me a csv file, under your workflow I'd have to open it in a text editor (or a different spreadsheet program) to figure out the data type of each column and run it through a perl script (or awk?). For a few columns, sure. For 200 columns, not ideal. Just to get around using excel's impmort directly. Plus, "taking into account Excel's quirks" is easier said than done. That said, if you have a script you use that does this, you'd make a lot of friends if you posted a link.
- avenger123 14y agoI only do this for automated processes where I know the exact requirements of the CSV and the data is large. I definitely wouldn't do it for ad-hoc type of scenarios. But for some things it just easier to put out an Excel file. In one scenario I have a set of complext spreadsheets that are updated nightly. I use EPPlus (http://epplus.codeplex.com/ http://epplus.codeplex.com/) a C# library which updates the appropriate data in the spreadsheet. In an other scenario, I am taking in transactions (accounts payable/receivable, general ledger,etc.) that are in CSV format, applying some business rules and inserting them into a spreadsheet. This spreadsheet is then used by the accountants to do postbacks to the actual ERP system. I looked at doing this using the ERP's own batch interfaces and couldn't justify the time and expense as Excel was the best way to get the data in. The above library doesn't require Excel on the machine. With Microsoft moving to an XML format for the file, it's made it much much easier to do these things. This particular library works well as long as you are doing simple data updates. There is no ambiguity in terms of the type of the data as you are able to explicitly state what is stored in the cell. I would love to know if other library like this exist for Ruby or even Python.
- mikec3k 14y agoExcel is not a database. The problem is people trying to use it as a database.
- doppenhe 14y agoExcel can be used as a database for analysis though.. check out www.powerpivot.com. This functionality also comes in Excel 2013.
- revelation 14y agoAt least its not parsing 01033 as octal..
- jorgeleo 14y ago"3. Serial numbers that have leading 0's" and "9. The Jet database driver's "type guess rows" registry entry." One word: UPC I still have nightmares....
- einhverfr 14y ago> 7. Save as CSV, then reopen the CSV in Excel. Lots of magical things happen there. Indeed. My favorite one is accounting spreadsheets tending to export accounting numbers as text columns with trailing whitespace. That was off a recent version of excel for the Mac. I understand it's a cute convention for currency formatting but it makes data transformation in a database very, very annoying.