Weekly Dynamic: Dealing with Leading Zeros in Excel

When working with Dynamics GP it’s common to need to import information from other systems. Typically this is an extract from some other system into a comma separated values (CSV) file. Then an integration tool like Integration Manager is used to import the file into Dynamics GP.

The most common problems with this process happen when the source properly includes leading zeros. So a vendor ID for example might be 0001234. What happens is that a user opens the CSV to validate the information or adjust the file using Microsoft Excel. When the file is opened in Excel, the leading zeros get removed and users end up with a vendor ID like 1234.

So assuming that the source file does include the leading zeros, how does someone ensure that the leading zeros stay intact when working with the file in Excel?

1. Rename CSV file so it has a TXT extension. This tells Excel to trigger the Excel Import Wizard.
2. Within Excel, open the TXT file. You may need to use the  the ‘Files of Type’ drop-down list at the bottom of the dialog box, indicate that you want to open Text Files (*.prn; *.txt; *.csv). .
3. Excel starts the Text Import Wizard, displaying the Step 1 of 3 dialog box.
4. Check Delimited and Next to move to step 2.
5. Select Comma as the delimiter and click next to move to step 3.
6. At the bottom of the dialog box, click on the field that has leading zeros. The entire column should now be selected.
7. In the Column Data Format area, make sure the Text radio button is selected.
8. Repeat steps 6 and 7 for any other fields that have leading zeros.
9. Click on Finish. Your file is imported, with leading zeros still intact.

While there may be other ways to trigger the Excel Import Wizard this one works every time.