In your source data:
•each transaction must be contained in a single row, and
•each type of data (e.g. details, date etc.) must be contained a single column
If a single transaction has more than one analysis account, i.e. a split analysis, it cannot be imported. You should therefore enter the transaction directly on the Universal Input Sheet (UIS) as shown in Example using the Universal Input Sheet: Split analysis
To import:
1.Open the Universal Input sheet and select Import from: CSV File / Text File and choose the file containing the data, or copy the data from your data source and select Import from: Clipboard.
| (If you are importing from Sage 50, it is best to run the Audit Trail report and export this to a CSV file) |
| You are then presented with the Import dialog, which displays the imported data in its raw format, with a list of options above each column for you to choose the corresponding column of the UIS. |
Screenshot of the Import dialog
2.The import option selected should be General options, unless you are importing transactions from a bank statement file, in which case it should be Bank statement options; to import from a bank statement, please refer to Importing from a bank statement.
3.If the first row of the source data contains the header for the transactions, tick First row of data contains column headers.
4.For each column of data, specify the corresponding column of the UIS, by selecting from the list above each of the columns. If there are other columns in your data that you do not want to import, select [Not imported] from the list above the columns.
5.Select OK. The imported data then appears on the UIS as follows:
Screenshot of data imported to the Universal Input Sheet
6.To convert the transaction type to the equivalent VT Transaction+ (VTT+) type, click on the header of the Type column to select the whole column, right-click to show the pop-up menu and select Convert. This displays the Convert Type dialog which may automatically suggest the VTT+ transaction type depending on the data, or you can set the VTT+ type by selecting Set.
7.To convert the Primary accounts or Analysis accounts of the imported data to the equivalent VTT+ accounts, click on the header of those columns to select the whole column, right-click to show the pop-up menu and select Convert. This displays the Converter dialog which allows you to select each transaction and assign a VTT+ account, by selecting from the list of VTT+ accounts on the left.
| You can also select Auto Assign which may automatically assign the accounts depending on how similar the imported account names are to the VTT+ accounts. If importing from Sage, right-click on the Primary Account or Analysis Account column on the UIS, select Conversion table, then select Sage to VTT+. |
8.Depending on the layout of your imported data, you may need to modify some of the data directly on the UIS, so that it fully matches the format specified in Data format of the Universal Input Sheet. Data can be edited on the UIS in the same manner as a spreadsheet, e.g. you can select a cell or range of cells ,and select Copy, Paste, Clear, Fill Down etc. from the pop-up menu.
9.Click on Post.
Alternatively, rather than importing, you can re-arrange your source data in a spreadsheet to match the layout required by the UIS, and copy and paste the data to the UIS as explained in Method 2: Copying and pasting directly.
Entries with departments
If you have set up departments or lists, you can analyse a transaction to a department/list category in the Analysis account field by appending the department name to the account name with a ':'. For example, the Stationery and printing account, analysed to the Finance department would appear in the format: Expenses: Stationery and printing: Finance:
