Print Friendly

Importing Excel CSV files with Leading Zeros

When importing, or exporting files to and from Restaurant365 the required excel file format is CSV (Comma Delimited). In this file type, Excel will automatically delete leading zeros (a string of numbers that starts with at least one zero, i.e., 00123) causing inconsistency between R365 and the data in Excel.

For example the following Vendor Item Number contains leading zeros:

CSV files opened in Excel, will have any leading zeros automatically removed, as shown below:

 

If this file is imported as a New AP invoice transaction, the Vendor Item number will not be recognized and a ‘Missing Item’ tab will appear and require that the Item number be assigned to an item before the transaction can be approved. If this approach is taken, a new Vendor Item will be created resulting in redundancy issues.

 

To avoid this issue, change the column data type to ‘Text’. By doing this, Excel will allow the leading zeros to remain as part of the number.

 

 

After setting the data type to text in the Vendor Item Number column, save the file as a CSV and then perform the import. If the plan is to save the file and enter more data in later before performing the import, save the Excel file as a text file (txt) so that when the file is reopened the leading zeros will remain.

 

After the import, the AP invoice will look like this:

 

This same concept applies to other types of imports, including creating new items, and new balances. Just be sure to use the correct template for the import type you are performing.

Was this article helpful to you?

Comments are closed.