Oninit
Talk to an expert →

Informix Error -847: Error in load file row number.

Cause and resolution

Error in load file row number.

A problem exists with the data on the indicated row of the load data file. The operation stopped after it inserted rows up to but not including the row that is noted (number-1 rows have been inserted). If this operation is inside a transaction, roll back the transaction. If not, either delete the inserted rows from the table or remove the used rows from the file before you repeat the operation. To correct the file, look for additional error messages that might help isolate the problem. Possibly not enough, or too many, fields (delimiters) exist on the indicated row. Possibly a data conversion problem exists, (for example, nonnumeric characters in a numeric field, an improperly formatted DATETIME value, or a character string that is too long). Possibly a null (zero-length) field exists in a column where nulls are not allowed. Edit the load file to correct the problem. Look for similar problems in following lines, and then repeat the operation. The row number and line number might not be the same because some rows might be split into several lines. To identify split rows and their corresponding line numbers, run the following command:

egrep -n"\\\\$"

To calculate a line number of an incorrect row, add the number of split rows that occur prior to the row to the row number.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-847 fires when a specific row of the LOAD data file has a data problem — per the official guidance, the operation stops after inserting all rows up to (but not including) the indicated row, and three specific causes are named: a delimiter-count mismatch on that row, a data conversion problem, or a NULL value in a column that disallows it.

  1. Too few or too many delimiters on the indicated row, per the official guidance — the row-specific version of -846's file-wide check.
  2. A data conversion problem, per the official guidance's examples — nonnumeric characters in a numeric field, an improperly formatted DATETIME value, or a character string too long for its column.
  3. A NULL (zero-length) field in a column that doesn't allow NULLs, per the official guidance.
Solutions / Resolution
  1. Roll back the transaction, if this operation is inside one, per the official guidance.
  2. If not in a transaction, either delete the already-inserted rows from the table, or remove the already-processed rows from the file, per the official guidance, before repeating the operation — since number-1 rows were already successfully inserted.
  3. Look for additional error messages that might help isolate the specific problem, per the official guidance.
  4. Edit the load file to correct the problem, per the official guidance, once the specific issue (delimiter count, data conversion, or NULL-in-non-null-column) is identified on the indicated row.
Examples
Inspecting the specific failing row
$ sed -n '42p' orders_import.unl

Examine row 42 (or whichever row number the error reported) directly for delimiter count, data format, and NULL/empty fields.

Diagnostic Checks
  1. Extract and inspect the specific row number reported, checking delimiter count, data format/type compatibility, and any NULL/empty field against the target column's NOT NULL status.
  2. Confirm how many rows were already inserted (number-1) before deciding whether to roll back or clean up partial data.
Related Errors / Related Topics
  • -846 — "Number of values in load file is not equal to number of columns." A related LOAD-content error, checked against the file's first line broadly, rather than a specific row reported here.
  • -838 — "A line in the load file is too long." A related LOAD-content error, about line length specifically.

Extract the specific row number reported and check it against the three named causes — delimiter count, data conversion, or a disallowed NULL.