Import invoices, bills and credit notes from another accounting system into Xero using Xero's CSV template. You can do this during your Xero setup process, and also as a regular business process once you're up and running on Xero.
To import invoices or bills into Xero go to either Accounts > Sales or Accounts > Purchases and click on Import. All users who have access to invoices or bills can import.
About importing invoices and bills
Invoice or bill data imported into Xero is treated in the same way as if you'd manually entered it into Xero - you're using the CSV as a way to quickly import the data into the invoice or bill fields.
You'll need to import invoices and bills in separate batches.
To import credit notes, add them as invoices or bills with negative amounts.
Your invoices, bills and credit notes come into Xero as drafts, for you to review and approve as normal.
IF you have the multi-currency option, you can import invoices, bills and credit notes in foreign currency.
You can't import payslips into Xero. To create payslips and bills associated with payroll, you'll need to do so within your pay run.
Import sales invoices or bills to match conversion account balance
Follow these steps if you are importing invoices or bills as part of your conversion process. If you are importing as part of your normal business in Xero, go to the next section:
Create & import your invoice or bill file
You can only import sales invoices or bills into Xero after setup is complete.
- In Xero, go to Sales and Purchases to import your invoices or bills as described below, and approve them. The dates for all these items should be before your Conversion Date.
- When all items are approved (have a status of Awaiting Payment) the totals of these items on the Awaiting Payment tabs in Sales and Purchases should match the balances you have from your previous system. Enter these totals on the Conversion Balance screen as the Accounts Receivable (Sales) and Accounts Payable (Purchases) balances and save the balances.
- Enter the lock date to be the date of your conversion date so that nothing is accidentally entered before this date.
The invoices or bills in Xero may have slightly different totals to ones you've issued your customers before using Xero. You can use a reconciliation adjustment to reconcile the payment received in your bank account when that payment is different to the invoice or bill in Xero.
Create & import your invoice or bill file
From any tab in Sales or Purchases, choose the Import button to go to the Import sales invoices or bills screen. The import process is the same for invoices and bills but you must import them from the relevant section in Xero.
If you want to start with Xero's blank template or reformat your invoices or bills exported out of an existing system to match the template, you'll need to go to the Import invoices or bills screen initially to get the template.
A CSV file is a document that is formatted with Comma Separated Values. The most common software program used to create or edit a CSV file is Microsoft Excel although many other programs will let you save a file in CSV format i.e. somefilename.csv. Xero will also accept comma separated values in a text-based file (created by a program like Notepad or Text Edit) which when saved will have a name like somefilename.txt. You need to save your file in the CSV format, and not just rename a file so that it contains '.csv' or '.txt'. As long as you follow some specific rules below, Xero will understand the data you put into your CSV file and will know how to create your invoices or bills from it.
Invoices and bills in foreign currency
- If you have the multi-currency option in your pricing plan, you can import invoices, bills and credit notes in currencies other than your base currency.
Change your Xero pricing plan
- You must add all required currencies in your Settings before you can import bills or invoices in those currencies.
Foreign currency settings & options
- If the invoice or bill is in a different currency from your base currency, in the Currency column in the import template enter the three letter currency code used by Xero in capital letters. For example, for an invoice denominated in Indian rupees, enter INR in the Currency column.
- If the invoice or bill is in your base currency, you can either enter the currency code or leave the Currency column blank.
- You can only enter one currency per invoice or bill.
Use the Xero Template
Sales invoice or bill data needs to be imported into Xero in the CSV format from a .csv or .txt file using a template that Xero can use to map values in your file to the relevant invoice or bill fields in Xero.
To open the blank template containing the headings under which you need to enter your invoice or bill details, go to Accounts > Sales or Purchases > Import, then click the Download template file link.
Depending on how your computer is set up, the CSV template provided by Xero will most likely open in Microsoft Excel.
Only simple invoice data can be imported into Xero. You can't import any extra fields, formatting, currency symbols, or attachments. The column headings in the CSV file must match the ones that Xero expects. Otherwise, the file won�t import. So you shouldn�t add or delete columns, or change the headings. If the data is wrong on an invoice and Xero can't interpret the invoice field information, the invoice won't import. You can see any errors that occurred with the import on the Import Summary screen.
Enter invoice or bill data
Enter or copy your sales invoice or bill data into the CSV file
Whether you started with the blank Xero template or got a file of your sales invoice or bill data from another system, enter or change the data in the columns provided in the CSV file.
The Xero template contains column headings under which you should enter or copy your invoice or bill data. The CSV file contains all the invoice or bill fields in the Xero database. Some of these are editable in Xero and some not. This means that there are columns in the CSV file that you should not use. Just enter data into the columns that you would normally use if you were entering an invoice or a bill in Xero. To create credit notes, enter negative amounts.
If you're not familiar with the Xero invoice or bill entry screen, you might like to take a look at one first so that the columns make some sense. The CSV file essentially 'maps' columns to invoice or bill fields in Xero so by completing the information column by column, you're essentially 'filling in' the fields in Xero automatically. The columns to use and rules for the data entered into them are explained below.
If you're not familiar working with CSV files, you'll probably find it easier to use Microsoft Excel because you can work with your data in columns rather than in a straight line separated by commas as with a text editing program.
Columns & rules for entering invoice or bill data
Enter your invoice or bill data into the CSV file, one invoice or bill per row, under the column headings provided (one column for each field on the invoice or bill) - or one invoice or bill line item per row if it has multiple line items (below). The header row is mandatory and some columns may be deleted if not used.
If you're working with a CSV file from another source and have opened it in Microsoft Excel, make sure the comma separated data is spread across the columns under each relevant heading rather than all data displaying in the first column of the spreadsheet. Sometimes this happens by default if you open the CSV using Microsoft Excel. To ensure the data displays correctly, you might need to open Microsoft Excel first then use the import feature in Microsoft Excel to import the CSV data. If all your data appears as comma separated in the first column of the spreadsheet it will fail to import into Xero.
Convert TXT data into columns
Once imported, data will be entered and saved as a draft invoice or bill. You'll need to check and enter mandatory data in the draft invoice or bill before you can approve it. In some cases you might be able to import data into Xero that isn't valid when it comes time to approve the invoice or bill e.g. the date entered is in a locked period or a field has been left blank. Your CSV needs to comply with these same rules as if entering a draft invoice or bill directly in Xero. Refer to the help for fields and validation when entering an invoice or bill in Xero normally - Add a sales invoice or Add a bill.
Using the following columns on the CSV template file for your sales invoice or bill data
Mandatory fields are marked with an asterisk (*).
Template file heading
Invoice or bill field in Xero
|*ContactName||To or From||Required field for import.|
If the customer or supplier doesn't already exist (with this name) in Xero they will be created as a new Contact.
|EmailAddress||Email address||You can update your contacts' email addresses. Xero will override any existing addresses.|
The addresses are included in your contacts' records but don't appear on invoices or bills.
|Street or PO Box address lines, town/city, state/region, postal or zip code and country fields||You can update your contacts' address details. Xero will override any existing addresses.|
The addresses appear on approved invoices and bills, but not on draft invoices or bills.
|*InvoiceNumber||Invoice # (Sales)|
|Required field for import.|
This is the number on sales invoices and it must be unique. There is no invoice number field on bills, so the number entered will be displayed in the Reference field.
Using invoice numbers
Import invoices or bills with multiple lines
|Reference (Sales CSV only)||Reference (Sales)||Sales invoices can have an additional reference number. (This column does not display on the Purchases CSV template downloaded.)|
|*InvoiceDate||Date||Required field for import.|
Must be entered as dd/mm/yyyy or entered in full.
|*DueDate||Due Date||Required field for import.|
Must be entered as dd/mm/yyyy or entered in full.
|Total||Used for checking your transaction totals.|
This field is typically for UK customers only. It doesn't display on the Xero invoice or bill and shouldn't be entered in the CSV file.
Tax and price calculations
|Inventory Item Code||Item||Inventory item codes that you want to import.|
Tips for a successful import
|*Description||Description||Required field for import.|
Description for the sales invoice or bill, or for the line item on it if more than one item.
|Quantity||Qty||The number of items for the sales invoice or bill, or for the line item on it if more than one item.|
Xero imports the identical quantity as your entries. For example, if you enter '0', the quantity shows '0'. If you leave the field blank, the quantity field is empty.
If you remove this column from your CSV file, Xero enters '1' for the quantity.
|*UnitAmount||Unit Price||Required field for import.|
The price for the sales invoice or bill, or for the line item on it if more than one item.
If you choose to import tax exclusive unit amounts, Xero uses the unit amount with the quantity and tax rate to calculate the overall total for you (there's no need to enter the total on the CSV).
Tax and price calculations
If you choose to import tax inclusive unit amounts, tax is calculated for you based on the quantity, tax rate and any discounts.
Special currency formats are expected for organisations based in France or Germany.
|Discount (Sales CSV only)||Disc %||The discount percentage you want to apply to the sales invoice line item. Enter the number to 2 d.p. without the % sign.|
|*AccountCode||Account||Required field for import.|
The account in your Chart of Accounts you want the sales invoice or bill, or line item if more than one item, to be coded to. Enter the Chart of Accounts code only e.g. 200 - Sales, enter 200 only.
|*TaxType||Tax Rate||Required field for import.|
The sales tax rate to be applied to the invoice or bill, or line item if more than one item. Enter the name of the tax rate as it is set up in Xero, whether it is a default rate or one you have set up yourself. If the tax rate name contains words and a percentage, enter it in full as it appears in Xero e.g. 20% (VAT on Expenses).
Importing account, tax and tracking information
Use this to make a tax adjustment on your sales invoice or bill. As tax amounts can't be manually set on a tax inclusive import, use a tax exclusive import for tax adjustments.Tax and price calculations
|TrackingName1||[Category name]||The name of the first (or only) tracking category as it appears on the sales invoice or bill in Xero.|
|TrackingOption1||[Category option]||The name of one of the options that belong to the first tracking category.|
|TrackingName2||[Category name]||The name of the second tracking category as it appears on the sales invoice or bill in Xero.|
|TrackingOption2||[Category option]||The name of one of the options that belong to the second tracking category.|
Tax and price calculations
|Currency||Currency||The three letter currency code if the amount is not in your base currency.|
Entering foreign currency invoices and bills
|Branding Theme||Branding||The branding theme you want to apply to each of your sales invoices. (This field isn't used for bills.)|
Tips for ensuring a successful import:
- You must leave the first row in place as the header row and all mandatory columns must be present on the template file. If you're copying data from another system, make sure you use these same column headings. If you delete the header row, Xero will not import your file.
- You can delete any columns that aren't mandatory although there's no harm in leaving them empty.
- If you are using Microsoft Excel to create your CSV file take care not to enter figures and symbols which could be automatically reconfigured by Microsoft Excel to read as dates or numbers or some other formula as this may prevent the file from being successfully imported.
- Special currency formats are required for organisations based in France or Germany. If the following formats are not used, amounts may import incorrectly, or your import may fail:
- France: 1100,00
- Germany: 1.000,00
- This applies to inventory item formats too. This is based on your location in Organisation Settings, not on the currency for each invoice or bill (if you have multi-currency). In all other regions the decimal place is marked with '.' (a period) as usual, and a comma will not be accepted.
Contact details (customer or supplier names)
- The rules Xero uses for determining whether the Contact (customer or supplier)already exists in Xero are the same as if you entered the invoice or bill manually in Xero i.e. if the name isn't spelled the same or doesn't already exist, Xero will assume it's a new Contact.
- You don't have to have Contacts set up in Xero first, but if you do, and you want to retain the names you have already used, make sure the Contact names on your CSV are the same as those already in Xero.
- If the email and street/PO Box fields are left blank, Xero will assume you want to leave the corresponding fields as they are (i.e. retain any existing information already in Xero for this Contact).
- Sales invoices in Xero have an automatic number sequence. The invoices you import don't have to use the same numbering sequence and won't affect the invoice number sequence in Xero (whether you've used it for invoices or not) unless invoice numbers on the CSV are the same as those already used by invoices in Xero.
- If you want to keep the same invoice number sequence on the CSV, make sure you enter the invoice number in the same format as it is in Xero e.g. INV-0001 (or whatever combination of letters and/or numbers you've set up).
- Sales invoice numbers can't be used on the CSV if an invoice with the same number already exists in Xero. You will be prompted on the import summary screenif an invoice will fail to import because it has the same invoice number as an invoice already in Xero so that you have a chance to change it. Only if an invoice has been deleted, can you use the same number again.
- Bills do not have a mandatory number in Xero, but if you want to include a bill number as a reference, enter it in the InvoiceNumber column on the CSV. The number will be put into the Reference field on the draft bill in Xero. The number must be unique within the CSV but you can remove it from the draft bill or change it as required after the import.
- All invoice numbers or bill reference numbers used on the CSV must be unique because this is what distinguishes one invoice from the next - this is especially important if you want to import invoices or bills that have more than one line item. Each line item is given the same number in order to identify it as being part of the same transaction.
Invoice date and Due date
- For bills, use the 'Invoice date' column for the date of the bill.
- Formats must be dd/mm/yyyy or entered in full. Double-check these because your regionalised computer settings may override the date formats. If you find this happens, you'll need to reformat the date cells in Microsoft Excel.
- Xero doesn't apply default due dates (in other words, payment terms for the organisation or individual contacts) to invoices or bills you import. If you want to import due dates, enter these into the import template.
- If you import an inventory item code that isn't in Xero, a new inventory item code will be created. Inventory in Xero
- If you have not set up tracked inventory items in Xero, all inventory items are untracked by default. If needed, you can mark them as tracked later.
Chart of Accounts, Tax & Tracking
- Enter only accounts in the Chart of Accounts, Tax Rates and Tracking options and categories that already exist in Xero. These need to be spelled and formatted the same as in Xero, otherwise your file will not import. The exception to this is the Tax Rate - if you enter the tax rate name incorrectly or enter a rate that doesn't exist in Xero, the field on the invoice or bill will come through as blank.
- If a tax rate you're using is made up of several components in Xero, you don't need to worry about this on the CSV. Just enter the overall tax rate name in the TaxType field and Xero will calculate the correct tax amounts for each component percentage when the invoice or bill is approved, and record these amounts against the relevant component and tax rate in your reports.
Tax amounts & totals
- Invoice, credit note or bill totals - if you chose to import unit amounts as tax exclusive, Xero will calculate the overall invoice or bill total for you based on the UnitAmount and TaxType entered on your CSV. You should review and adjust your figures as necessary on the draft invoice or bill once it's imported to make sure the final total and tax calculation is correct. This is important so that you can ensure the right tax rate has been used and tax adjustments (see below) have applied or removed as necessary.
- Tax exclusive unit amounts are calculated on each line of an invoice or bill in Xero behind the scenes and displayed as a total tax amount for each tax rate used in the subtotal for the invoice or bill. This means that sometimes, often because of multiple lines or tax rates, that the final invoice or bill amount calculated by Xero and displayed on the draft might be slightly different to the total on your original one. You can edit the invoice or bill or leave a small amount as rounding when it displays as such on it.
- The Tax Amount field can be used for tax adjustments - you can force a particular tax amount for your line item that is different to the tax that will be calculated by Xero based on the tax rate you've entered in the TaxType column. This is mostly for those in the UK who want to apply a VAT adjustment on an invoice or bill. Make sure you've completed both the TaxType field and your specific amount of tax - Xero will display the difference in a tax adjustments box on the invoice or bill once imported. The tax adjustments box will appear on any invoice or bill where an amount of tax has been entered in the TaxAmount column that doesn't match Xero's calculation. If your country doesn't allow the adjustment of tax on an invoice or bill you shouldn't use this column. However, if an adjustment amount does make its way onto your invoice or bill, you can edit it to remove it (enter 0.00 in the adjustments box to revert the tax amount to match the tax rate).
Invoices or bills with more than one line item
You can use the invoice number on your CSV to group several rows together to be a multi-line invoice or bill in Xero. All line items for the same invoice or bill must have the same invoice number and currency (if you have the multi-currency option enabled).
In the first row, enter the key information from the invoice or bill. On the lines underneath, complete only those fields relevant to each line item. There is no need to repeat the contact name each time.
Locate and select the file to import
Click the Browse button to locate the CSV or TXT file you want to import into Xero.
Browse your computer's file system to find the CSV file you've saved on your computer.
- Make sure you have saved the file you want to import.
- Xero will only accept a file with the suffix .csv or .txt.
- You can import a large number of sales invoices or bills at once (as long as the overall file size is less than 2MB) however for speed purposes we suggest that you import no more than 500 at one time. If you have more than this you can just keep on importing files of invoices or bill in groups of 500 and these will be added to those already imported into Xero.
Click the Update button to bring the invoice or bill data from the file into Xero where you will be asked to Confirm the import before finally importing the data as invoices or bills into Xero.
Click the Cancel button if you want to stop the import process for now.
Confirm the import
Review the sales invoice or bill data to be imported
Once you have created your invoice or bill file and selected it as a .csv or .txt to import into Xero, you can review what's going to be imported before finally completing the process.
- As long as you have followed the guidelines for entering invoices or bills on the CSV, you'll see that Xero will import the same number of invoices or bills into Xero as you have on your CSV.
- If there is something wrong with the structure of your file i.e. missing or incorrectly labelled columns, missing header row, incorrect format, line totals not correct (only if you've used them) Xero will not import the file.
- If individual invoices or bills on your CSV cannot be imported, the reasons why will be listed so that you can stop the import process to fix your CSV and try again.
- Use the line item numbers given in the list of errors to identify where, on your CSV, the invoices or bills that need to be fixed are.
- In Microsoft Excel the line numbers correspond to the row number.
- If you're working with a text (TXT) file in the Notepad program, line numbers will be displayed if you have the program set up to show the status bar (do this from the 'View' menu).
- If you want to address the errors on your CSV file, use the Go Back button to stop the import process to make corrections on your CSV before importing it again.
- If you want to proceed without importing the problem invoices or bills, click theComplete Import button. You can manually add the missing invoices or bills in Xero or import another CSV containing those that weren't imported.
View & process the imported sales invoices or bills
Once imported, your sales invoices and bills will display on the Sales or Purchases Draft tab depending on the section of Xero you were in when you chose to import. From there you can process the draft invoices or bills in the normal way.