# 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. Source: https://docs.querylab.io/bigquery/loading-csv/ Updated: 2026-11-10 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](https://docs.querylab.io/bigquery/loading-data/). JSON has its own page: [Loading JSON into BigQuery](https://docs.querylab.io/bigquery/loading-json/). ## A load that works ```sh 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: ```sql 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](https://docs.querylab.io/data-management/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 loaded | Result | | ------------------------------------------------------ | --------------------------------------------------------------------------------------------- | | header + schema `id:INTEGER`, no `--skip_leading_rows` | fails: `Unable to parse; line_number: 1 ... column_name: "id" column_type: INT64 value: "id"` | | header + schema, `--skip_leading_rows=1` | loads | | `--skip_leading_rows=2` | loads, but the first data row is gone with no warning | | autodetect, header `id,name`, rows `1,Ada` | columns named `id` and `name`. The header is recognised because one column is numeric | | autodetect, header `name,city`, rows `Ada,London` | columns `string_field_0`, `string_field_1`. **The header is loaded as a data row** | | the same file with `--skip_leading_rows=1` | the header row is dropped, but the columns are still `string_field_0`, `string_field_1` | | autodetect, a file with only a header line | one 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`. | File | Result | | ---------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- | | `1,"a, b"` and `2,"say ""hi"""` | loads: `a, b` and `say "hi"` | | a quoted value that contains a line break | fails: `Missing close quote character (").` and `CSV table references column position 1, but line contains only 1 columns.` | | the same, with `--allow_quoted_newlines` | loads, 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_rows` | loads; 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_rows` | still fails. Jagged rows are about missing trailing fields, not extra ones | | the same, with `--ignore_unknown_values` | loads; `extra` is dropped | | a blank line, or a line with only spaces, between two rows | fails with the one-column error above, the same as a short row | | a blank line, with `--allow_jagged_rows` | loads as a row of all `NULL`s | | 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 quote | fails: `Missing close quote character (").` | | `1,"a"b` | fails: `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 file | fails: `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 uses | Flag | | --------- | ------------------------------------------------------------------ | | 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. | Case | Result | | ----------------------------------------------------------------------- | ----------------------------------------------------------------------- | | a Latin-1 file (`Caf\xe9`), no `--encoding` | loads as `Café`, stored as UTF-8 | | the same, with `--encoding=ISO-8859-1` | the same bytes in the table | | a file that mixes valid UTF-8 and stray Latin-1 bytes, no flag | each line converted on its own: `Café` and `Zürich` both came out right | | a UTF-8 file loaded with `--encoding=ISO-8859-1` | **mojibake**: `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-16 | fails (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 file | Column | Result | | -------------------------------- | --------- | ------------------------------------------------------------- | | `,` (nothing between the commas) | any type | `NULL` | | `""` (two quotes) | `STRING` | an empty string, not `NULL` | | the text `NULL` | `STRING` | the string `"NULL"` | | the text `NULL` | `INTEGER` | fails: `Unable to parse ... column_type: INT64 value: "NULL"` | | nothing, column is `REQUIRED` | any type | fails: `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: ```sh 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 cell | `INTEGER` | `FLOAT` | `NUMERIC` | | --------------------- | ------------------------ | ------------ | ---------------------------------------- | | `12.50` | fails: `Unable to parse` | `12.5` | `12.5` | | `"1,234"` | **`1234`** | `1234` | fails: `Invalid NUMERIC value: 1,234` | | `"1,234.56"` | - | `1234.56` | fails: `Invalid NUMERIC value: 1,234.56` | | `"12,50"` (European) | - | **`1250`** | - | | `$12.50` | - | `12.5` | fails: `Invalid NUMERIC value: $12.50` | | `12.5%` | - | **`0.125`** | - | | `(12.50)` | - | - | fails: `Invalid NUMERIC value: (12.50)` | | `1e3` | fails | `1000` | - | | ` 12` or `12 ` | `12` | - | - | | `+12`, `0012` | `12` | - | - | | `NaN`, `inf` | - | `nan`, `inf` | - | | `9223372036854775808` | fails: `Unable to parse` | - | - | | `0x1F`, `1_000` | fails | - | - | 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](https://docs.querylab.io/bigquery/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. | Cell | `DATE` | `TIMESTAMP` | `DATETIME` | | -------------------------------------- | ------------------------ | ---------------------------------------------------------------------------------------------------------------- | -------------------------------- | | `2026-03-01` | loads | fails: `Could not parse '2026-03-01' as a timestamp` | - | | `03/01/2026`, `3/1/2026` | fails: `Unable to parse` | - | - | | `2026/03/01` | fails | - | - | | `20260301`, `01-Mar-2026` | fails | - | - | | `2026-03-01T10:00:00` | fails | - | - | | `2026-03-01T10:00:00Z` | - | `10:00:00 UTC` | fails: `Invalid datetime string` | | `2026-03-01 10:00:00+02:00` | - | `08:00:00 UTC`, converted | fails: `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 flag | Result | | ------------------------------------------------------------- | ----------------------------------------------------------------------------- | | `03/01/2026`, `--date_format=MM/DD/YYYY` | `2026-03-01` | | `01/03/2026`, `--date_format=DD/MM/YYYY` | `2026-03-01` | | `1/3/2026`, `--date_format=DD/MM/YYYY` | `2026-03-01` (no zero padding needed) | | `2026/03/01`, `--date_format=YYYY/MM/DD` | `2026-03-01` | | `01-Mar-2026`, `--date_format=DD-Mon-YYYY` | `2026-03-01` | | `2026-03-01`, `--date_format=MM/DD/YYYY` | fails: `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 ways** ```sql 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 (1 row): | id | right_unit | truncated | | --- | --- | --- | | 1 | 2026-03-01 10:00:00.123000 UTC | 2026-03-01 10:00:00 UTC | 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: ```text 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 ``` | Column | Detected type | What was stored | | ---------- | ------------- | -------------------------------------------------- | | `zip` | `INTEGER` | `02134` became `2134`. The leading zero is gone | | `amt` | `FLOAT` | `12.5` and `7.0` | | `price_us` | `FLOAT` | `$12.50` became `12.5` | | `pct` | `FLOAT` | `12.5%` became `0.125` | | `eu` | `INTEGER` | `"12,50"` became **`1250`**, `"7,25"` became `725` | | `thou` | `INTEGER` | `"1,234"` became `1234` | | `d_iso` | `DATE` | fine | | `d_slash` | `DATE` | `03/01/2026` became `2026-03-01` (see below) | | `ts_off` | `TIMESTAMP` | `10:00:00+02:00` became `08:00:00` UTC | | `ts_epoch` | `INTEGER` | the number, not a timestamp | | `flag_tf` | `BOOLEAN` | fine | | `flag_yn` | `BOOLEAN` | `yes`/`no` became `true`/`false` | | `flag_01` | `INTEGER` | `1`/`0` stay integers | | `mixed` | `STRING` | `7` 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 orders** ```sql 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 (3 rows): | id | d | as_month_first | as_day_first | | --- | --- | --- | --- | | 1 | 03/01/2026 | 2026-03-01 | 2026-01-03 | | 2 | 25/03/2026 | NULL | 2026-03-25 | | 3 | 2026/03/01 | NULL | NULL | `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: | Header | Result | | --------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- | | `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,id` | fails: `Duplicated field name 'id'` | | `id,Id` | fails: `Duplicate column 'Id'`. Names are case-insensitive | | `_PARTITIONTIME` | fails: `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](https://docs.querylab.io/bigquery-errors/invalid-field-name/). `--column_name_character_map` decides what happens to the rest. On the header `order id,price ($),a.b,2nd,Café`: | Value | Resulting names | | -------- | ----------------------------------------------- | | `STRICT` | fails on `price ($)` | | `V1` | `order_id`, `price____`, `a_b`, `_2nd`, `Caf__` | | `V2` | `order 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: ```text 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](https://docs.querylab.io/bigquery-errors/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 INT64** ```sql 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 (3 rows): | line_no | qty | | --- | --- | | 3 | 1,234 | | 4 | 7.0 | | 5 | ten | 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 NUMERIC** ```sql 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 (4 rows): | sku | price | price_num | | --- | --- | --- | | A-1 | 12,50 | 12.5 | | A-2 | 1.234,50 | 1234.5 | | A-3 | 7 | 7 | | A-4 | n/a | NULL | Currency, with accounting-style negatives in parentheses: **strip $ and commas, handle (12.50)** ```sql 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 (4 rows): | id | amount | amount_num | | --- | --- | --- | | 1 | $1,234.50 | 1234.5 | | 2 | $12.50 | 12.5 | | 3 | ($12.50) | -12.5 | | 4 | -$3.00 | -3 | 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 back** ```sql 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 (3 rows): | id | zip | zip5 | | --- | --- | --- | | 1 | 2134 | 02134 | | 2 | 10001 | 10001 | | 3 | 501 | 00501 | 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 rows** ```sql 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 (4 rows): | sku | tag | | --- | --- | | p1 | cotton | | p1 | large | | p1 | red | | p2 | blue | 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 order** ```sql 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 (3 rows): | order_id | amount | loaded_on | | --- | --- | --- | | o1 | 12 | 2026-03-02 | | o2 | 20 | 2026-03-01 | | o3 | 5 | 2026-03-02 | ## Limits | Limit | Value | | ------------------------------ | --------------------------- | | Row | 100 MB | | File, gzip-compressed | 4 GB | | File, uncompressed | 5 TB | | Local file through the console | 100 MB | | Load jobs per table per day | 1,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](https://docs.querylab.io/bigquery/streaming/) and [Storage pricing](https://docs.querylab.io/bigquery/storage-pricing/). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Loading CSV data from Cloud Storage](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv) - [Using schema auto-detection](https://cloud.google.com/bigquery/docs/schema-detect) - [Batch loading data](https://cloud.google.com/bigquery/docs/batch-loading-data) - [Quotas and limits, load jobs](https://cloud.google.com/bigquery/quotas#load_jobs)