Forum Discussion
CSV file is dropping zeros on zip codes when imported
Hi,
I have a csv file that I eventually need to reformat as an iif file in order to upload it to outlook.
There is a column in the file that contains zip codes, some have leading zeros.
I have tried formatting this column as a zip code but no matter what I do when I reopen the file or try to reformat it as an iff file the zeros are dropped.
Any help would be appreciated,
Thank you
ABcomputer Try importing the file. Do NOT open it directly in Excel by double-clicking it, as Excel will then assume that the zip codes are numbers and thus drop all leading zeroes.
Importing can be done in different ways. On the Data ribbon, Get Data, CSV/TXT (i.e. Power Query) or Get Data, From Text (Legacy).
I suspect you are not familiar with Power Query. Otherwise, you would not have asked the question. So, let's go for the From Text (Legacy) option. It opens a window, just like the one in Text-to-columns. Follow through steps 1 and 2 to create separate columns (probably Delimited, Comma). In step 3 of 3, in the preview box, select the column with the zip codes and explicitly set the data format to Text.
Press Finish. Now your CSV data should be neatly organised in columns with the leading zeroes of the zip codes preserved.
- What I have done so far has not worked-
Here is what I did-
Data-> From text/CSV-> Selected the file-> Transfrom -> selected the column-> on the transform ribbbon data type= text-> Close and load-> I get an excel sheet with the zeros -> save as csv and the zero are lost againABcomputer You went for the PQ solution anyway. You'll find he Finish button in the Text import option.
Anyway, you managed to import the numbers with the leading zeroes. And you say that when you save the file as a CSV, the leading zeroes disappear. I've never tried to do this myself and can't explain what's happening. Saving as a CSV should just create flat text. No formatting or intelligence whatsoever. But perhaps I'm wrong.
- Also once the query window opens, should I click on load or transform data?
Riny_van_Eekelen Thank you so much,
Can you send me a screenshot of where the finish button would be? I am probably not in the right place
