Formatting your client data in .csv format
The following procedure uses Microsoft Excel to prepare the .csv file, but you can use any editing tool to produce the import file.
Note: Journal and ledger data should conform to the following guidelines:
- Each row must represent either a debit or a credit, meaning each transaction takes up at least two rows.
- No more than one transaction ID column.
- No more than one amount column, with debits as negative amounts and credits as positive amounts.
If your files do not conform to this formatting, you can adjust them later.
To format a .csv file for import into Analytics:
1. In your accounting software, export your client file in an editable format (such as .txt, .xls or .csv).
2. Open the file in Excel.
3. Select each blank row in your file. Right-click on any blank row and select Delete.... In the Delete dialog, select Entire row, then click OK.
4. Delete all rows containing:
- totals or subtotals.
5. Click File | Save As and choose: CSV (Comma delimited) as the Save as type.
6. Unicode (UTF-8) as the Encoding (under Tools | Web Options at the bottom of the dialog).
7. Enter a filename and click Save.
The client data can now be imported into your Analytics engagement.
Importing .csv files into Analytics
Import your formatted .csv client file into your Analytics engagement. During this process, you will have the option to map the columns of your import file to the data columns in Analytics.
To import a .csv file into Analytics:
1. Ensure that you have the Editor role, or equivalent privileges.
2. From the Cloud menu, select Analytics.
3. Select the Analytics engagement, then select Add Data Files.
4. Select the appropriate option. After you select a file, the File Import dialog opens.
5. Select New File: Upload a file from your system.
6. Select Existing File: Select a file from the Files page in Cloud.
You can map your file from the File Import dialog, or wait until after the import is complete. You will need to map the file before you can perform most Analytics tests.
To map the file from this dialog:
1. Enter a description of the file in the Description field.
2. Select the predefined Analytics Type that best matches the file. The Type determines which Column meanings will be available to select from. If none of the Types are a good match, select Unassigned.
3. If your file has a header row, select the Contains Header checkbox and specify its row number under Specify row containing column names. This is set to 1 by default. Do the same for the First row of data, which is set to 2 by default.
4. For each column, select whether you want to include it in the import by selecting or deselecting Ignore selected column.
5. For each column, specify a Column name, Column type, and Column meaning that best match the data. If no available meanings are appropriate or you're unsure which meaning to assign, leave the column Unmapped. True/False data can be imported, but not mapped.
6. Select Save.
When the upload is complete, the data file will appear in black in the Data Files pane. If you mapped the file, a summary of its data will appear in the Profile page.