# 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. Source: https://docs.querylab.io/bigquery/loading-excel/ Updated: 2026-11-04 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](https://docs.querylab.io/bigquery/google-sheets/)). Querylab.io's [Import](https://docs.querylab.io/data-management/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 ` | 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: ```python 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](https://docs.querylab.io/bigquery/loading-data/). ## 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: ```text 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: **decimal comma to NUMERIC** ```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 ``` Result in BigQuery (3 rows): | id | price | price_num | | --- | --- | --- | | 1 | 12,50 | 12.5 | | 2 | 1.234,50 | 1234.5 | | 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: **restore leading zeros** ```sql 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 (3 rows): | city | zip | zip_text | | --- | --- | --- | | Boston | 2134 | 02134 | | New York | 10001 | 10001 | | 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: **CAST rejects a thousands separator** ```sql SELECT CAST('1,234.50' AS NUMERIC) AS amount ``` BigQuery rejects this query: ```text 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 NUMERIC** ```sql 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 (5 rows): | id | amount | value | | --- | --- | --- | | 1 | 1,234.50 | 1234.5 | | 2 | $7.00 | 7 | | 3 | (15.25) | -15.25 | | 4 | 12% | 0.12 | | 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 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 value** ```sql 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 (3 rows): | id | len_trimmed | matches_after_trim | matches_after_replace | | --- | --- | --- | --- | | 1 | 12 | true | true | | 2 | 12 | false | true | | 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: **Excel serial number to DATE** ```sql 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 (3 rows): | order_id | serial | ordered_date | | --- | --- | --- | | 1 | 45717 | 2025-03-01 | | 2 | 46082 | 2026-03-01 | | 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: **serial with a time of day** ```sql 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 (3 rows): | event_id | happened_ts | | --- | --- | | 1 | 2026-03-01 12:00:00 UTC | | 2 | 2026-03-01 18:00:00 UTC | | 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: **fill merged cells down** ```sql 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 (5 rows): | row_num | region | qty | | --- | --- | --- | | 1 | EMEA | 10 | | 2 | EMEA | 5 | | 3 | APAC | 8 | | 4 | APAC | 2 | | 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 MiB 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](https://docs.querylab.io/bigquery/loading-data/). ## 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](https://docs.querylab.io/bigquery/google-sheets/). 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) - [Batch loading data](https://cloud.google.com/bigquery/docs/batch-loading-data) - [Using schema auto-detection](https://cloud.google.com/bigquery/docs/schema-detect) - [Create Google Drive external tables](https://cloud.google.com/bigquery/docs/external-data-drive)