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.
| Command | Result |
|---|---|
bq load --source_format=CSV --autodetect | CSV 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 schema | Too 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_format | the same CSV errors, plus the client’s note data will be treated as CSV format by default |
--source_format=NEWLINE_DELIMITED_JSON | Failed to parse JSON: Unexpected token; Parser terminated before end of string |
--source_format=PARQUET | Input file is not in Parquet format. |
--source_format=AVRO | The Apache Avro library failed to parse the header with the following error: Invalid data file. Magic does not match |
--source_format=ORC | The Apache Orc library failed to parse file metadata from file tail with error: Invalid ORC postscript length |
--source_format=XLSX | rejected 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
| Route | Best for | Watch out for |
|---|---|---|
| Querylab.io Import tab | A one-off workbook, several sheets, checking the types | One sheet per import |
Export CSV, then bq load | Repeating loads, files already in Cloud Storage | Everything in the next section |
| Google Sheets external table | A sheet other people keep editing | Read live on every query, needs Drive access, see the Sheets article |
| Script (pandas to Parquet) | Many workbooks, same layout | You 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
| File | Result |
|---|---|
| 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 quotes | loads 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
| Load | Result |
|---|---|
| autodetect, no delimiter flag | two 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:
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
| Row | id | price | price_num |
|---|---|---|---|
| 1 | 1 | 12,50 | 12.5 |
| 2 | 2 | 1.234,50 | 1234.5 |
| 3 | 3 | 7,00 | 7 |
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 CSV | Autodetect | Explicit DATE column |
|---|---|---|
2026-03-01 | DATE | works |
3/1/2026, 12/31/2026 | DATE, 1 March | fails: Unable to parse; works with --date_format='MM/DD/YYYY' |
01/03/2026, 02/03/2026 | DATE, 3 January, 3 February | |
01/03/2026, 25/03/2026 | DATE, 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:
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
| Row | city | zip | zip_text |
|---|---|---|---|
| 1 | Boston | 2134 | 02134 |
| 2 | New York | 10001 | 10001 |
| 3 | Newark | 7102 | 07102 |
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 CSV | Autodetect | FLOAT column | NUMERIC column |
|---|---|---|---|
"1,234.50" | FLOAT, 1234.5 | 1234.5 | fails: Invalid NUMERIC value: 1,234.50 |
"$1,234.50" | FLOAT, 1234.5 | 1234.5 | fails |
12% | FLOAT, 0.12 | 0.12 | fails |
(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:
SELECT CAST('1,234.50' AS NUMERIC) AS amountInvalid NUMERIC value: 1,234.50
This strips the symbols and handles parentheses, percentages and text that is not a number:
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
| Row | id | amount | value |
|---|---|---|---|
| 1 | 1 | 1,234.50 | 1234.5 |
| 2 | 2 | $7.00 | 7 |
| 3 | 3 | (15.25) | -15.25 |
| 4 | 4 | 12% | 0.12 |
| 5 | 5 | n/a | null |
Extra columns, empty rows and errors
- Trailing commas. A used range wider than the data exports as
id,name,,,. The extra columns arrived asstring_field_2,string_field_3,string_field_4, allNULL. Trailing empty rows loaded as all-NULLrows. - Title rows. A report with a title above the header (
Monthly report,,) loaded with the title as the header-less first row and every columnstring_field_N. Delete the rows above the header before exporting, or use--skip_leading_rows. - Formula errors.
#DIV/0!and#N/Aare text in the CSV. A column that holds them is detected asSTRING. - 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_newlinesloads it. - Non-breaking spaces. Data pasted from the web often contains them.
TRIMremoves one at the start or end, but not one inside a value:
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
| Row | id | len_trimmed | matches_after_trim | matches_after_replace |
|---|---|---|---|---|
| 1 | 1 | 12 | true | true |
| 2 | 2 | 12 | false | true |
| 3 | 3 | 12 | true | true |
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:
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
| Row | order_id | serial | ordered_date |
|---|---|---|---|
| 1 | 1 | 45717 | 2025-03-01 |
| 2 | 2 | 46082 | 2026-03-01 |
| 3 | 3 | 61 | 1900-03-01 |
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:
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
| Row | event_id | happened_ts |
|---|---|---|
| 1 | 1 | 2026-03-01 12:00:00 UTC |
| 2 | 2 | 2026-03-01 18:00:00 UTC |
| 3 | 3 | 2026-03-02 01:30:00 UTC |
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:
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
| Row | row_num | region | qty |
|---|---|---|---|
| 1 | 1 | EMEA | 10 |
| 2 | 2 | EMEA | 5 |
| 3 | 3 | APAC | 8 |
| 4 | 4 | APAC | 2 |
| 5 | 5 | APAC | 1 |
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.