Oninit
Talk to an expert →

Informix Error -846: Number of values in load file is not equal to number of columns.

Cause and resolution

Number of values in load file is not equal to number of columns.

The LOAD processor counts the delimiters in the first line of the file to determine the number of values in the load file. One delimiter must exist for each column in the table or for each column in the list of columns if one is specified. Check that you specified the file that you intended and that it uses the correct delimiter character. An empty line in the text can also cause this error.

If the LOAD statement does not specify a delimiter, verify that the default delimiter matches the delimiter that is used in the file. If you are in doubt about the default delimiter, specify the delimiter in the LOAD statement.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-846 fires when LOAD's delimiter-count check on the input file's first line doesn't match the target table's (or specified column list's) column count — per the official guidance, several distinct scenarios can produce this mismatch.

  1. The wrong file specified, per the official guidance — a file with a genuinely different structure than intended.
  2. The wrong delimiter character used or assumed, per the official guidance — if the LOAD statement doesn't specify one explicitly, the default delimiter must actually match what's in the file.
  3. An empty line in the file, per the official guidance — specifically named as a cause, since an empty first line has zero delimiters regardless of the file's real structure.
  4. A column list specified in LOAD with a different count than the file's actual columns, if the statement names specific columns rather than loading into every column.
Solutions / Resolution
  1. Check that the file specified is the one intended, per the official guidance.
  2. Check that the file uses the correct delimiter character, per the official guidance, and that the LOAD statement's delimiter (or the default, if unspecified) matches it.
  3. Check for an empty line in the file, per the official guidance, especially at or near the beginning.
  4. If in doubt about the default delimiter, specify it explicitly in the LOAD statement, per the official guidance:
    LOAD FROM 'orders_import.unl' DELIMITER '|' INSERT INTO orders;
Examples
Checking the file's first line and delimiter
$ head -3 orders_import.unl

Count the delimiters in the first non-empty line and compare against the target table's column count.

Specifying the delimiter explicitly
LOAD FROM 'orders_import.unl' DELIMITER '|' INSERT INTO orders;
Diagnostic Checks
  1. Inspect the file's first line for the correct delimiter character and column count, and check for a leading empty line.
  2. Compare against the target table's (or specified column list's) actual column count.
Related Errors / Related Topics
  • -838 — "A line in the load file is too long." A related LOAD file-content error, about line length rather than delimiter/column-count mismatch.
  • -809 — "SQL Syntax error has occurred." A related LOAD/UNLOAD/INFO error, about the INSERT clause's own syntax rather than the data file's structure.

Check the file's first line's delimiters against the target column count — an empty first line is a specifically named, easy-to-miss cause.