CSV Help!

August 20, 2008 11:27 AM

I need to save an Excel csv file of numbers with leading zeroes (e.g., 00987654).

Everytime I save the file and reopen it, it drops the leading zeroes. I know how to get them to appear (custom create a number format of 00000000) but they are not saved this way in the csv file. Help! The file has to be a csv file because I am importing data into a database, and those numbers are unique IDs assigned by the database and cannot be changed to ones without the leading zeroes.

posted by archimago to technology (8 comments total)

I've found that telling Excel to export the data enclosed in quotes usually does the trick. If the unique IDs in the database have leading zeroes, then I'd guess it must be a text field anyway(?)
posted by le morte de bea arthur at 11:33 AM on August 20, 2008

More info here
posted by le morte de bea arthur at 11:34 AM on August 20, 2008

but they are not saved this way in the csv file

That's because a csv file, by definition, doesn't contain any formatting information. If you open it up in excel, it will revert to whatever defaults are set in excel.

Thus, your options are:
1) Change the way excel handles leading zeros (is there a setting for this?)

2) represent your items as strings instead of numbers (as le morte de bea arthur suggests). Make sure that this isn't going to be an issue in your database import.

3) use something other than excel to edit the file. Is there some reason you can't use a text editor like notepad to edit it?
posted by chrisamiller at 11:39 AM on August 20, 2008

The issue here is not with Excel not saving the leading zeroes, it is with Excel not opening the leading zeroes.

-Create the file with the leading zeroes using the Cell Format option.
-Save the file as a CSV.
-Try "open with" on the file and open it with Wordpad or Notepad or whatever. The zeroes should be there. I think you will find that the database importer will see the zeroes too.

Good luck!
posted by milqman at 11:39 AM on August 20, 2008

That was exactly it, milqman. The file was saving it, but wasn't reading it when re-opened in Excel. Opening the file in Notepad revealed the zeroes were indeed there, and now my import into the database is working! Woo hoo!!

Thanks!!
posted by archimago at 11:46 AM on August 20, 2008

Looks like your problem is solved, but if you ever want to re-import the .csv into Excel, when you do the import set those columns to "text" format (instead of default, or number). That should keep the leading zeroes.
posted by inigo2 at 12:06 PM on August 20, 2008

If you want to bring the CSV into Excel without losing the leading zeroes, try this:

Data menu -> Import External Data -> select CSV file (may need to change file type at bottom)

(menus are different in Excel 2007, but the gist is the same)

Be sure that the numbers with leading zeroes are imported as text, not as numbers.
posted by scottso17 at 1:35 PM on August 20, 2008

Or you can change the file extension to .txt. When you try to open the file with Excel, it will walk you through a wizard which gives you the option to specfify whether a particular column is text or numbers.
posted by monkeydluffy at 4:46 PM on August 20, 2008