How can I save an Excel spreadsheet to CSV and preserve the format?
Spreadsheet with 2 columns, A and B.
Column A contains Item numbers, many of which start with leading zeros. ie 00001, 00002, etc...
When you save the Excel spreadsheet to CSV it removes the leading zeros and the numbers show up only as 1, 2 etc.. it must keep the leading zeros.
Column A contains Item numbers, many of which start with leading zeros. ie 00001, 00002, etc...
When you save the Excel spreadsheet to CSV it removes the leading zeros and the numbers show up only as 1, 2 etc.. it must keep the leading zeros.
Last Comment
CSV is typically just ASCII text so there's no way I'm aware of to preserve any formatting. Even if you format the cells in Excel as text when you save as CSV the leading zeros will get stripped out.
This is correct. CSV files will not save formatting, spacing, font, color, etc. In its simplest form it is data in plain text seperated by commas. If you want those options you need to keep it in xls or xlsx format.
If you format the cells/columns as text, you should be able to output a CSV file with the leading zeros preserved.
See the attached CSV for an example
Q-28586471.csv
See the attached CSV for an example
Q-28586471.csv
That's true aikimark. We don't know what application the posted will be opening the CSV file in. I know in Excel it will treat it as a general format and strip the zeros.
If saved with a .txt file extension, Excel might be inclined to treat the column as text, especially if it is quote delimited.
If you can start your text with an ' (apostrophe) character, you can then add as many leading zeros as you want, and if saving to CSV, then the formatting should be preserved.
HOWEVER, if you open the CSV in Excel again, the formatting is lost - even with double-quotes " around them.
So, it seems to be a one-way trip. Does this work for you? Why do you need ad CSV, please? Where's the data going....?
HOWEVER, if you open the CSV in Excel again, the formatting is lost - even with double-quotes " around them.
So, it seems to be a one-way trip. Does this work for you? Why do you need ad CSV, please? Where's the data going....?
It is not a one way trip.
As alkimark says
As alkimark says
Excel might be inclined to treat the column as textIf the csv file is imported to Excel using the data import wizard, you can define the column to be treated as text, and the leading zeros are preserved.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This works.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
