• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • 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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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