Docs/Writing data/Loading Excel
Writing data

Loading Excel files into BigQuery

BigQuery has no Excel load format. An .xlsx file fails every loader with a different error. Export one sheet to CSV, or open the file in Querylab.io's Import tab, which reads .xlsx and .xls directly. This page lists what Excel's CSV does to dates, zip codes and decimals.

BigQuery cannot load an Excel file. .xlsx and .xls are not load formats: bq load, LOAD DATA and the console’s Create table accept CSV, JSON, Avro, Parquet and ORC, and that is all. To get a workbook in, you convert one sheet at a time to something BigQuery reads: a CSV file, a Parquet file, or a Google Sheet that BigQuery reads in place (see Google Sheets and BigQuery). Querylab.io’s Import tab does the conversion for you: it reads .xlsx and .xls directly. Batch loads are free; you pay to store the table.

Everything below was run as real load jobs. The error messages are the ones BigQuery returned.

Loading the .xlsx anyway

An .xlsx file is a ZIP archive. We built a two-sheet workbook with openpyxl and gave it to each loader.

CommandResult
bq load --source_format=CSV --autodetectCSV processing encountered too many errors, giving up. Rows: 23; errors: 25; max bad: 0 with Bad character (ASCII 0) encountered. on line 1
bq load with a 5-column CSV schemaToo many values in line. Found 6 column(s) when expecting 5. and CSV table references column position 4, but line contains only 1 columns.
bq load with no --source_formatthe same CSV errors, plus the client’s note data will be treated as CSV format by default
--source_format=NEWLINE_DELIMITED_JSONFailed to parse JSON: Unexpected token; Parser terminated before end of string
--source_format=PARQUETInput file is not in Parquet format.
--source_format=AVROThe Apache Avro library failed to parse the header with the following error: Invalid data file. Magic does not match
--source_format=ORCThe Apache Orc library failed to parse file metadata from file tail with error: Invalid ORC postscript length
--source_format=XLSXrejected by the client: value should be one of <CSV|NEWLINE_DELIMITED_JSON|DATASTORE_BACKUP|AVRO|PARQUET|ORC|THRIFT>

The first row’s error value starts with PK\003\004, the ZIP signature. The CSV errors are the ones people search for: the file is read as text, and the first zero byte stops it. None of these loads wrote anything.

Three ways to get a sheet in

RouteBest forWatch out for
Querylab.io Import tabA one-off workbook, several sheets, checking the typesOne sheet per import
Export CSV, then bq loadRepeating loads, files already in Cloud StorageEverything in the next section
Google Sheets external tableA sheet other people keep editingRead live on every query, needs Drive access, see the Sheets article
Script (pandas to Parquet)Many workbooks, same layoutYou own the types

The Import tab

The Import tab accepts .xlsx and .xls from your machine or from Google Drive. A workbook with several sheets shows a Sheet select above the schema, and the sheet you pick is the one that loads. The first row of the sheet is the header (untick First row is header to get column_1 … column_n), and each cell is read as the text the sheet displays: a date formatted yyyy-mm-dd arrives as 2026-03-01, and a number formatted 00000 arrives as 02134. The detected schema is shown and editable before anything is written, and the load itself is an ordinary free load job. The BigQuery console’s Create table dialog takes only the five load formats above.

A script

For many workbooks with the same layout, convert them in code. This reads one sheet, keeps zip as text and writes Parquet:

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype={"zip": str})
df.to_parquet("orders.parquet", index=False)

bq load --source_format=PARQUET mydataset.orders orders.parquet then loaded it with the types from the file. The date column (datetime64) became TIMESTAMP, not DATE, and zip stayed STRING with its leading zero. Parquet files carry their types, so no autodetect is involved. See Loading data into BigQuery.

What Excel’s CSV export changes

Excel can save a sheet as CSV (Comma delimited), CSV UTF-8 or, in some locales, a semicolon-delimited file. The export writes what the cells display, only for the active sheet. We could not run Excel here, so we wrote CSV files in the shapes it produces and loaded each one with autodetect, then with a schema.

Encoding

FileResult
UTF-8 with a byte order mark (CSV UTF-8)loads; the first column is named order_id, with no stray characters
Windows-1252 (CSV (Comma delimited)), Café, €, curly quotesloads and reads back exactly, with no --encoding flag

The Windows-1252 result is better than Google’s docs promise. They say a file that is not UTF-8 is converted, and that ISO-8859-1 data “may not exactly match what you expect”. Our € and “quoted” were byte for byte right. The bq client does not accept --encoding=WINDOWS-1252; its choices are UTF-8, ISO-8859-1, UTF-16BE, UTF-16LE, UTF-32BE and UTF-32LE. If you see � in the table, the file was in a different encoding than you thought.

Semicolons and decimal commas

A European-locale Excel writes 12,50 for twelve and a half and uses ; between fields. We loaded this file:

id;price;ordered_on
1;12,50;01.03.2026
LoadResult
autodetect, no delimiter flagtwo columns named string_field_0 and string_field_1: the header is data, and the file was split at the decimal comma
autodetect, --field_delimiter=';'price is INTEGER and 12,50 is stored as 1250, with no error
schema with price:NUMERIC and ;fails: Invalid NUMERIC value: 12,50
schema with price:STRING and ;12,50, as text

The second row is the dangerous one. Autodetect read the comma as a thousands separator, and the load succeeded. Load decimal-comma columns as STRING and convert in SQL:

decimal comma to NUMERICOpen in Querylab.io
1234567891011
WITH exported AS (
  SELECT 1 AS id, '12,50' AS price UNION ALL
  SELECT 2, '1.234,50' UNION ALL
  SELECT 3, '7,00'
)
SELECT
  id,
  price,
  CAST(REPLACE(REPLACE(price, '.', ''), ',', '.') AS NUMERIC) AS price_num
FROM exported
ORDER BY id
Result in BigQuery
Rowidpriceprice_num
1112,5012.5
221.234,501234.5
337,007
3 rows

The 01.03.2026 column stayed STRING under autodetect. With a schema, d:DATE fails with Unable to parse; --date_format='DD.MM.YYYY' loads it as 2026-03-01.

Dates

Excel shows dates in your regional format, and the CSV carries that text. These are the cases we ran:

In the CSVAutodetectExplicit DATE column
2026-03-01DATEworks
3/1/2026, 12/31/2026DATE, 1 Marchfails: Unable to parse; works with --date_format='MM/DD/YYYY'
01/03/2026, 02/03/2026DATE, 3 January, 3 February
01/03/2026, 25/03/2026DATE, 1 March, 25 March

The last two rows are the same text, 01/03/2026, read as two different dates. Autodetect picks one format for the whole column from the rows it samples, and a single 25/03/2026 in the file turns the whole column day-first. If your dates are not ISO, state the format with --date_format and a DATE column, or load as STRING and use PARSE_DATE. Format elements that fail: M/D/YYYY returns Cannot find matched format element at 0 and FM elements are rejected with Format element 'FM' is not supported for parsing; MM/DD/YYYY accepted 3/1/2026.

Zip codes, IDs and long numbers

A zip code stored as a number reaches the CSV without its zero (2134), and autodetect makes the column INTEGER. If the cell is text, or has a number format such as 00000, the CSV has 02134; autodetect still makes it INTEGER and drops the zero. Either way, declare the column STRING. When the zero is already gone from the file, put it back:

restore leading zerosOpen in Querylab.io
12345678
WITH exported AS (
  SELECT 'Boston' AS city, 2134 AS zip UNION ALL
  SELECT 'New York', 10001 UNION ALL
  SELECT 'Newark', 7102
)
SELECT city, zip, LPAD(CAST(zip AS STRING), 5, '0') AS zip_text
FROM exported
ORDER BY city
Result in BigQuery
Rowcityzipzip_text
1Boston213402134
2New York1000110001
3Newark710207102
3 rows

In the General format, Excel shows a number with more than 11 digits, such as an account ID, in scientific notation (1.23457E+15), and the CSV contains that text. Autodetect made it FLOAT and stored 1.23457E15. Casting it to INT64 gives 1234570000000000: the remaining digits were lost when Excel wrote the file. Format the column as Number with no decimals, or as text, before you export. Excel keeps only 15 significant digits of any number, so IDs longer than that must be text in the sheet.

Numbers formatted as text

Excel writes a formatted number as it displays it. Autodetect is lenient about these, and an explicit schema is not:

In the CSVAutodetectFLOAT columnNUMERIC column
"1,234.50"FLOAT, 1234.51234.5fails: Invalid NUMERIC value: 1,234.50
"$1,234.50"FLOAT, 1234.51234.5fails
12%FLOAT, 0.120.12fails
(5)INTEGER, -5

So a FLOAT column absorbs separators, currency signs and percentages, while the better type for money fails. Loading as STRING and casting is the way to keep NUMERIC. A plain CAST fails on the separator:

CAST rejects a thousands separatorBigQuery rejects this query
1
SELECT CAST('1,234.50' AS NUMERIC) AS amount
Error in BigQuery

Invalid NUMERIC value: 1,234.50

This strips the symbols and handles parentheses, percentages and text that is not a number:

clean formatted numbers to NUMERICOpen in Querylab.io
12345678910111213141516171819
WITH exported AS (
  SELECT 1 AS id, '1,234.50' AS amount UNION ALL
  SELECT 2, '$7.00' UNION ALL
  SELECT 3, '(15.25)' UNION ALL
  SELECT 4, '12%' UNION ALL
  SELECT 5, 'n/a'
)
SELECT
  id,
  amount,
  SAFE_CAST(
    CASE
      WHEN STARTS_WITH(amount, '(') THEN '-' || REGEXP_REPLACE(amount, r'[^0-9.]', '')
      WHEN ENDS_WITH(amount, '%') THEN CAST(SAFE_CAST(REGEXP_REPLACE(amount, r'[^0-9.]', '') AS NUMERIC) / 100 AS STRING)
      ELSE REGEXP_REPLACE(amount, r'[^0-9.]', '')
    END AS NUMERIC
  ) AS value
FROM exported
ORDER BY id
Result in BigQuery
Rowidamountvalue
111,234.501234.5
22$7.007
33(15.25)-15.25
4412%0.12
55n/anull
5 rows

Extra columns, empty rows and errors

  • Trailing commas. A used range wider than the data exports as id,name,,,. The extra columns arrived as string_field_2, string_field_3, string_field_4, all NULL. Trailing empty rows loaded as all-NULL rows.
  • Title rows. A report with a title above the header (Monthly report,,) loaded with the title as the header-less first row and every column string_field_N. Delete the rows above the header before exporting, or use --skip_leading_rows.
  • Formula errors. #DIV/0! and #N/A are text in the CSV. A column that holds them is detected as STRING.
  • Line breaks inside a cell. A cell with Alt+Enter exports as a quoted value with a newline. Autodetect fails with Missing close quote character (").; --allow_quoted_newlines loads it.
  • Non-breaking spaces. Data pasted from the web often contains them. TRIM removes one at the start or end, but not one inside a value:
non-breaking spaces inside a valueOpen in Querylab.io
123456789101112
WITH exported AS (
  SELECT 1 AS id, 'Ada Lovelace ' AS name UNION ALL
  SELECT 2, 'Ada Lovelace' UNION ALL
  SELECT 3, 'Ada Lovelace'
)
SELECT
  id,
  LENGTH(TRIM(name)) AS len_trimmed,
  TRIM(name) = 'Ada Lovelace' AS matches_after_trim,
  REPLACE(TRIM(name), ' ', ' ') = 'Ada Lovelace' AS matches_after_replace
FROM exported
ORDER BY id
Result in BigQuery
Rowidlen_trimmedmatches_after_trimmatches_after_replace
1112truetrue
2212falsetrue
3312truetrue
3 rows

Things that only go wrong in the .xlsx

These matter when you read the workbook yourself, or in the Import tab.

Dates are serial numbers. The file stores 45717, and the format on the cell makes it look like a date. If a script or an export hands you the serial, subtract the epoch. Excel counts days from 1899-12-30:

Excel serial number to DATEOpen in Querylab.io
1234567891011
WITH exported AS (
  SELECT 1 AS order_id, 45717 AS ordered_on UNION ALL
  SELECT 2, 46082 UNION ALL
  SELECT 3, 61
)
SELECT
  order_id,
  ordered_on AS serial,
  DATE_ADD(DATE '1899-12-30', INTERVAL ordered_on DAY) AS ordered_date
FROM exported
ORDER BY order_id
Result in BigQuery
Roworder_idserialordered_date
11457172025-03-01
22460822026-03-01
33611900-03-01
3 rows

Serial 61 is 1900-03-01. Excel thinks 1900 had a 29 February (serial 60), so serials below 60 are one day off from this formula. A workbook saved with the 1904 date system counts from 1904-01-01 instead. A time of day is the fraction:

serial with a time of dayOpen in Querylab.io
12345678910111213
WITH exported AS (
  SELECT 1 AS event_id, 46082.5 AS happened_at UNION ALL
  SELECT 2, 46082.75 UNION ALL
  SELECT 3, 46083.0625
)
SELECT
  event_id,
  TIMESTAMP_ADD(
    TIMESTAMP '1899-12-30 00:00:00',
    INTERVAL CAST(ROUND(happened_at * 86400) AS INT64) SECOND
  ) AS happened_ts
FROM exported
ORDER BY event_id
Result in BigQuery
Rowevent_idhappened_ts
112026-03-01 12:00:00 UTC
222026-03-01 18:00:00 UTC
332026-03-02 01:30:00 UTC
3 rows

In a CSV, a date column that Excel formatted as General comes out as that serial and autodetect makes it INTEGER.

Formulas. A workbook stores the formula and its last calculated value. Readers take the stored value. A file produced by a library that writes formulas without calculating them (our openpyxl file) has no stored value, and the cell reads as empty until Excel opens and saves the file.

Merged cells. Only the top-left cell of a merged range holds a value; the others are empty. We read a sheet with A4:A5 merged and the second row’s region came back empty. To fill it down, you need a column that says which row came first, because a table has no row order of its own. Add a row_num column in the sheet (=ROW()) before you export:

fill merged cells downOpen in Querylab.io
12345678910111213
WITH exported AS (
  SELECT 1 AS row_num, 'EMEA' AS region, 10 AS qty UNION ALL
  SELECT 2, NULL, 5 UNION ALL
  SELECT 3, 'APAC', 8 UNION ALL
  SELECT 4, NULL, 2 UNION ALL
  SELECT 5, NULL, 1
)
SELECT
  row_num,
  LAST_VALUE(region IGNORE NULLS) OVER (ORDER BY row_num) AS region,
  qty
FROM exported
ORDER BY row_num
Result in BigQuery
Rowrow_numregionqty
11EMEA10
22EMEA5
33APAC8
44APAC2
55APAC1
5 rows

Several sheets. CSV holds one sheet. Export each sheet to its own file, or import each in the Import tab. A workbook with several sheets does not load as one table.

Header not on row 1. BigQuery’s skip_leading_rows skips whole rows, so a header on row 3 means --skip_leading_rows=3 with a schema (autodetect does not find it, as the title-row case above showed). The Import tab takes the first row of the sheet as the header.

Limits

Google’s docs limit a local file loaded through the console to 100 MB; a larger CSV goes to Cloud Storage first. The Import tab takes files up to 200 MB from Google Drive. The load limits (1,500 jobs per table per day, 100 MB per CSV row, 4 GB for a compressed file) are in Loading data into BigQuery.

What it costs

A batch load on the shared slot pool is free. You pay to store the table, and for a query on it as usual. Putting the data in a Google Sheet and reading it through an external table is billed for the bytes read on every query; see Google Sheets and BigQuery.

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