As type is CSV (comma delimited) and save the file into the appropriate folder — for example, you could use a ‘CONVERSION-DataReadyForImporting’ folder. (Refer to the section ‘Setting up file directories’, earlier in this chapter, for more.) Microsoft Excel pops up with an alert box to make sure you really want to save the file as a text file. Click Yes to confirm.
Preparing data for import
After you’ve exported your data, and if necessary converted it to a CSV file (refer to the preceding section for some general information on these processes), you’re ready to prepare your data for importing.
Here are some useful techniques for preparing data in Excel for importing into Xero:
Convert dollar values to numbers — dollars values aren’t recognised.
Enter dates in day/month/year format. Users of the US version of Xero enter dates in month/day/year format.
Import amounts as a single column — when data is split between deposits and withdrawals, for example, the columns need to be merged and the withdrawals recognised as a negative number, by prefixing them with a negative symbol.
Remove consumer tax from values (if required). Most information extracted doesn’t include consumer tax — so this hopefully won’t be an issue for you — but may be required if you’re converting from MYOB.
Here’s how you can convert amounts to a single column, using the example of combining deposit and withdrawal columns:
1 Insert an additional column in the Excel spreadsheet, beside the Withdrawal column, and label it Amount.The new column is shown in Figure 3-5.FIGURE 3-5: Combining data into a single cell.
2 Combine the Deposit and Withdrawal column by entering a formula in the Amount column to subtract Withdrawals from Deposits.For example, using the spreadsheet shown in Figure 3-5, you could enter the formula ‘=+C2-D2’ in cell E2, combining data into a single cell. The withdrawal in this example is now represented by a negative number.
Here’s how to calculate the tax exclusive value from the tax inclusive value:
1 Calculate the consumer tax Exclusive Amount by inserting a column beside the Inclusive Amount column and using the formula Inclusive Amount/(Consumer Tax+100)%.Figure 3-6 shows an example of this calculation, including the formula required based on the Excel cells involved. In this example:Inclusive Amount: $500Consumer tax: 10%Exclusive Amount: 500/1.1 = 454.54FIGURE 3-6: Calculating consumer tax Exclusive Amount.
2 Copy the formula to the end of the data list by clicking the small black box at the bottom right of the cell and dragging it down alongside the existing data. Don’t apply the Exclusive Amount calculation where the Selling Price is already exclusive of consumer tax, the consumer tax is 0, or you’re importing bank statement lines.
Mapping tax rates
The tax rates of the raw data need to reflect Xero’s own unique tax rates before importing. Refer to Chapter 2 for more detailed information on tax rates. To update the tax rates:
1 Identify how the tax rates should be mapped from the old system to Xero default tax rates.You may wish to seek specialist advice here.
2 Update using the Find and Replace function within Excel.If you’re selecting Import from MYOB or BankLink, a mapping process is in place for matching the tax rates between the two software packages, so this step is not needed.
Importing Data into Xero
Once you’ve exported and prepared your data from your existing solution, it’s ready for importing. At this point, undertake one last review of the data and/or submit it to responsible parties for final approval. A cup of tea and fresh eyes may highlight issues to be rectified prior to import.
When you’re satisfied, follow the steps outlined in the following sections to import data into Xero.
Chart of accounts
Follow these steps to import your chart of accounts into Xero:
1 From the Xero Dashboard go to Accounting → Advanced → Chart of Accounts → Import.The Import Your Chart of Accounts window opens (see Figure 3-7).
2 Answer the two questions on the window.What system are you importing from? If you’re using a UK, US or global version of Xero, you only have the single option of choosing Xero at this point. If you’re importing from Sage or QuickBooks, or another system, choose Xero. Yup! A little confusing, but if you’ve completed the Xero Chart of Accounts template (refer to the section ‘Downloading Xero templates’, earlier in this chapter), Xero is the option you need to select here. If you’re using an Australian or New Zealand version of Xero, you’re presented with three options here: Xero, MYOB or BankLink. If you’re importing from MYOB, select MYOB. If you’re importing from Sage or QuickBooks, pick Xero. The other option is BankLink, another type of accounting software.Does the file you are importing contain account balances?Select No. It is preferable not to import balances, particularly if you’re importing into an existing Xero file (that is, one you’ve already added some transactions to, rather than a brand new file) because some checks are not repeatable. If your data has balances, don’t worry — Xero will ignore them if you select this option.
3 Click the Browse button to select the required file and then click the blue Import button.For example, you could select the AccountsImport.csv file in the DataReadyForImporting folder within the CONVERSION folder.Clicking the Import button brings up the Confirm Your Imported Accounts window, which provides a report on the impending import process. This is an opportunity to review the integrity of your data prior to import.
4 Review data integrity by clicking the View link beside each report line.You’re provided with an overview of the import, summarising what has been included: New accountsUpdated accounts (the numbering may look odd, because the default Xero accounts will be archived, but don’t stress — continue the process and you will see the new accounts added)System and locked accounts that haven’t been updatedYou’re also given a summary of what has been excluded:Accounts that can’t be imported due to errorAccounts that have been deleted or archivedYou have the option here to print errors and changes; however, the option
