Import the client's transactions and other subledger data from a CSV or Excel file

You can import the client’s transaction and other subledger data after importing the trial balance data.

Note that:

Upload the file

To import your data, you must first upload the file and then assign the data fields.

To upload the CSV or Excel file:

  1. From the Import tab of the Data page, select Accounting Software or Structured File.

  2. If this is the first time you’re importing data into the engagement, select New Data Source. If you’ve previously imported data, select Add data.

  3. Select CSV File Import or Excel File Import from the list of data sources, then select Next.

    If the option is not visible, you can use the search box to find it.

  4. In the Add new dataset dialog, browse or drag the CSV file to the file upload area.

  5. If you uploaded a CSV file, the Select Delimiter dialog displays. Select the Delimiter used in the file, and then select Continue.

Assign data fields

After you upload the file, you need to assign the data fields in the file to predefined target fields in the dataset to complete the import.

This ensures analytics tests can be run successfully and that your general ledger data displays correctly in the Transactions tab of the Data page.

For example, when the data field in the CSV file "nr" is assigned to "Entry Number", the values of the column will display in the Entry Number column in the Transactions tab. Similarly, the values for "cw_js" will display in the Document Type column, and so on.

Transaction file tagging example

To assign the data fields:

  1. Select Configure.

  2. From the Dataset type drop-down, select the type of data you are importing and then select Next.

    To learn more about the dataset options, see Dataset types. Not all dataset types may be available if they have not been enabled in your product.

  3. On the Assign Fields page, you can:

    1. Use the drop-downs to manually assign a field to each column in the imported file

    2. Use the Select Data Layout drop-down to apply a previously saved data layout and automatically assign the fields. To learn more, see Save a data layout.

    Tip: Move your cursor to a specific field to view a description of it to help you assign the values correctly. You can also find specific target fields using the Search field at the top of the drop-down menu.

  4. If value mapping is required for a specific field, the Map column values drop-down appears at that field.

    1. Select the Map column values drop-down.

    2. Specify your selections, then click outside of the drop-down menu when you're done.

      The following figure shows an example of field value mapping for DOCUMENT TYPE. Note that DOCUMENT TYPE has been mapped to the cw_js field in the source CSV file. In the process of value mapping, the target value General Ledger Entries (GJ) is mapped to GJ from the CSV file.

  5. When you have finished assigning the fields, select Done.

If you want to reassign the fields later, you can select Options () | Reassign fields on the Datasets page.

If you imported general ledger data, you can view the data in the Transactions tab of the Data page. To learn more, see View the client's transactions.

Note: Any other type of subledger data cannot currently be viewed in the Data page but can be used in analytics tests.

Dataset types

Dataset type Description
General ledger General ledger transactions
Trial balance A worksheet in which all ledgers are compiled into debit and credit columns in order to ensure a company's bookkeeping system is mathematically correct.
Accounts payable - invoices

The Accounts payable—invoices dataset contains all invoices received during the period under review.

Accounts payable - payments made

The Accounts payable—payments Made dataset contains all payment transactions (check, wire transfer, cash, and so on) made during the period.

Accounts payable - suppliers

The Accounts payable—suppliers dataset contains all details related to the Supplier such as Contact details, address, credit limit and so on.

Accounts payable - open balances

The Open accounts payable dataset contains details regarding all open, unpaid, or unresolved payable transactions as of the close of business on a specified date.

Accounts receivable - invoices

The Accounts receivable—invoice dataset contains all invoices generated during the period under review.

Accounts receivable - cash received

The Accounts receivables—cash received dataset contains all payment transactions (check, wire transfer, cash, and so on) received during the period.

Accounts receivable - customers The Accounts receivable—customers dataset contains all details related to the customer, such as contact details, address, credit limit and so on.
Accounts receivable - open balances The Accounts receivable—open balances dataset contains details regarding all open, unpaid, or unresolved customer transactions as of the close of business on a specified date.
Inventory transactions Captures all transactions affecting inventory accounts during the time period specified (for example, receipts, shipments, transfers, returns, and adjustments).
Inventory on hand Captures the on-hand inventory quantities of items by location and amounts as of the specified date.
Other Unstructured transaction data that does not follow a predefined schema. Select this option to import data that does not correspond to any of the dataset types described here. Each file you import using this dataset type is considered its own dataset.