19

Kind of the inverse of this question.

I frequently download CSVs from outside sources as part of my programming work. I like to examine their data in Excel because, let's face it, Excel is an ideal tool for this sort of thing.

Except for one behavior: Excel attempts to outwit the data in the CSV, and therefore, if something looks like a number (like, say, many ISBN/EAN codes), Excel will treat it as such. And if this number is really big (like pretty much all ISBN/EAN codes), Excel will turn the number into scientific notation. Finally, if the number isn't particularly big, with most of these data formats, that indicates that Excel has decided that my number shouldn't have leading zeroes.

So ... Is there a way to open a CSV (via double-click if possible, but I'm willing to compromise here) in such a way that I can have Excel 2007 treat all the columns as text?

1
  • 9
    You sir, have great restraint. I would not have been able to ask this question without uttering profanity, because I find this behavior to be @!#$!@$ ANNOYING.
    – dreftymac
    Mar 25, 2010 at 18:43

5 Answers 5

16

You can, but it takes a couple of manual steps. These steps are from Excel 2003, but I'd assume the wizard is similar in 2007:

  1. Rename the file as TXT. Don't double-click it.
  2. Open Excel
  3. Click File, Open
  4. Locate and double-click the text file
  5. It should open a "Text Import Wizard"
  6. In step one, choose "Delimited"
  7. In step two, uncheck "tab" and check "comma"
  8. In step three, scroll-over to the last column, shift-click to select all columns, then click "Text" as the format.
  9. Ignore the annoying "Number stored as text" warnings everywhere

Goes pretty fast with some practice.

4
  • This is what I wound up doing today.
    – John Rudy
    Sep 8, 2009 at 23:13
  • 2
    This was helpful, thanks. As far as I can tell there isn't any other way of doing this. Changing the columns to 'Text' format after opening (without doing what you said) doesn't work either, the scientific notation remains until the cell is edited. Very annoying, and probably worthy of being considered a bug. Jul 13, 2012 at 9:02
  • 2
    @KarlNicoll It's actually worse than that; if you don't have the text type specified before the input is performed, it actually loses precision on the imported values (the last few digits of the "number" are rounded to zeroes).
    – BradC
    Oct 19, 2015 at 16:36
  • wow. excel sucks. going back to open/libre office May 3, 2018 at 13:53
0

Well there seems there no way to stop conversion of large numbers, So a work around this problem is to change the property of the field to text before it is opened in excel. Example if you are creating a COGNOS report with a field with numbers like provider No., which can be up to 15 character or more. in COGNOS change the property of that field to text before you run the report. So when you run the report as a Excel output excel will see it as text instead of number. Any number that you are not using in a calculation is quite safe to change to text.

0

I export from my online banking. I choose to save the file (not open it). It saves in the .csv format. Then I open an excel sheet, in the Data tab in the "Get & Transform Data" ribbon, I click on From Text/CSV, choose (double click) the .csv file I saved.

It will open with File Origin, Delimiter and Data Type Detection as headers. Keep everything the same except Data Type Detection - choose Do not detect data types. Then click on Load below. The data will be loaded into your excel sheet with all the digits correctly.

Hope this help. It was a mission to figure this one out

-1

The key to importing as a text, using the text import wizard is to prevent to original data import from saving as csv values. Since excel is the default for such files, it would "do its damage" before you get the file. so first download the file, do not double click to open it. Go the downloaded file and change the extension to 'txt' eg "mydata.txt" Now open excel and import changing the column you want preserved as text.

-1

What I've noticed is that you can change the format on the column to 'number' and set the decimal places to 0. This will display the full value in Excel after importing the file. The critical component here is that you MUST do this every time you open the CSV in order to retain the significant digits and prevent rounding. The issue with rounding the number seems to only occur if you save the CSV file prior to adjusting the number format. I've learned to keep backup copies for those moments of weakness when I forget about this silly 'feature'. I haven't been able to find a way to turn this off completely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.