Dynamics GP Tip: Understanding Excel and Leading Zeros
Learn how to retain leading zeros when data is exported from Dynamics GP to a CSV or Excel file.
Learn how to retain leading zeros when data is exported from Dynamics GP to a CSV or Excel file.
Table of Content
Improve the way you view data by understanding Excel.
Some fields in Microsoft Dynamics GP may contain data with leading zeros. For example, a Vendor ID may be recorded as 0001234. If this data is exported from GP as a CSV file, then opened in Excel, the leading zeros will be stripped out. This is true for a file exported from any source (not just GP).
The above CSV file was opened in Excel, and the leading zeros in the first field are gone.
To prevent this, rename the file to a .txt file. For example, Sample CSV.csv was renamed to Sample CSV.txt. Then open the file in Excel. This will launch the Text Import Wizard. Choose Delimited as the file type that best describes your data and click Next.
Choose Comma as the delimiter, then click Next.
Highlight the column in the “Data preview” section that contains the data with leading zeros, and then select Text as the column data format. Repeat for each column that contains data with leading zeros. Then click Finish.
When the file opens in Excel, the leading zeros are retained.
Find more Tips & Tricks for using Microsoft Dynamics GP here.