Xero For Dummies. Heather Smith

Скачать книгу

→ 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.

      Converting exported CSV files into Excel

      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.

      Understanding the CSV file format

      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.

      Using the Excel Import Wizard to convert text files to CSV files

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).

Once you have the data in Excel, you can manipulate the way it’s presented so you can see everything more clearly. Simply select all the data in the spreadsheet by hovering over the top left corner block and clicking it. With the data range still highlighted, resize the columns by hovering over the column border and double clicking the double-sided arrow (see Figure 3-4). The columns and rows resize to fit the data contained within them. Now you can easily see and edit your data.

      FIGURE 3-4: Resize Excel spreadsheet columns.

Once satisfied with your data, save the

Скачать книгу