An unscientific method?
July 22, 2010 1:56 PM Subscribe
ExcelFilter: Help me avoid scientific notation when importing a .csv file. I face the same problem faced by "Mark" in this article. Is there a solution to this problem that does not require changing the file extension? I am using Excel 2003.
You could find and replace "e" with another character prior to the import and then find and replace the other character with "e" after the import.
posted by cnanderson at 2:10 PM on July 22, 2010
posted by cnanderson at 2:10 PM on July 22, 2010
Did you try "import" or "open"?
If you import the data into a blank worksheet and specify the file type as "text" you should be able to control the column format before importing. I have Excel 2007, where this option is under "Data"-->"Get external data" and don't remember the 2003 version -- I think it is "Data"-->"import external data".
posted by prenominal at 2:13 PM on July 22, 2010 [1 favorite]
If you import the data into a blank worksheet and specify the file type as "text" you should be able to control the column format before importing. I have Excel 2007, where this option is under "Data"-->"Get external data" and don't remember the 2003 version -- I think it is "Data"-->"import external data".
posted by prenominal at 2:13 PM on July 22, 2010 [1 favorite]
I think it's just a formatting thing. After reading in the .csv, try selecting all cells (click up and to the left of the first cell) and then change the formatting to regular numbers à la these instructions. Fewer steps than going through the import wizard, anyway.
posted by en forme de poire at 2:22 PM on July 22, 2010
posted by en forme de poire at 2:22 PM on July 22, 2010
Oops, just saw you're using Excel '03. I think the option you need is under "Format" -> "Cells".
posted by en forme de poire at 2:23 PM on July 22, 2010
posted by en forme de poire at 2:23 PM on July 22, 2010
Wow, two strikes! Sorry, I re-read the question more carefully. Try converting everything to "text" under formatting, not "numbers", and see if that helps.
posted by en forme de poire at 2:28 PM on July 22, 2010
posted by en forme de poire at 2:28 PM on July 22, 2010
I'm not sure if the formatting alone will work. For instance, I just imported 23e23, which Excel recognized as 2.3E+24. If you format this as text, this value remains the same (2.3E+24, instead of 23e23).
posted by prenominal at 2:29 PM on July 22, 2010
posted by prenominal at 2:29 PM on July 22, 2010
You format it when you import, not after it's imported. On step 3 of 3 click on the column you want to change and select text as the column data format. (screenshot) That will tell Excel to ignore it's smart formatting and leave it how it is. It worked for me with an e in the middle too (23e23).
posted by msbutah at 2:59 PM on July 22, 2010
posted by msbutah at 2:59 PM on July 22, 2010
I'm not sure everyone is understanding the situation - the article specifies that a workaround is to go through the manual import process, which I'm assuming the OP does not want to do (because frankly it's a waste of time).
posted by wongcorgi at 3:11 PM on July 22, 2010
posted by wongcorgi at 3:11 PM on July 22, 2010
You could also open up the CSV in a text editor and do a Replace All - replace any instance of a comma with a comma-followed-by-an-apostrophe. This will put an apostrophe before each value, which will make Excel treat it as a literal plain-text value. You can then bulk-replace all the apostrophes in Excel. Bing, bang, boom. :-)
posted by julthumbscrew at 3:13 PM on July 22, 2010
posted by julthumbscrew at 3:13 PM on July 22, 2010
This thread is closed to new comments.
This is why I never save files as xls files anymore; I exclusively work in .csv because it's a sensible format that doesn't try to ruin my data. No fonts or formatting, but I'm just a data guy, so I guess I don't need those things.
Sorry for the non-answer - if somebody else knows how to do this, I'll be very happy to hear about it.
posted by koeselitz at 2:09 PM on July 22, 2010