Excel -> CSV - how to keep leading 0's

[dpnab]

I'm saving (as values) an excel file into a CSV file but it's removing any leading 0's I have. I set the cell as text, didn't work. I set it as custom with the appropriate amount of 0's, same thing.

The cell is a formula (vlookup) so it returns 2 leading 0's but I can't get it to stay in the CSV file.

Any ideas on how to fix this?

Thanks!

 

[Joe4]

You probably don't need to fix anything. The issue is probably how you are trying to view your CSV file.

Exporting it from Excel to CSV will actually maintain the leading zeroes.
My guess if you are trying to use Excel to view the CSV file after doing that. You NEVER want to use Excel to view CSV files.
The reason is because it will not show you the data as it really exists.
Excel will actually perform data conversions on your CSV file, and if anything looks like a number, it will treat it as one, thus dropping leading zeroes.

If you want to view the TRUE contents of a CSV file, use a Text Editor like NotePad to view it after creating it.
This will show you TRULY what the CSV file looks like.

This to me is one of my biggest Microsoft complaints, that they set Excel to be the default program for opening CSV files.
One of the first things I do when I get a new computer is to change that default program to NotePad.

 

[dpnab]

You probably don't need to fix anything. The issue is probably how you are trying to view your CSV file.

Exporting it from Excel to CSV will actually maintain the leading zeroes.
My guess if you are trying to use Excel to view the CSV file after doing that. You NEVER want to use Excel to view CSV files.
The reason is because it will not show you the data as it really exists.
Excel will actually perform data conversions on your CSV file, and if anything looks like a number, it will treat it as one, thus dropping leading zeroes.

If you want to view the TRUE contents of a CSV file, use a Text Editor like NotePad to view it after creating it.
This will show you TRULY what the CSV file looks like.

This to me is one of my biggest Microsoft complaints, that they set Excel to be the default program for opening CSV files.
One of the first things I do when I get a new computer is to change that default program to NotePad.

Perfect thanks. I opened it in notepad and it shows the 0s.

 

[dpnab]

There's another issue. The CSV I'm using is meant to import into a program. For a different column, unless I go into excel, and simply double click in the cell, save it, it won't work.

I looked at it in notepad and it doesn't change.

So what I'm doing is:

1. Run macro to save excel as csv
2. Upload CSV - get error
3. Open CSV in excel, double click on specific cell
4. Upload CSV - it works

Again, when I look at the CSV in notepad, nothing looks different at all. What could be the possible cause?

 

[Joe4]

That's a bit cryptic.
Can you tell us more about the specific cell?
What exactly is in it?

If you look at the value in that cell in NotePad AFTER you create the CSV but BEFORE you re-open it in Excel and double-click it, what does it look like?
Then, If you look at the value in that cell in NotePad AFTER you re-open it in Excel and double-click it, what does it look like?

 

[dpnab]

So I looked at it again, and as long as I open the csv file in excel, save it, it will upload the csv file correctly. So it's not a specific cell.

In regards to the leading zeroes, when I open the csv in notepad the first time, it shows the leading 0's.

If I open it in excel, save it, and then open it in notepad, the 0's are gone.

I can probably live without the leading 0's, the question I have is why do I need to open the csv file in excel in order for it to work?

 

[Joe4]

In regards to the leading zeroes, when I open the csv in notepad the first time, it shows the leading 0's.

If I open it in excel, save it, and then open it in notepad, the 0's are gone.

Yes, as I explained, opening any CSV file in Excel that has numeric values with leading zeroes, Excel will perform its own conversion on it and drop the leading zeroes.
And if you then save the CSV file in Excel, you have "saved" this dropping of leading zeroes.
For this reason, you usually do not want to edit CSV files in Excel, as Excel will perform its own conversions on data as it sees fit, whether you like it or not.

I can probably live without the leading 0's, the question I have is why do I need to open the csv file in excel in order for it to work?

I really cannot help you with that if you don't give me the detail I asked for in my last post, since I have no idea what this data you are double-clicking on looks like.
I want to to know what it looks like in NotePad BEFORE and AFTER you perform these steps.

 

[dpnab]

Yes, as I explained, opening any CSV file in Excel that has numeric values with leading zeroes, Excel will perform its own conversion on it and drop the leading zeroes.
And if you then save the CSV file in Excel, you have "saved" this dropping of leading zeroes.
For this reason, you usually do not want to edit CSV files in Excel, as Excel will perform its own conversions on data as it sees fit, whether you like it or not.


I really cannot help you with that if you don't give me the detail I asked for in my last post, since I have no idea what this data you are double-clicking on looks like.
I want to to know what it looks like in NotePad BEFORE and AFTER you perform these steps.

The only difference is the leading 0's are removed. Everything else stays exactly the same. But the error I get it referencing a different "cell", but nothing in that "cell" changes before and after (ie there are no leading 0's)

 

[Alex Blakenburg]

Just a thought. If after step 2 you open the file that errored out in the upload in Notepad and go right to the bottom of the file.
Is there any chance that you have a number of lines just filled with commas eg , , , , , , , ?

2. Upload CSV - get error

 

[dpnab]

Just a thought. If after step 2 you open the file that errored out in the upload in Notepad and go right to the bottom of the file.
Is there any chance that you have a number of lines just filled with commas eg , , , , , , , ?

I do when it's first exported into the CSV file (checking in notepad). When I open it in excel and save, it removes them.

I'm guessing that's the issue? (I have a lot of "") cells in excel since I'm trying to leave them blank.