Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
2  
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 '10 at 18:43

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
    
This is what I wound up doing today. –  John Rudy Sep 8 '09 at 23:13
1  
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. –  Karl Nicoll Jul 13 '12 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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