Xero For Dummies. Heather Smith

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 file by selecting Save As. Clearly identify the file — for example, by renaming it <FILE>Import.csv. Ensure the Save 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.

      

Microsoft Excel is perhaps a little overzealous about making sure you’ve saved your changes before you close the file, and asks if you want to save the file before closing. If you’re positive you’ve saved it in the format required (CSV), and haven’t made any further changes, you can simply click Don’t Save!

      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