Docs/Writing data/Loading CSV
Writing data

Loading CSV into BigQuery

Load a CSV with bq load, a schema and --skip_leading_rows=1. Autodetect skips an all-text header, reads 12,50 as 1250, and turns 03/01/2026 into March 1 or January 3 depending on the other rows.

To load a CSV into BigQuery, run bq load --source_format=CSV --skip_leading_rows=1 dataset.table file.csv schema, use LOAD DATA in SQL, or upload the file in the console or in Querylab.io. Give the load a schema and skip the header row yourself. Autodetect is for a first look: it can leave the header in your data, read 12,50 as the integer 1250, and read the same 03/01/2026 as March 1 or January 3 depending on the other rows. One bad value fails the whole job and nothing is written. A batch load is free; you pay only to store the table.

Everything below was tested with real load jobs from local files. The error messages are the ones BigQuery returned. For the parts that are the same for every format (load limits, partition loads, cost), see Loading data into BigQuery. JSON has its own page: Loading JSON into BigQuery.

A load that works

bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  mydataset.orders \
  ./orders.csv \
  order_id:STRING,zip:STRING,amount:NUMERIC,ordered_on:DATE

The same from SQL. LOAD DATA reads from Cloud Storage, not from your machine:

LOAD DATA INTO mydataset.orders (order_id STRING, zip STRING, amount NUMERIC, ordered_on DATE)
FROM FILES (
  format = 'CSV',
  skip_leading_rows = 1,
  uris = ['gs://my-bucket/orders/*.csv']
);

Neither is run here; both need a dataset. A load appends by default. --replace (or LOAD DATA OVERWRITE) replaces the table. Gzip files (orders.csv.gz) load without any flag; we loaded one. Wildcards work for Cloud Storage only; a local file loads one per command.

Querylab.io’s Import tab does the same from a dropped file. It reads the first 1,000 rows, guesses the delimiter from comma, tab, pipe, semicolon and colon, guesses whether the first row is a header, and shows every column’s type before it runs the load job. You can change the delimiter, the null marker, the header setting and each type there. The other way is the BigQuery console’s Create table dialog.

The header row

BigQuery doesn’t know the first line is a header. --skip_leading_rows=1 tells it, and --autodetect tries to guess.

What we loadedResult
header + schema id:INTEGER, no --skip_leading_rowsfails: Unable to parse; line_number: 1 ... column_name: "id" column_type: INT64 value: "id"
header + schema, --skip_leading_rows=1loads
--skip_leading_rows=2loads, but the first data row is gone with no warning
autodetect, header id,name, rows 1,Adacolumns named id and name. The header is recognised because one column is numeric
autodetect, header name,city, rows Ada,Londoncolumns string_field_0, string_field_1. The header is loaded as a data row
the same file with --skip_leading_rows=1the header row is dropped, but the columns are still string_field_0, string_field_1
autodetect, a file with only a header lineone row with the header text, in string_field_0, string_field_1
autodetect, header names a,, (two blank)a, int64_field_1, string_field_2: blank names are generated from the type

The all-text header is the one that bites. Google’s docs say the same (“does not automatically detect headers if all of the columns are string types”). The fix is to pass a schema, or to add a numeric column to the file. With a schema, --skip_leading_rows=1 is all you need.

Delimiters, quotes and line breaks

CSV is only a convention, so the failures are about structure. Each line below was a separate load with a two-column schema id:INTEGER,note:STRING.

FileResult
1,"a, b" and 2,"say ""hi"""loads: a, b and say "hi"
a quoted value that contains a line breakfails: Missing close quote character ("). and CSV table references column position 1, but line contains only 1 columns.
the same, with --allow_quoted_newlinesloads, and the value keeps its \n
a row with one field instead of two (2)fails: CSV table references column position 1, but line contains only 1 columns.
the same, with --allow_jagged_rowsloads; the missing note is NULL
a row with three fields (2,x,extra)fails: Too many values in line. Found 3 column(s) when expecting 2.
the same, with --allow_jagged_rowsstill fails. Jagged rows are about missing trailing fields, not extra ones
the same, with --ignore_unknown_valuesloads; extra is dropped
a blank line, or a line with only spaces, between two rowsfails with the one-column error above, the same as a short row
a blank line, with --allow_jagged_rowsloads as a row of all NULLs
a blank line as the last line (the file ends in \n\n)fails the same way. A single \n at the end, or none, is fine
1,"abc with no closing quotefails: Missing close quote character (").
1,"a"bfails: Data between close quote character (") and field separator.
1,5" pipe and 1,a"b"c (quote inside an unquoted field)loads, the quotes kept as text
1, "a,b" (a space before the opening quote)loads as a,b
a UTF-16 filefails: Bad character (ASCII 0) encountered.

The blank line is a common one: a file saved by a tool that writes an empty line at the end fails the whole load, and nothing is written. Strip it, or load with --allow_jagged_rows and delete the all-NULL row afterwards. A blank line is not skipped by default, and in a one-column table it loads as a NULL row without any flag.

Windows line endings (\r\n), old Mac line endings (a lone \r) and a file with no final newline all load. A UTF-8 byte order mark at the start of the file was stripped without trouble, including when the first column name is read by autodetect. Google’s docs still say to remove BOMs, but we saw no problem with a UTF-8 one.

Other delimiters

The default is a comma, and autodetect does not look for another one. The delimiter must be one character: --field_delimiter='||' fails with Field delimiter must be a single character, found:"||". A semicolon file loaded with autodetect came out as one STRING column, string_field_0, holding id;name and 1;Ada. With a two-column schema it fails with the short-row error. Set the delimiter:

File usesFlag
semicolon--field_delimiter=';'
tab--field_delimiter=tab or --field_delimiter='\t', both work
pipe--field_delimiter='|'
quote '--quote="'" (the default is "; --quote='' turns quoting off)

A file with the wrong delimiter and a one-column STRING schema loads without any error: 1;Ada is stored as one value. Check the column count after the first load.

Encoding

The default is UTF-8. A Latin-1 or Windows-1252 file is the usual reason for odd characters in a table.

CaseResult
a Latin-1 file (Caf\xe9), no --encodingloads as Café, stored as UTF-8
the same, with --encoding=ISO-8859-1the same bytes in the table
a file that mixes valid UTF-8 and stray Latin-1 bytes, no flageach line converted on its own: Café and Zürich both came out right
a UTF-8 file loaded with --encoding=ISO-8859-1mojibake: Café became Café
Windows-1252 smart quotes (0x93, 0x94) with --encoding=ISO-8859-1“hi”, converted correctly
0xFF in a UTF-8 file, no flagÿ, not the replacement character
UTF-16fails (see above)

So an old file often loads right with no flag. Don’t count on it: Google’s docs say a non-UTF-8 file “may not exactly match what you expect”, and the flag is the only way to say what the encoding is. Don’t set --encoding=ISO-8859-1 on a file that is already UTF-8. Convert UTF-16 first: iconv -f UTF-16 -t UTF-8 in.csv > out.csv.

NULL, empty and “NULL”

In the fileColumnResult
, (nothing between the commas)any typeNULL
"" (two quotes)STRINGan empty string, not NULL
the text NULLSTRINGthe string "NULL"
the text NULLINTEGERfails: Unable to parse ... column_type: INT64 value: "NULL"
nothing, column is REQUIREDany typefails: Required column value is missing.

The text NULL is a string unless you say otherwise, which is how NULL strings get into tables exported from other databases. --null_marker names the text to treat as NULL, and --null_markers takes a comma-separated list:

bq load --source_format=CSV --skip_leading_rows=1 --null_markers='NA,-' \
  mydataset.readings ./readings.csv id:INTEGER,value:INTEGER,unit:STRING

With NA and - as markers, NA in the INTEGER column and - in the STRING column both loaded as NULL. Three things we hit:

  • A quoted marker is still a marker. With --null_marker=NULL, "NULL" (in quotes) became NULL too. You can’t have both a real string “NULL” and a null marker.
  • Once a marker is set, an empty field is no longer NULL in a STRING column. It is an empty string.
  • Once a marker is set, an empty field in a non-string column fails the load: Null marker is set to 'NULL' but empty value found for field n that is of type different than STRING or BYTES. Put the marker in every empty numeric or date field, or replace empty values before the load.

Querylab.io’s Import tab has a Null marker field, and when you set one it makes every column nullable before it sends the load job.

Numbers

With an explicit schema, each cell must parse as the column’s type. The cases below were separate loads of a single cell. Commas inside a value need quotes in the file, or the extra comma starts a new column and fails with Too many values in line.

Value in the cellINTEGERFLOATNUMERIC
12.50fails: Unable to parse12.512.5
"1,234"12341234fails: Invalid NUMERIC value: 1,234
"1,234.56"-1234.56fails: Invalid NUMERIC value: 1,234.56
"12,50" (European)-1250-
$12.50-12.5fails: Invalid NUMERIC value: $12.50
12.5%-0.125-
(12.50)--fails: Invalid NUMERIC value: (12.50)
1e3fails1000-
12 or 12 12--
+12, 001212--
NaN, inf-nan, inf-
9223372036854775808fails: Unable to parse--
0x1F, 1_000fails--

A dash means we didn’t test that pair. Two results matter most:

  • A European decimal 12,50 in quotes loaded into a FLOAT column without an error, as 1250. BigQuery drops the comma as a thousands separator. The same file with a NUMERIC column fails, which is the safer outcome. Load such a column as STRING and convert it in SQL (below).
  • FLOAT accepts a leading $ and a trailing %, and divides by 100 for the percent sign. NUMERIC accepts neither.

NUMERIC keeps 9 decimal digits. 12.123456789012 fails with Invalid NUMERIC value; BIGNUMERIC took 12.1234567890. Money should be NUMERIC, not FLOAT; see Data types.

Dates, times and booleans

An explicit DATE column takes YYYY-MM-DD and nothing else, as Google’s docs say. We also saw 2026-3-1 accepted as March 1.

CellDATETIMESTAMPDATETIME
2026-03-01loadsfails: Could not parse '2026-03-01' as a timestamp-
03/01/2026, 3/1/2026fails: Unable to parse--
2026/03/01fails--
20260301, 01-Mar-2026fails--
2026-03-01T10:00:00fails--
2026-03-01T10:00:00Z-10:00:00 UTCfails: Invalid datetime string
2026-03-01 10:00:00+02:00-08:00:00 UTC, convertedfails: Invalid datetime string
2026-03-01 10:00:00 +02:00-08:00:00 UTC-
2026-03-01 10:00:00 +0200-fails: Could not parse ... Required format is YYYY-MM-DD HH:MM[:SS[.SSSSSS]] or YYYY/MM/DD HH:MM[:SS[.SSSSSS]]-
2026-03-01 10:00:00 America/New_York-15:00:00 UTC-
2026/03/01 10:00-loads (no seconds needed)-
2026-03-01 10:00:00.123456789-10:00:00.123456, truncated-
1772359200 (seconds)-2026-03-01 10:00:00 UTC-
1772359200123 (ms)-fails: Cannot return an invalid timestamp value of 1772359200123000064 microseconds-

TIME takes HH:MM:SS; 10:00 failed with Invalid time string.

Other date formats: --date_format

For other layouts, say what the file uses. The flags take format elements (YYYY, MM, DD, Mon, HH24, MI):

Cell and flagResult
03/01/2026, --date_format=MM/DD/YYYY2026-03-01
01/03/2026, --date_format=DD/MM/YYYY2026-03-01
1/3/2026, --date_format=DD/MM/YYYY2026-03-01 (no zero padding needed)
2026/03/01, --date_format=YYYY/MM/DD2026-03-01
01-Mar-2026, --date_format=DD-Mon-YYYY2026-03-01
2026-03-01, --date_format=MM/DD/YYYYfails: Failed to parse input timestamp string at 0 with format element 'MM'
03/01/2026 10:30, --timestamp_format="MM/DD/YYYY HH24:MI"2026-03-01 10:30:00 UTC

Once you set a format it is the only one accepted for that type: the ISO date in the last DATE row stopped working. --datetime_format and --time_format exist for DATETIME and TIME. A timestamp without an offset is read as UTC. --time_zone=America/New_York changes that: 2026-03-01 10:30:00 became 15:30:00 UTC. Use it for files exported in local time.

A +0200 offset (no colon) is not accepted. If a source writes it, load the column as STRING and fix it in SQL.

Epoch timestamps

A number in a TIMESTAMP column is read as seconds. A value in milliseconds fails (above), and the failure message is misleading: it prints a huge number of microseconds. Load epoch columns as INTEGER and convert them in SQL; the unit is your call:

epoch milliseconds, read two waysOpen in Querylab.io
12345678
WITH raw AS (
  SELECT 1 AS id, 1772359200123 AS created
)
SELECT
  id,
  TIMESTAMP_MILLIS(created) AS right_unit,
  TIMESTAMP_SECONDS(DIV(created, 1000)) AS truncated
FROM raw
Result in BigQuery
Rowidright_unittruncated
112026-03-01 10:00:00.123000 UTC2026-03-01 10:00:00 UTC
1 row

Dividing by 1,000 first drops the .123. Querylab.io’s Import tab recognises numbers that look like epochs and offers Epoch Conversion (seconds or milliseconds to TIMESTAMP) in the type menu; it rewrites those columns to ISO timestamps in the file before the upload.

Booleans

BOOLEAN takes true/false, t/f, yes/no, y/n and 1/0, in any case. on failed with Unable to parse. Autodetect finds all of these except 1/0, which become INTEGER.

What autodetect guesses

With --autodetect, BigQuery samples the file and picks a type per column. We loaded one file with each of these cells in the first data row:

id,zip,amt,price_us,pct,eu,thou,d_iso,d_slash,ts_off,ts_epoch,flag_tf,flag_yn,flag_01,mixed
1,02134,12.50,$12.50,12.5%,"12,50","1,234",2026-03-01,03/01/2026,2026-03-01 10:00:00+02:00,1772359200,true,yes,1,7
2,10001,7,$7.00,3%,"7,25","12,345",2026-03-02,03/02/2026,2026-03-02 10:00:00+02:00,1772445600,false,no,0,x
ColumnDetected typeWhat was stored
zipINTEGER02134 became 2134. The leading zero is gone
amtFLOAT12.5 and 7.0
price_usFLOAT$12.50 became 12.5
pctFLOAT12.5% became 0.125
euINTEGER"12,50" became 1250, "7,25" became 725
thouINTEGER"1,234" became 1234
d_isoDATEfine
d_slashDATE03/01/2026 became 2026-03-01 (see below)
ts_offTIMESTAMP10:00:00+02:00 became 08:00:00 UTC
ts_epochINTEGERthe number, not a timestamp
flag_tfBOOLEANfine
flag_ynBOOLEANyes/no became true/false
flag_01INTEGER1/0 stay integers
mixedSTRING7 and x in one column

Other cells we tried: 1.0 is FLOAT; a column of only empty values is STRING; N/A or the text NULL in an otherwise numeric column makes it STRING; a column of " 12" with a leading space is INTEGER; 1-Mar-2026 and 3/1/26 both came out as DATE (2026-03-01).

Autodetect is more permissive than a schema. A DATE column you declare rejects 03/01/2026, but autodetect picks DATE for it and parses it.

The same date, two meanings

Autodetect decides a day-first or month-first order from the whole column. A file with 03/01/2026 and 03/02/2026 loaded as March 1 and March 2. A file with 03/01/2026 and 25/03/2026 loaded the first value as January 3, because 25 can’t be a month. So a day-first file where no day is above 12 comes out month-first, with no error, and the dates are wrong for the rows where they look valid. Declare such a column STRING and parse it yourself:

one string, two date ordersOpen in Querylab.io
123456789101112
WITH raw AS (
  SELECT 1 AS id, '03/01/2026' AS d UNION ALL
  SELECT 2, '25/03/2026' UNION ALL
  SELECT 3, '2026/03/01'
)
SELECT
  id,
  d,
  SAFE.PARSE_DATE('%m/%d/%Y', d) AS as_month_first,
  SAFE.PARSE_DATE('%d/%m/%Y', d) AS as_day_first
FROM raw
ORDER BY id
Result in BigQuery
Rowiddas_month_firstas_day_first
1103/01/20262026-03-012026-01-03
2225/03/2026null2026-03-25
332026/03/01nullnull
3 rows

SAFE.PARSE_DATE returns NULL instead of failing when the value doesn’t match the format. Row 2 (25/03/2026) is NULL as a month-first date, and row 3 matches neither pattern.

How much of the file it reads

Google’s docs say autodetect reads the first 500 rows. In our tests it read more than that: a text value on row 700 made the column STRING, and so did one on row 100,000 of a local file upload. A bad value near the end of a bigger file was not seen. With a bad value on row 130,000 or on the last row of a 200,000-row file, autodetect chose INTEGER, and the load failed at that line (line_number: 130001). A file with 30,000 wider rows (about 200 bytes each) behaved the same way. We found no fixed row count, so treat the sample as a part of the file, not all of it. A schema removes the guesswork.

Column names

The header becomes the column names. BigQuery accepts more than letters and underscores: with autodetect and no extra flag, order id, a-b, Café, 2nd, a&b, a% and a:b all loaded as they were written. These did not:

HeaderResult
a.b, a/b, a(b), price ($)fails: Field name 'price ($)' is not supported by the current character map. Please change your field name or use character map V2 ...
id,idfails: Duplicated field name 'id'
id,Idfails: Duplicate column 'Id'. Names are case-insensitive
_PARTITIONTIMEfails: Invalid field name "_PARTITIONTIME". Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION ...

The a.b and _PARTITIONTIME messages are explained in Invalid field name.

--column_name_character_map decides what happens to the rest. On the header order id,price ($),a.b,2nd,Café:

ValueResulting names
STRICTfails on price ($)
V1order_id, price____, a_b, _2nd, Caf__
V2order id, price ___, a_b, 2nd, Café

V1 keeps names usable without backticks, and it replaces é too. V2 keeps spaces and accented letters and replaces only what it must. Either is a faster fix than editing a 200-column header.

Appending when the column order changed

A file whose columns are in a different order than the table is a quiet way to corrupt data. We appended b,a / y2,x2 to a table (a STRING, b STRING) with a schema and --skip_leading_rows=1. The load succeeded and wrote a = y2, b = x2: the header was skipped, not read, and columns were matched by position. With both columns STRING there is no error to warn you.

--source_column_match=NAME reads the header row and matches columns by name. It worked when appending to an existing table (note,id went into id and note correctly). It can’t be combined with a schema given on the command line: source_column_match cannot be set to NAME when providing the table schema. Autodetect matches by name by default when the table exists, as Google’s docs say, and appending a file with an extra column fails with It looks like you are appending to an existing table with autodetect enabled. Disabling autodetect may resolve this.

One bad row fails the load

A cell that doesn’t parse fails the whole job, and nothing is written. The error names the line, the byte offset, the column and the value:

Error while reading data, error message: Unable to parse; line_number: 3 byte_offset_to_start_of_line: 9 column_index: 1 column_name: "n" column_type: INT64 value: "x"

--max_bad_records=N skips up to N bad rows. We loaded four rows, two with a bad INTEGER (x, y). With --max_bad_records=1 the job failed, and with 2 it succeeded with two warnings, one per bad row, and the table had 2 rows. The skipped rows are only in the job’s warnings. If you allow bad records, compare the row count with the source. The message of a job that fails is explained in CSV or JSON table encountered too many errors.

Fixing it in SQL after the load

For anything messy, load every column as STRING, fix it in SQL, and write the typed table with CREATE TABLE ... AS SELECT. A text column can’t fail a load. To find the values that wouldn’t parse, SAFE_CAST returns NULL for them:

find values that won't cast to INT64Open in Querylab.io
1234567891011
WITH raw AS (
  SELECT 1 AS line_no, '42' AS qty UNION ALL
  SELECT 2, '' UNION ALL
  SELECT 3, '1,234' UNION ALL
  SELECT 4, '7.0' UNION ALL
  SELECT 5, 'ten'
)
SELECT line_no, qty
FROM raw
WHERE qty != '' AND SAFE_CAST(qty AS INT64) IS NULL
ORDER BY line_no
Result in BigQuery
Rowline_noqty
131,234
247.0
35ten
3 rows

The 1,234 and 7.0 rows both fail INT64. In a load, 1,234 is accepted and 7.0 is not, so the same value can pass a load and fail this query. European decimals, with the thousands dot removed first:

1.234,50 and 12,50 to NUMERICOpen in Querylab.io
123456789101112
WITH raw AS (
  SELECT 'A-1' AS sku, '12,50' AS price UNION ALL
  SELECT 'A-2', '1.234,50' UNION ALL
  SELECT 'A-3', '7' UNION ALL
  SELECT 'A-4', 'n/a'
)
SELECT
  sku,
  price,
  SAFE_CAST(REPLACE(REPLACE(price, '.', ''), ',', '.') AS NUMERIC) AS price_num
FROM raw
ORDER BY sku
Result in BigQuery
Rowskupriceprice_num
1A-112,5012.5
2A-21.234,501234.5
3A-377
4A-4n/anull
4 rows

Currency, with accounting-style negatives in parentheses:

strip $ and commas, handle (12.50)Open in Querylab.io
12345678910111213
WITH raw AS (
  SELECT 1 AS id, '$1,234.50' AS amount UNION ALL
  SELECT 2, '$12.50' UNION ALL
  SELECT 3, '($12.50)' UNION ALL
  SELECT 4, '-$3.00'
)
SELECT
  id,
  amount,
  IF(STARTS_WITH(amount, '('), -1, 1)
    * SAFE_CAST(REGEXP_REPLACE(amount, r'[$,()]', '') AS NUMERIC) AS amount_num
FROM raw
ORDER BY id
Result in BigQuery
Rowidamountamount_num
11$1,234.501234.5
22$12.5012.5
33($12.50)-12.5
44-$3.00-3
4 rows

Zip codes that autodetect turned into integers can be padded back to five digits, as long as you know the real width:

put the leading zeros backOpen in Querylab.io
12345678
WITH loaded AS (
  SELECT 1 AS id, 2134 AS zip UNION ALL
  SELECT 2, 10001 UNION ALL
  SELECT 3, 501
)
SELECT id, zip, LPAD(CAST(zip AS STRING), 5, '0') AS zip5
FROM loaded
ORDER BY id
Result in BigQuery
Rowidzipzip5
11213402134
221000110001
3350100501
3 rows

This can’t recover a value that lost information. 007 and 7 were the same integer, so a column of mixed widths can’t be repaired. Reload it as STRING.

A cell that holds a list, such as red|large|cotton, splits into rows with SPLIT and UNNEST:

split a delimited cell into rowsOpen in Querylab.io
1234567
WITH products AS (
  SELECT 'p1' AS sku, 'red|large|cotton' AS tags UNION ALL
  SELECT 'p2', 'blue'
)
SELECT sku, tag
FROM products, UNNEST(SPLIT(tags, '|')) AS tag
ORDER BY sku, tag
Result in BigQuery
Rowskutag
1p1cotton
2p1large
3p1red
4p2blue
4 rows

If the same file is loaded twice, or an export overlaps the last one, keep the newest row per key:

keep the latest row per orderOpen in Querylab.io
12345678910
WITH orders AS (
  SELECT 'o1' AS order_id, 10 AS amount, DATE '2026-03-01' AS loaded_on UNION ALL
  SELECT 'o2', 20, DATE '2026-03-01' UNION ALL
  SELECT 'o1', 12, DATE '2026-03-02' UNION ALL
  SELECT 'o3', 5, DATE '2026-03-02'
)
SELECT order_id, amount, loaded_on
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_on DESC) = 1
ORDER BY order_id
Result in BigQuery
Roworder_idamountloaded_on
1o1122026-03-02
2o2202026-03-01
3o352026-03-02
3 rows

Limits

LimitValue
Row100 MB
File, gzip-compressed4 GB
File, uncompressed5 TB
Local file through the console100 MB
Load jobs per table per day1,500, failed ones included

A gzip file can’t be read in parallel, so one big .csv.gz loads slower than the same data split into several uncompressed files, and one job can’t mix compressed and uncompressed files. CSV can’t hold nested or repeated data; for that use JSON, Parquet or Avro.

What it costs

A batch load on the shared slot pool is free. You pay for storing the table. Inserting the same rows with INSERT statements is billed as queries, and streaming is billed per GiB. See Streaming and Storage pricing.

Open the playground. It is the real app on public datasets and needs no sign-in.