Excel/CSV WTF
-
We've got an app that imports data from CSV. I didn't write the import - presumably they could have made it support Excel as well. That's not the WTF.
One of our users has been complaining for a while that she was getting weird behavior when she imported. We investigated (using our own CSV files) and figured out that we'd been bitten by the old issue where Microsoft Jet tries to guess the column types, and then discards any data that doesn't match the guessed types. That's *a* WTF, but not *the* WTF.
In order to make this change on the app we had to change a registry setting on the server. It was completed by IT yesterday and she continued testing. However, she complained that the application was still importing junk. We verified the settings, imported another file, etc. Finally one of the engineers asks her to send him the file she's trying to import, so we can see if there's something unusual with it.
When the file arrives, he opens it in Notepad to inspect it - it's only 27K, and she claims it should have five columns and about a hundred rows. When the file opens, we see nothing but random junk. It's obviously a binary file. So I had him rename the file from "import.csv" to "import.xls". Hey, guess what - Excel likes the file just fine.
So following some swearing and laughter, we informed her that she can't just change a file extension to "CSV" if she wants a CSV file.
-
@GalacticCowboy said:
So following some swearing and laughter, we informed her that she can't just change a file extension to "CSV" if she wants a CSV file.
I can't? Next thing you're going to tell me is that I can't rename my .dll files .png and open them up as hidden photos from the MS christmas party.
-
In all fairness, renaming xls to csv is only slightly less successful than saving as csv in Excel. Excel's support for delimited text files is rubbish. I've had OpenOffice Calc installed on a few people's computers so they can use that instead.
As a bonus, Calc has an awesome delimited text file importer. You can select any character delimiter you want and even manually mark field locations if you have fixed width fields.
-
Hey cowboy whats up, been a while.
the problem is going the other way, take a CSV file and name it XLS and excell still opens it. I bet your user will use that as a reason why you are wrong.
-
@shakin said:
As a bonus, Calc has an awesome delimited text file importer. You can select any character delimiter you want and even manually mark field locations if you have fixed width fields.
You can do that in Excel too.
I've never used OO for this but if it doesn't turn any moderately large number (looking at you, EAN codes) into @$*%! scientific format I might start.
-
If you double click a CSV file from Windows Explorer to open it in Excel (assuming you have .csv extensions to open in Excel) then the results are generally shit.
If you start Excel and then open the CSV file from there it works a hell of a lot better, including automatically prompting you with the text import wizard.
I learned this the hard way after I had users screaming at me that Excel was mangling the CSV output of their reports.
-
Not exactly a WTF, just an usual case of an uninformed user.
@Yesterdays Article said:[...]many users are simply lazy, stupid, or lazy and stupid[...]
@Robajob said:I've never used OO for this but if it doesn't turn any moderately large number (looking at you, EAN codes) into @$*%! scientific format I might start.
As far as I know, Excel only displays it using scientific notation. But since it still has the full number, using appropriate formatting should show it as wanted.
-
@random.next said:
As far as I know, Excel only displays it using scientific notation. But since it still has the full number, using appropriate formatting should show it as wanted.
The problem happens when you save the file in Excel. It's still a CSV file so it saves the representation of the number, not the full precision that would have been stored in a native Excel cell.
@KattMan said:
the problem is going the other way, take a CSV file and name it XLS and excell still opens it.
I think this only works for tab-seperated files. I tested it last year and I'm pretty sure I tried commas and tabs and only tabs worked. It does make a neat solution when your customer insists on getting Excel files and you don't want to install any extra components on the server.
-
@KattMan said:
the problem is going the other way, take a CSV file and name it XLS and excell still opens it.
Even better, take an html file and name it XLS, it looks quite ok in Excel ... We used it a while back to export data from a webapp ...
-
@Nelle said:
Even better, take an html file and name it XLS, it looks quite ok in Excel ... We used it a while back to export data from a webapp ...
Of course, stupid Office 2007 has to complain that it's not a "real" Excel file, and everybody complains that the site is broken. I still don't understand why Microsoft added that "feature".
-
@GalacticCowboy said:
One of our users has been complaining for a while that she was getting weird behavior when she imported. We investigated (using our own CSV files) and figured out that we'd been bitten by the old issue where Microsoft Jet tries to guess the column types, and then discards any data that doesn't match the guessed types.
I hate that so much. I always end up with columns containing a handful of float values and a bunch of blanks. I then have to import it with Access (another bag of WTFs, but at least they don't overlap on this one), which gives the right column data types, and then export to Excel. Then take anti-hypertension medications.
-
@amischiefr said:
I can't? Next thing you're going to tell me is that I can't rename my .dll files .png and open them up as hidden photos from the MS christmas party.
It only works with .gif, moron! Windows can't even open .png's.
-
-
@Qwerty said:
@random.next said:
As far as I know, Excel only displays it using scientific notation. But since it still has the full number, using appropriate formatting should show it as wanted.
The problem happens when you save the file in Excel. It's still a CSV file so it saves the representation of the number, not the full precision that would have been stored in a native Excel cell.
@KattMan said:
the problem is going the other way, take a CSV file and name it XLS and excell still opens it.
I think this only works for tab-seperated files. I tested it last year and I'm pretty sure I tried commas and tabs and only tabs worked. It does make a neat solution when your customer insists on getting Excel files and you don't want to install any extra components on the server.
The real "beauty" of Excel is that it remembers your "text to columns" settings, and tries to apply them the next time you open any file, paste in data, etc. So if you ever split anything besides tabs, you'll see very odd behavior until you switch it back. (Well, not "odd" so much as just "Why the heck is it splitting this by spaces when I paste from the clipboard?")
Hey Katt. Life treating you ok? :)
