Questions? We have answers.

Data Validation: Consistent number of columns

Article author
Kate H
  • Updated

Summary

MindBridge expects every row of a general ledger file to have the same columns appearing in the same order, and therefore have the same number of columns. The first row in the file is expected to be the header row that lists the name of each column in the file that applies to each row. To help detect cases of general ledger files not conforming to this requirement, our data validator checks if each row has the same number of columns.

Learn why a file might not have a uniform number of columns, and how to fix it.


Symptom

The data contains an inconsistent number of columns, and an error appears on the Data Validation step of the import process.


Resolution

Inconsistent format

The columns change within the file. This can happen if:

  • Multiple files, such as for different months or periods, not in the exact same format were combined (before combining multiple files and/or Excel worksheets, each file/worksheet must have the exact same columns in the exact same order); or
  • If the conversion or ingestion is attempted of an unsupported file type (for example, PDF or fixed width text).

Improper escaping

If certain special characters, such as " \ " and the file's delimiter character (for example, the comma [,] for CSV files), are present in field values, they must be escaped in order to preserve the integrity of the data, or else the system will not be able to properly read where columns begin and end. See Data Validation: Special characters that must be escaped for more information.

Shifted columns

Sometimes some 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 or inconsistent format (see above).

  • 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:mceclip1.png

Split/Broken lines

MindBridge expects there to be one and only one transaction per row (no broken lines; next transaction begins on the next line). However, sometimes an entry splits into two or more lines. This often happens if there is a newline or carriage return character (line break characters) within a field, such as a long memo field with more than one line of data. To fix this issue,

  • New lines (also known as line breaks or carriage returns) should be removed before the data is exported; or
  • The field must be properly escaped by wrapping it in quotation marks; i.e.; when the data is exported, the field should begin with " and end with ", with " characters within the field preceded by \ (backslash) (see Improper escaping above)


Anything else on your mind?
Log in to MindBridge to chat with us, email support@mindbridge.ai, or reach out to your assigned Customer Success Manager.


Related articles

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.