• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

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.
0
100questions
Asked:
100questions
  • 2
  • 2
  • 2
  • +3
1 Solution
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
100questionsAuthor Commented:
This works.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now