Summary
MindBridge expects every row of a analysis file to have the same columns for each row and the data in the same order for each row. Optionally, the first row in the file is expected to be a header row that indicates the name of each column in the file that applies to each row. To help detect cases of files not conforming to this requirement, MindBridge 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 warning appears on the Data Validation step of the import process indicating "The data contains an inconsistent number of columns" and some more details about the number of columns for each row.
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 fixed width text).
Improper escaping
If certain special characters, such as double quotes or the file's delimiter character (for example, a 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:
Split/Broken lines
MindBridge expects there to be one and only one entry per row (no broken lines; next entry 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; for example when the data is exported, the field should begin with " and end with ", with " characters within the field doubled (see Improper escaping above)
Anything else on your mind? Chat with us or submit a request for further assistance.