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.

I have two issues with a csv file that occur when I try and edit it in Excel.

  1. I have an ISBN column that gets reformatted to scientific format every time I save the file
  2. The encoding for the file is changed from UTF-8 to ANSI when I open the file.

It is really, really frustrating and a complete waste of time.

Is there any way that I can force Excel to leave the format of the file alone?

share|improve this question
    
Relevant: eusprig.org/stories.htm (My favorite horror story is #39) –  pavium Jul 1 '11 at 14:28
    
Again, PLEASE PLEASE don't use Excel for CSVs!!!! –  surfasb Jul 3 '11 at 5:52

2 Answers 2

up vote 6 down vote accepted

Don't double click on the CSV file because that will auto format the columns. Instead create a new spreadsheet and use the Data Import and you can manually format the columns to TEXT to avoid cutting off numbers and silly auto formatting.

share|improve this answer

It's sad that on the Windows side, Excel has somehow become the defacto editor of CSV files, even though it does a sub par job at it. I had the same experience as you as I was merging two speadsheets of contacts and importing them into Google.

The problem you are having is since CSV files contain no formatting metadata, Excel loads the text file with all default formats. If it detects date on the load, guess what? If it detects leading zeros, guess what??

I gave up with Excel. And you should too!

Access is a far better tool. For one, Access, by defaults, imports all data into as text. It doesn't do any gimmick formatting unless you tell it to. Two, it actually formats CSVs correctly by allowing you to surround all fields with quotation marks. It all leave untouched leading spaces. Spread the word.

Saves lives!! Spread the word!! Don't use Excel for CSVs!!! Use Access!!

share|improve this answer
    
I like that :-) Nice and passionate. Let me try it immediately! –  Martin Duys Jul 2 '11 at 8:14
    
Aargh! My Office licence doesn't include Access. Any ideas around other applications that I could use? –  Martin Duys Jul 2 '11 at 8:18
    
I'm going to try the options mentioned in this post –  Martin Duys Jul 2 '11 at 8:26
    
Yeah, anything but Excel to use CSVs. That sucks you don't have Access. 1000 times better at handling CSVs than Excel. –  surfasb Jul 2 '11 at 19:16
    
This is nice. Importing and exporting UTF-8 with Excel 2013 on Windows 7 always caused me problems. Access seems great 1) Once linked to a csv i dont have to configure import settings every time i load the file. 2) I can easily sort columns without the need to turn csv into an excel table and likely much more! –  bioslime May 25 at 10:52

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.