CSV Help!
August 20, 2008 11:27 AM Subscribe
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.
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.
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
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
Best answer: 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
-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
Response by poster: 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
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
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
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
posted by monkeydluffy at 4:46 PM on August 20, 2008
This thread is closed to new comments.
posted by le morte de bea arthur at 11:33 AM on August 20, 2008