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	Pflichtfeld
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
Überblick	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