This video looks at a problem you sometimes get with CSV files that you need to import into Excel. Download the featured file here. The problem is where you have fields that contain values with leading Zeros. The CSV file featured in the video has two fields with leading zeros: the transaction ID field and the telephone number field.
If you were to open this file directly in Excel it would display those fields without the leading zeros. Excel does this is it assumes the fields are numeric fields – numbers cannot start with a zero in Excel.
The best solution for this problem is different for different versions of Excel. If you have Power Query installed, which would be the case in Excel 2016 and Excel 365, then use the first solution below, otherwise use the second.
Power Query Solution
To use Power Query to solve the problem of leading zeros:
- Click on the Ribbon’s Data tab. Then click From Text/CSV in the Get and Transform Data group.
- Browse for and select the CSV file you want to import into Excel. Then click Import.
- Power Query opens and gives a preview of the import. Click on the Transform Data or Edit button, bottom-right of the dialog box.
- In the Power Query Editor change the data type for columns that you want to retain leading zeros in. The data type will currently be number but it needs to be changed to text. You can do this by clicking on the button to the left of the column heading, (currently the button will display 123).
- Once you have selected a new data type, Power Query will ask you whether you want to replace the current step or add a new step. Always select Replace current.
- Once the data type has been changed you can load the data into Excel by clicking on the Close & Load button on the Home tab.
Text Import Wizard Solution
If you don’t have Power Query, this solution works just as well.
- On Excel’s Data tab, click the From Text button in the Get External Data group.
- Browse and select the CSV file you wish to import into Excel and then click on Import.
- The Text Import Wizard opens. In step 1 select Delimited as your file type. Click Next >.
- In step 2, specify that your delimiter is a comma. Click Next >.
- In Step 3, specify that the columns containing leading zeros should be imported as text. To do this select the relevant column/s in the data preview and select text in the format options above. Click Finish.
- In the Import Data dialog select the cell that you want to import the data to and then click OK.