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.
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.
Last Comment
Karen Falandays
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
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.
Have you considered not opening the file directly in Excel?
How do you need/want to split it?
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..
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.
Add them back with
=TEXT(A1,"000000000")
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
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
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.
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
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.
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
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
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 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
