Docs/Error messages/CSV or JSON table encountered too many errors
Error messagesData values

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 textWhat it meansFix
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 typecorrect 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: 8the 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: STRINGthe 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 closedfix the file; --max_bad_records skips that row
JSON parsing error in row starting at position 20: Expected , or } after key:value pairthe line is not valid JSONfix 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: xa JSON value does not fit the column typecorrect 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 linewrite 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

FlagWhat it doesProbed result
--max_bad_records=Nskip up to N bad recordsbadint.csv with 1 loaded rows 1 and 3, and the job ended with a warning that holds the skipped row
--allow_jagged_rowsaccept rows that miss trailing columns (CSV only)a file with a line 2 instead of 2,b loaded 2 with name NULL
--allow_quoted_newlinesaccept a line break inside quotes (CSV only)the newline file loaded 2 rows
--ignore_unknown_valuesignore extra trailing CSV fields, or JSON keys the schema lacks1,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_rows does not help a row with an extra field. The job still fails with Too many values in line. Use --ignore_unknown_values.
  • --max_bad_records counts errors. A file with 12 bad values and --max_bad_records=1 still fails. With --max_bad_records=12 it loads the one good row. A JSON file with three bad lines failed with --max_bad_records=2. The job error said Rows: 4; errors: 3, and the first failure detail said errors: 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

MessageCause
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.5a 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.