Avatar of AaronSSH
AaronSSHFlag for United States of America

asked on 

How to preserve leading zeros in CSV file when saving in Excel

I have a large 300,000 row CSV file that I need to split into 4 CSV files. When I open this CSV file in notepad, I can see that the leading 0's are there for our part numbers: (000006446) for example. Excel by default truncates the leading zeros from view when I open the CSV. That's fine, I don't need to see them there. The problem is that when I use Excel to modify this CSV file and save a copy, then Excel updates the CSV file and removes all of the leading 0's from the file.

Does someone have a creative idea on how to preserve my leading zeros? I can't just convert the column to Text format because the leading zeros are already dropped from the view in Excel, so this too removes all of the leading zeros.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
AaronSSH
Avatar of Karen Falandays
Karen Falandays
Flag of United States of America image

Have you set a custom number format for the part numbers in Excel? That way they will be treated as text, as they should be
Format the cells as text. This will stop Excel removing the 0's
Avatar of AaronSSH
AaronSSH
Flag of United States of America image

ASKER

Karen, I can't set a custom number format because there isn't a standard naming convention. One part might be 0000646 but another is 002243-GR and another is BRACKET.
Avatar of Norie
Norie

Have you considered not opening the file directly in Excel?

How do you need/want to split it?
The field type is governed by the first few rows. You can force a type by putting a Schema.ini file into that file's folder..
Avatar of AaronSSH
AaronSSH
Flag of United States of America image

ASKER

Kenneth, can you be more specific? Any documentation I can find on using a schema.ini file is for importing into databases, not Excel files.
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Add them back with
=TEXT(A1,"000000000")

Open in new window

Avatar of AaronSSH
AaronSSH
Flag of United States of America image

ASKER

That does not work Shaun. 1) it doesn't add the leading zeros back in and 2) even if it did, my part numbers don't have a standard naming convention with a fixed length. Some might be 4 digits, while others are 20 digits.
You must be opening the csv file by double clicking it and the file will open then in Excel default program to open the csv file. This will remove any leading zeros.
To retain the leading zeros, you can use the Open Text File wizard or Power Query to get the desired output.

Refer to the following videos which illustrate both the methods.
Retain-leading-zeros.mp4
Retain-leading-zeros_PQ.mp4
Avatar of AaronSSH
AaronSSH
Flag of United States of America image

ASKER

Subodh, I have tried to use the Open Text File wizard. This lets met specify that the Part column is text and the leading zeros are preserved. But this introduces a new problem where the next column (CommentText) contains line breaks, and the Import Text File wizard treats the line breaks as a new row, completely throwing in the sheet everything out of order.
Sounds like your file is not really a CSV.  It must follow database rule where a column is a field. As such, it can only have one format.  Text format sounds like your best route.
Avatar of AaronSSH
AaronSSH
Flag of United States of America image

ASKER

It is a valid CSV, I can confirm in notepad. Every field is correctly delimited by commas and surrounded by quotes. It's just that one of the fields contains line breaks between the quotes, and that throws off the excel import as text wizard.
ASKER CERTIFIED SOLUTION
Avatar of AaronSSH
AaronSSH
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of laltobelli
laltobelli
Flag of United States of America image

Hi Aaron,

I'm having the same issue.  The CSV file wants to change text fields with leading zeros to dates, other numbers or drop the 0 altogether.

did you ever find a resolution to the issue?

Thanks,

Larry
Avatar of AaronSSH
AaronSSH
Flag of United States of America image

ASKER

No solution found. The workaround for me is to only use .CSV files and only edit them in Notepad++. Not a great solution, but better than invalid values.
Microsoft Excel
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo