Prepare a CSV or Excel file to import transactions or subledger data

You can import your transactions, general ledger or subledger data into a Cloud engagement from properly formatted CSV or Excel files.

For more information on importing transactions, general ledger or subledger data from a CSV or Excel file into Cloud engagements, see Import the client's transactions and other subledger data from a CSV or Excel file.

When importing general ledger data, certain fields are consistent across lines within an entry. During an import, these fields will be stored separately, being populated with the values from the first entry line within the entry. For more information, see How general ledger data is stored upon import.

CSV file requirements

To ensure successful import:

  • File must be a .csv with UTF-8 encoding.

  • If the CSV file includes column headers with no data (only header values exist), the files are rejected and the upload fails.

  • File size must be less than 5 GB.

  • The first row in the CSV (the header row) is excluded from the import. Transactions should start on the second row.

  • If there is a value with quotation marks ("):
    • Value must have two quotation marks (an opening and closing quote). If a value has one quotation mark, either add another one or remove the quote.

    • Value must be escaped by enclosing it between additional pair of quotes or backslashes (\).

    • Values that consist of more than one word - where one or two of them have quotation marks - must be entirely enclosed between quotation marks.

    • Examples:

      In the data below, the value Definitely "real" cash consists of one word between quotation marks:

      accountId,accountDescription,amount

      123,Definitely "real" cash,1000000

      You must change it so it looks like this:

      accountId,accountDescription,amount

      123,"Definitely ""real"" cash",1000000

      Or like this:

      accountId,accountDescription,amount

      123,"Definitely \"real\" cash",1000000

      In the below data, the value "cash" is between quotation marks:

      accountId,accountDescription,amount

      123,"cash",1000000

      You must change it so it looks like this:

      accountId,accountDescription,amount

      123,""cash"",1000000

      Or like this:

      accountId,accountDescription,amount

      123,\"real\",1000000

Excel file requirements

To ensure a successful import:

  • The file format must be .xlsx.

  • The file must not contain merged cells.

  • Data must exist on the first sheet, as only the first sheet is included in the import.

  • Data must start on the second row, as the first row (the header row) is excluded from the import.

  • At least one column must have data past row one.

  • There must be no empty rows.

Mandatory fields for importing transactions

To import transactions for viewing or performing sample section, you must ensure the following fields are included in the import data:

  • Posting Date
  • Account Identifier
  • Amount

See also How general ledger data is stored upon import.

Mandatory fields for data analytics

If data analytics is enabled in your product, you must ensure the following fields are included in the transactions import data to ensure the analysis tests function properly:

Test name Required field
Complex Account Account ID
Duplicate Entries
  • Posting Date (effective date)
  • Account ID
  • Amount
Ends in 999
  • Account ID
  • Amount
High Amounts
  • Account ID
  • Amount
Missing Description
  • Detail Comment
  • Account ID
Rounded Amount
  • Account ID
  • Amount
Same Use Entering and Approving
  • Account ID
  • Entered By
  • Entry Responsible Person
Sequence Gaps
  • Entry Number
  • Account ID
Specific Keywords
  • Detail Comment
  • Account ID
2-Digit Benford's
  • Account ID
  • Amount
Unusual Account Combinations Account ID
Unusual Days
  • Posting Date (effective date)
  • Account ID
Unusual Times
  • Entered Date
  • Account ID
Unusual Users
  • Document Type
  • Account ID
  • Entered By
Zero Amounts
  • Account ID
  • Amount

Mandatory fields for Envision

If Envision is enabled in your product, you must ensure the following fields in included in the transactions import data:

Data visualization diagram Required fields
Overview
  • Document Type
  • Posting Date (effective date)
  • Amount
  • Entered By
Summary Amount
Distribution
  • Document Type
  • Amount
  • Entered By
Posted Date Distribution
  • Posting Date (effective date)
  • Amount