How to keep leading zeros in cvs file format

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
I am using a macro to create a csv file (comma seperated text file). I have some entries which are numbers with leading zeros e.g 0001245

I want to keep these leading zeros and have them appear when the file is eventually opened in Excel.

I am able to capture them in the csv file by using double quotation marks and it appears like this in the cvs file

" 0101 "," 1792 ","0001245453"

But when opened in Excel the leading zeros are always dropped.

Is there any way to keep the zeros showing when opened in Excel?

Thanks

voodoo
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
Easy, import the file using the data import wizard and DO NOT skip the last step, you have to set the data import column format to text or else it will ALWAYS drop those leading zeros.

Even if you set the column format before doing the import, it will set it to general unless you specify and therefore drop the zeros.
 

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
Thanks for the reply

Can you tell me exactly how to access the data file wizard you mention?

I know when I try and open a txt file in Excel it goes through a text file wwizard which gives these kind of options. But with csv file when I Press File then Open and select the file it juts opens automatically in Excel without giving any options.

Another complication is I am creating this file in a macro and at the end of the macro the file needs to be shown in an open state. So I would need to access the data file wizard via the macro using VBA if this is possible?

Thanks again for your help
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
You can either rename the csv file to txt or you can also import a csv file just as you would a txt file. Goto Data - > Get external data.
 

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
Thanks for your help

I worked it out in the VBA as well. I was using workbooks.Open instead of Workbooks.OpenText
 

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
Oh no upon checking again this is not working, it only works when my file is a .txt file. it doesnt work for .csv.

The thing is my users want a .csv not a .txt file

Another thing is my users all use Excel97 and there is no Inport Text File option from Data -> Get External Data. This appears to be new to Excel 2000. So this appear not to be an option as well.


Any other ideas?
 

Forum statistics

Threads
1,143,617
Messages
5,719,736
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top