Heather Smith - Xero For Dummies

I tend not to add spaces to folder names because sometimes the spaces mean the folder names don’t work across platforms, and these procedures are to be applied universally. If you prefer to add spaces or use different names, feel free to; however, I have used space-free examples throughout this chapter.

Keeping a copy of the original and converted data files can be useful if you need to review data at a later date.

Xero has a number of downloadable templates to assist with converting your files. By entering data from the old accounting system into these templates, and matching unaltered column headings, you can organise your data in a format that’s easily imported into Xero.

Access the Xero downloadable templates by following these steps:

1 From the Xero Dashboard, click on the relevant directions for the data you need to download, as provided in Table 3-2 .For example, to access the Xero Contacts template from the Xero Dashboard, click on Contacts → All Contacts.

2 Click the blue Import button for the required template and then on the Download Template File link. TABLE 3-2Downloadable Template InformationDataDirectionsCSV File NameChart of AccountsAccounting → Advanced → Chart of Accounts → Import → Chart of Accounts CSV fileChartOfAcountsContactsContacts → All Contacts → Import → Download template fileContactsBank StatementsDashboard → three dot menu (on right of bank account name) → Import a Statement → Download Our CSV templateStatementImportTemplateInventoryBusiness → Products and Services → Import → Items → Download template fileInventoryItemsSalesBusiness → Invoices → Import → Download template fileSalesInvoiceTemplatePurchasesBusiness → Bills to Pay → Import → Download template fileBillTemplateManual JournalsAccounting → Advanced → Manual Journals → Import → Download template fileManualJournalFixed AssetsAccounting → Advanced → Fixed Assets → Download template file *FixedAssets *Before being able to download the template for fixed assets, you will need to set up a Start Date for your register.

3 Save the template to your file directory.For example, you could save the template within your ‘XeroTemplates’ folder within the ‘CONVERSION-BusinessName’ folder.

4 Repeat the three preceding steps until you’ve downloaded all required templates.

As well as using the downloadable template for bank statements, you can create your own Excel template for importing bank statement information. Before doing this, however, see Chapter 4, which covers exporting bank statement information from your actual bank account. To create a bank statement template, list the following bank statement fields at the top of an Excel spreadsheet as column headers:

Transaction Date

Transaction Amount

Payee

Description

Reference

Cheque No.

Note: Only Transaction Date and Transaction Amount from the preceding list are mandatory for the import, but the other fields help with reconciliation.

Populate the Excel spreadsheet with the relevant data and save the file in a CSV (comma delimited, for PC users) format.

On the CSV downloaded templates, the asterisks (*) at the left of the column header name indicate the field is mandatory.

When preparing the data for importing, sit your two Excel files side by side and carefully copy the data from the original file into the Xero template file, to ensure you’re copying data across to the correct fields.

Don’t ever change the header row of the template file — it must remain unchanged for fields to align correctly during importing. You can change the header row in your bank statement imports (and map these changes to the field you want), but I recommend leaving everything as is here as well, for simplicity and consistency with the other imports.

When working with data from different software solutions such as accounting programs, understanding and being able to navigate and manipulate CSV file formats is useful, because this is a common file format. You can then import the CSV file into a spreadsheet program such as Excel, making data manipulation easier.

CSV is an initialism for comma-separated values . CSV is a simple file format where data values are separated by commas — similar to in a spreadsheet, where data is separated into columns. Most programs (such as MYOB, Sage and QuickBooks) allow you to import and export files in CSV format. The CSV files can then be easily edited and, if fields are correctly matched, can be imported into Xero.

CSV files are deceptive! They look like Excel files. Check as you save them that you’re saving to .csv format, not .xls format.

When extracting data from an existing system, extracting it in Excel or CSV format is desirable. If that’s not an option, you may be able to opt to extract files in text file format. When a text file is then opened in Microsoft Excel, the Excel Import Wizard automatically activates and takes you through an extraction process to show data in Excel.

To view the text files in spreadsheet format, follow these steps:

1 Open Microsoft Excel and go to File → Open to search in your file directory of saved files.The file directory is shown in Figure 3-1. As shown at the bottom right of Figure 3-1, select the Text Files option from the drop-down menu to access the text files. (If the Text Files option isn’t selected, the files don’t display, even if they are in the folder.)

2 Select the required file and click Open.The Text Import Wizard window (Step 1 of 3) appears (shown in Figure 3-2).Did your heart just skip a beat?! Don’t be terrified! The Text Import Wizard looks a lot scarier than it really is.

3 Click Next.The Text Import Wizard window (Step 2 of 3) appears (shown in Figure 3-3).

4 Check the Comma box.

5 Click Next and then Finish.Voila! Your Excel spreadsheet is populated with data that hopefully makes some sense to you!

FIGURE 3-1:Using the file directory to open a text file in Excel.

FIGURE 3-2:Text Import Wizard window (Step 1 of 3).

FIGURE 3-3:Text Import Wizard window (Step 2 of 3).