Import client's financial data from a CSV file for analysis

Import client's financial data from a CSV file for analysis

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.
  • comments.

5. Click File | Save As and choose: CSV (Comma delimited) as the Save as type.

Preparing a CSV file.

6. Unicode (UTF-8) as the Encoding (under Tools | Web Options at the bottom of the dialog).

Updating web options.

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

An overview of your uploaded CSV file appears in the Profile page

    • Related Articles

    • Common import errors in CaseWare Cloud analytics

      Problem When you attempt to import your file, you receive a prompt that the file is invalid. Solution Analytics only accepts .csv files and valid Transformer .zip files. If your client data is in a file type Analytics does not currently support (such ...
    • Detecting misstatements in your file with CaseWare Cloud Analytics

      Analytics offers several tests specifically designed to look for misstatements, errors, or possible fraud in your client file to help assess risk and ensure that reported information conforms with accounting standards. These tests produce reports, ...
    • Importing from desktop accounting software

      You can import to Analytics from a wide variety of desktop accounting packages. You can import client data for analysis from a number of desktop accounting suites. Downloading CaseWare Transformer First, you'll first need to download the CaseWare ...
    • Importing from online accounting software

      We have begun to integrate Analytics with online accounting platforms, enabling you to upload data directly from these services without having to leave your browser or download the engagement files to your hard drive. These operations do not require ...
    • Import from MYOB Account Right Live

      The Cloud Import Tool doesn't have a taskbar icon, so if the application is hidden in the background, please use Alt-Tab to switch to the tool. The CaseWare Desktop Import Tool now supports the ability to import from MYOB Account Right Live through ...