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.
100questionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn MCommented:
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.
0
Joseph DalyCommented:
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.
0
aikimarkCommented:
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
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Glenn MCommented:
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.
0
aikimarkCommented:
If saved with a .txt file extension, Excel might be inclined to treat the column as text, especially if it is quote delimited.
0
Danny ChildIT ManagerCommented:
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....?
0
Ejgil HedegaardCommented:
It is not a one way trip.
As alkimark says
Excel might be inclined to treat the column as text
If 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.
0
100questionsAuthor Commented:
Solution:  When saving it to a csv, do not open that saved file with Excel, open it with Notepad.  The format will not be disrupted to the leading zeros.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
100questionsAuthor Commented:
This works.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.