Summary
Learn why a general ledger must contain at least 1 numeric column.
What is a numeric column?
An Amount column, or Debit and Credit columns, are the numeric columns among the fields required by MindBridge for general ledger files. Without a numeric column, MindBridge cannot perform an analysis.
- If Amount is used, all debits must be positive and credits negative.
- The amounts present in the column must also satisfy the following:
- Numeric characters (numbers), and symbols . (period), - (negative sign)
- Period (.) character used to indicate decimal (i.e., not commas as is the convention in some jurisdictions)
- A - (minus/negative) sign in front of the number to indicate it is negative (other conventions such as (), <>, or - after the number, are not supported)
- All other kinds of characters must be removed (for example, currency symbols, spaces, letters, and preferably commas as well).
- If Debit and Credit columns are used, the values in both must have default positive balance.
- MindBridge can ingest amounts that have commas as thousands separators, but it is safer to remove the commas.
If you already have numeric Amount or Debit/Credit columns satisfying the above requirements, please check your GL file for the following potential issues:
- Extraneous data: MindBridge expects general ledger files to have a header row as the first row and then transactions in the rest of the file, with each transaction represented by one and only one row. Subheaders, total, and subtotal rows should be removed. The Amount, Debit, or Credit columns might have non-numeric values if they are used as text columns in subheader, total, or subtotal rows.
- Corrupt data or problematic data: Includes shifted cells, fields spanning multiple columns, or multiple fields per column.
- Sometimes values in certain rows are shifted one or more columns to the right or left of the column they belong to, usually due to improper escaping of values that contain " or \ or ' characters or the file's delimiter character.
- In the following example, column V, DESCRIPTION, which is the column to the left of the DEBIT column (column W), splits into two in rows 277100 and 277101. Half of the DESCRIPTION falls into the DEBIT column and then the DEBIT amount falls into the TRANSACTION column (column X) to the right of the DEBIT column:
- In the following example, column V, DESCRIPTION, which is the column to the left of the DEBIT column (column W), splits into two in rows 277100 and 277101. Half of the DESCRIPTION falls into the DEBIT column and then the DEBIT amount falls into the TRANSACTION column (column X) to the right of the DEBIT column:
- Sometimes fields do not fit perfectly into a column. This can happen if you try to import into Excel a fixed width text file that is not perfectly fixed width, so the first few digits of the number is in one column and the rest in the next, sometimes followed by part of the text that should be in another column. This also often happens when converting from PDF files. See this article for more on why MindBridge does not support PDF files.
- Sometimes values in certain rows are shifted one or more columns to the right or left of the column they belong to, usually due to improper escaping of values that contain " or \ or ' characters or the file's delimiter character.
- Each column is must contain one and only one field: If there are multiple fields per column, such as the amount followed by a space followed by a debit/credit indicator, they would need to be split into two columns. For example "4500 D" would need to have 4500 in one column and D in another.
Anything else on your mind? Chat with us or submit a request for further assistance.