CSV or JSON table encountered too many errors, giving up
A load job, or a query over an external table, found more bad rows than it may skip. The first message is only a count. The line, the column and the value are in the failure details further down.
Error while reading data, error message: CSV processing encountered too many errors, giving up. Rows: 3; errors: 1; max bad: 0; error percent: 0
For a JSON file the first line reads:
Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 2; errors: 1. Please look into the errors[] collection for more details.
Older questions quote CSV table encountered too many errors, giving up. Rows: 1; errors: 1. BigQuery now says CSV processing for a CSV file. For JSON the first line still says JSON table, and the first failure detail says JSON processing encountered too many errors. The cause and the fixes are the same.
What BigQuery means
A load job reads the file row by row. A row that does not fit the schema is a bad record. max_bad_records is how many bad records the job may skip, and it defaults to 0. The first bad record is therefore enough to fail the job, and nothing is written.
The message above is only the count. It does not say which row or why. The reason is in the failure details, which are listed with the message. This page shows each detail text and what it means.
The smallest file that reproduces it
badint.csv:
id,name
1,a
x,b
3,c
bq load --source_format=CSV --skip_leading_rows=1 mydataset.t badint.csv id:INT64,name:STRING
Error while reading data, error message: CSV processing encountered too many errors, giving up. Rows: 3; errors: 1; max bad: 0; error percent: 0
Failure details:
- Error while reading data, error message: Unable to parse; line_number: 3 byte_offset_to_start_of_line: 12 column_index: 0 column_name: "id" column_type: INT64 value: "x"
Line 3 of the file, column id, value x. That is the whole diagnosis. Line numbers count the header line.
Read the details of a failed job
bq load prints the failure details. For a job that ran elsewhere, give its id to bq show -j:
bq show -j JOB_ID
Under Error encountered during job execution, the first line is the count and the lines under Failure details are the rows. With --format=prettyjson, status.errorResult holds the count and status.errors holds the count again followed by one entry per detail. A JSON load adds a second count line before the rows, for example JSON processing encountered too many errors, giving up. Rows: 4; errors: 3; max bad: 2; error percent: 0. It is the only place a JSON load shows max bad. All of them have the reason invalid.
BigQuery lists the details it found before it gave up. A file with 12 bad values and --max_bad_records=1 listed all 12. A file read by a JSON load with --max_bad_records=1 stopped at the second error and read 3 of its 4 lines (Rows: 3; errors: 2). The counts show how far BigQuery got.
The detail messages
Each CSV text below comes from a file with a single bad row. Line numbers and offsets are from those files.
| Detail text | What it means | Fix |
|---|---|---|
Unable to parse; line_number: 3 byte_offset_to_start_of_line: 12 column_index: 0 column_name: "id" column_type: INT64 value: "x" | the value does not fit the column type | correct the value, load the column as STRING, or skip the row with --max_bad_records |
Too many values in line. Found 3 column(s) when expecting 2.; line_number: 2 byte_offset_to_start_of_line: 8 | the row has more fields than the schema has columns | --ignore_unknown_values, or add the column to the schema |
CSV table references column position 1, but line contains only 1 columns.; line_number: 3 byte_offset_to_start_of_line: 12 column_index: 1 column_name: "name" column_type: STRING | the row has fewer fields than the schema | --allow_jagged_rows loads the missing values as NULL |
Missing close quote character (").; line_number: 2 byte_offset_to_start_of_line: 8 column_index: 1 column_name: "name" column_type: STRING value: "abc" | a quote was opened and never closed | fix the file; --max_bad_records skips that row |
JSON parsing error in row starting at position 20: Expected , or } after key:value pair | the line is not valid JSON | fix the line; the position is the offset of the row in the file |
JSON parsing error in row starting at position 20: Could not convert value 'string_value: \t "x"' to integer. Field: id; Value: x | a JSON value does not fit the column type | correct the value or change the column type |
JSON parsing error in row starting at position 20: No such field: extra. | the object has a key the schema lacks | --ignore_unknown_values, or add the field |
JSON parsing error in row starting at position 0: Start of array encountered without start of object. | the file is one JSON array, and BigQuery wants one object per line | write newline-delimited JSON, one object on each line |
Every detail, CSV and JSON, starts with Error while reading data, error message: in the output. It is left off in the table. The gap before "x" in the JSON type error is a tab. In an external table each detail also starts with the file, as gs://my-bucket/badint.csv: Error while reading data, error message: ....
A newline inside a quoted value
id,name
1,"multi
line"
2,b
This gives two details, because the reader sees a quote that never closes and then a short line:
- Error while reading data, error message: Missing close quote character (").; line_number: 2 byte_offset_to_start_of_line: 8 column_index: 1 column_name: "name" column_type: STRING value: "multi"
- Error while reading data, error message: CSV table references column position 1, but line contains only 1 columns.; line_number: 3 byte_offset_to_start_of_line: 17 column_index: 1 column_name: "name" column_type: STRING
--allow_quoted_newlines fixes it. The value multi and line is then loaded as one value with a line break in it.
The flags
| Flag | What it does | Probed result |
|---|---|---|
--max_bad_records=N | skip up to N bad records | badint.csv with 1 loaded rows 1 and 3, and the job ended with a warning that holds the skipped row |
--allow_jagged_rows | accept rows that miss trailing columns (CSV only) | a file with a line 2 instead of 2,b loaded 2 with name NULL |
--allow_quoted_newlines | accept a line break inside quotes (CSV only) | the newline file loaded 2 rows |
--ignore_unknown_values | ignore extra trailing CSV fields, or JSON keys the schema lacks | 1,a,extra loaded as 1,a; {"id":2,"name":"b","extra":1} loaded as 2,b |
Two things to know before using them:
--allow_jagged_rowsdoes not help a row with an extra field. The job still fails withToo many values in line. Use--ignore_unknown_values.--max_bad_recordscounts errors. A file with 12 bad values and--max_bad_records=1still fails. With--max_bad_records=12it loads the one good row. A JSON file with three bad lines failed with--max_bad_records=2. The job error saidRows: 4; errors: 3, and the first failure detail saiderrors: 3; max bad: 2.
A job that skips rows succeeds. The skipped rows are only in the warning, so compare the row count of the table with the file when you allow bad records.
Queries that look wrong but run
A load with autodetect and bad values. --autodetect picks the type from the file. For a JSON file with "id":1 on one line and "id":"x" on the next, it makes id a STRING, and both rows load. The load did not fail because the schema bent to the data. Check the schema it chose with bq show --schema.
COUNT(*) over an external table. A query that reads no columns never parses the values. SELECT COUNT(*) over an external table on a file with 12 bad values returned 13, bad rows included. The same table fails when a query reads the id column.
The same error from an external table
An external table reads the file when you query it, with the same options. The message starts with the table name instead:
SELECT * FROM mydataset.ext_orders
Error while reading table: mydataset.ext_orders, error message: Unable to parse; line_number: 3 byte_offset_to_start_of_line: 12 column_index: 0 column_name: "id" column_type: INT64 value: "x" File: gs://my-bucket/badint.csv
A single bad row gives this detail without a count. A table with max_bad_records = 1 over a file with 12 bad rows gives the count message:
Error while reading table: mydataset.ext_orders, error message: CSV processing encountered too many errors, giving up. Rows: 13; errors: 12; max bad: 1; error percent: 0
For JSON, the first line is the row that failed, and the count is in the failure details:
Error while reading table: mydataset.ext_json, error message: JSON parsing error in row starting at position 20: Expected , or } after key:value pair File: gs://my-bucket/bad.json
The option goes in the table definition:
CREATE OR REPLACE EXTERNAL TABLE mydataset.ext_orders (id INT64, name STRING)
OPTIONS (
format = 'CSV',
uris = ['gs://my-bucket/badint.csv'],
skip_leading_rows = 1,
max_bad_records = 1
)
A query over it returns rows 1 and 3 and a warning that names the file and the skipped value. ignore_unknown_values = true takes extra CSV fields the same way as the load flag. See External tables.
Different errors that look similar
| Message | Cause |
|---|---|
Invalid field name "a.b". Fields must contain the allowed characters... | a header or a JSON key is not a legal column name. See Invalid field name |
Bad int64 value: 1.5 | a CAST inside a query. A load reports the same problem as Unable to parse |
In Querylab.io
A failed import in Querylab.io shows this message and every failure detail under it, with a link to this page. The editor cannot read your file, so nothing is marked as you type. A query over an external table that fails like this links to this page from the error details. There is no fix button.
See Loading CSV, Loading JSON and Loading data.