Last in my series on Xero Inventory is an overview of how you can import inventory items, and starting inventory balances, into Xero.
Two separate import features are provided. Xero works with comma-delimited (CSV) files, and they provide you with templates for the file layouts that you need. One feature is used to import new items, or update some information in existing items. The other feature is used to import opening quantity and value balances for those items. The process is straightforward, although there are a few places where you might need some additional information.
Charlie Russell will be presenting the session, Recent Developments in QuickBooks and Looking Ahead, at Accountex 2016.
This article is the fifth and last in my series on Xero inventory. Note that Xero provides inventory tracking in all levels of their product. Here’s a list of the articles I’ll have on this topic:
- Xero Inventory (basics)
- Working with Xero Inventory
- Xero Inventory Costing
- Xero Inventory Reports
- Importing Inventory Records to Xero (this article)
Remember from my first article, Xero has both tracked and untracked inventory items, where tracked items are those that we are working with a quantity on hand and inventory value.
Note: Online accounting products can evolve quickly, so operational details on inventory can change quickly. Look at the tag #Xero Inventory to see the latest information about the inventory control features of this program.
Xero Inventory Import
If you want to add new items, or update existing items, you can create a comma-delimited (CSV) file and import it easily. Xero does a good job at trapping errors here, but there are a few things I would like to see improved.
The import file has to be formatted in a particular way, and Xero provides you with a template.
Select the Accounts menu and select Inventory to open the inventory item list. The Import button at the right of the screen has an option for Items.
The Import Items window displays. In the first step they provide you with a link to download a CSV import template file.
Use this template to create your import file. You must not change any of the headings, or change their order. Note that the only field that must have a value is ItemCode. You can use this to create both tracked and untracked items, which you control by which fields you enter values into.
The fields in the template are:
- *ItemCode: This is the only required field. If you have a value that is not found in the existing Xero inventory item list, this record will be added. If the item code already exists in Xero, then the values will be updated.
- PurchasesUnitPrice: If left blank, the value will be 0.00.
- PurchasesAccount: If you enter a valid account number then the “I purchase this item” box is checked.
- SalesUnitPrice: If left blank, the value will be 0.00.
- SalesAccount: If you enter a valid account number, then the “I sell this item” box is checked.
- InventoryAssetAccount: If you enter a valid account number here and in the CostOfGoodsSoldAccount column, this item will be a “tracked” item.
- CostOfGoodsSoldAccount: If you enter a valid account number here and in the InventoryAssetAccount column, this item will be a “tracked” item.
Account numbers are entered using the Code. For example, if you have an account “1000 – Purchases,” you would enter the value “1000.” The accounts must already exist in Xero.
If you want to create a “tracked” inventory item, then you must have a valid account for both InventoryAssetAccount and CostOfGoodsSoldAccount. If both are blank, then an “untracked” item will be created.
Tax rates are entered by name. For example, if you have a tax rate that shows in Xero as “No Tax (0%),” you can enter “No Tax (0%)” or “No Tax.” You cannot enter “0” or “0%.” And the tax rate must already exist in Xero.
You can only list an ItemCode once in the import file. If there are duplicates, the first will be accepted but others will be rejected.
Note that a maximum of 1000 items can be imported in one import file.
Once you have your import file set up, return to the Import Items window. Select the import file and click Continue to start the process. The file will be evaluated and a summary will be displayed. Nothing has imported yet, you have the option of continuing or abandoning the import.
This window tells you if there are any errors, and specifies what the problems are. I wish that they had a way to print or export this list, so that I would have a way to save it when I want to go back to correct the file if there are errors. It would also be nice if they listed the ItemCode of the line that had an error rather than just the line number, for easy reference. I do like that they give you a preview before the import is finalized.
If you want to continue, click the Complete Import button. The import will take place, and when done you will be returned to the inventory item list. Keep in mind that you cannot “undo” an import.
Note that you can use this process to convert untracked items into tracked items in bulk. Export your list to CSV, add the inventory asset and COGS accounts to each item, and then import. It isn’t an elegant process but it can be done. However, you cannot convert untracked items if they are used in a repeating invoice or repeating bill.
Overall, importing is a very easy and efficient process. I wish that we had a better report of the errors, and I wish that the “help” file described the requirements of the fields as far as the format of the values (such as only needing the account number).