Google BigQuery

BigQuery export to CSV: query results, tables, Sheets and Excel

BigQuery has five ways out to a CSV: Save results to your computer, to Google Drive or to Google Sheets; the EXPORT DATA statement to Cloud Storage; and an extract job for a whole table, from the console or bq extract. This guide gives the size limit and the cost of each, flattens nested columns the way CSV needs, checks EXPORT DATA with a dry run, and says what to use when the CSV has to go out every week.

Google BigQueryIevgen Krasovytskyi11 min read

BigQuery export to CSV: query results, tables, Sheets and Excel

There are five ways to get BigQuery data into a CSV. Which one fits depends on two things: whether you are exporting a query result or a whole table, and how big it is.

Route Exports Goes to Size limit Cost
Save results > local file a query result your computer (CSV or JSON) 10 MB nothing beyond the query
Save results > Google Drive a query result the root of My Drive (CSV or JSON) 1 GB nothing beyond the query
Save results > Google Sheets a query result a new sheet in My Drive Sheets’ own limits nothing beyond the query
EXPORT DATA statement a query result Cloud Storage 1 GB per file, many files the query is billed
Extract job: console Export or bq extract a whole table Cloud Storage 1 GB per file, many files free in the shared pool, up to 50 TiB a day

Two rules apply to every route that writes CSV: nested and repeated columns (STRUCT, ARRAY) cannot go into a CSV, and nothing exports straight to Excel. Both are covered below.

Note: Originally published in August 2025. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026, and every EXPORT DATA statement was validated by a dry run, not run.

Reading to get this done?

The table used in the examples

The table used in the examples

Most real tables that refuse to export to CSV have nested data in them, so the sample has some: a customer record and an items array.

WITH orders AS (
  SELECT 1001 AS order_id, DATE '2026-10-01' AS order_date,
         STRUCT('anna@example.com' AS email, 'DE' AS country) AS customer,
         [STRUCT('mug' AS sku, 2 AS qty, 12.50 AS price),
          STRUCT('tee', 1, 20.00)] AS items
  UNION ALL SELECT 1002, DATE '2026-10-01',
         STRUCT('ben@example.com', 'FR'),
         [STRUCT('cap', 1, 15.00)]
  UNION ALL SELECT 1003, DATE '2026-10-02',
         STRUCT('cara@example.com', 'DE'),
         [STRUCT('mug', 1, 12.50),
          STRUCT('cap', 2, 15.00),
          STRUCT('tee', 3, 20.00)]
)

Three orders, six order lines. Paste it before the queries below and they run as written.

Save query results: local CSV, Google Drive or Google Sheets

Save query results: local CSV, Google Drive or Google Sheets

This is the route for most “BigQuery export query results to CSV” needs. Run the query in the console, click Save results above the result grid, and choose where it goes (Google’s Export query results to a file and writing query results pages, read in October 2026):

  • Local file. CSV or newline-delimited JSON, saved to your browser’s download folder. The maximum download size is 10 MB.
  • Google Drive (Beta). CSV or JSON, always saved to the root of My Drive as bq-results-[TIMESTAMP]-[RANDOM].csv. The result must be 1 GB or less.
  • Google Sheets. A new spreadsheet named results-YYYYMMDD in My Drive. The first time, Google asks you to allow BigQuery to write to your Drive. A cell holds at most 50,000 characters; longer values arrive blank.
  • Cloud Storage. Opens the same dialog as a table export, described further down.

None of the first three works from bq or the API: they are console-only (Google’s Download and save query results, read in October 2026). If you sort the result by a column in the grid first, the file keeps that order.

Will it fit in 10 MB?

The limit is counted on the data, not on rows, so check before you hit it. This query runs on Google’s public GA4 sample and estimates the size of a CSV with four columns:

WITH result AS (
  SELECT event_date, event_name, user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_location
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210125' AND '20210131'
)
SELECT
  COUNT(*) AS result_rows,
  ROUND(SUM(BYTE_LENGTH(CONCAT(
    event_date, ',', event_name, ',', user_pseudo_id, ',',
    IFNULL(page_location, ''), '\n'))) / POW(1024, 2), 1) AS approx_csv_mb
FROM result;
Range result_rows approx_csv_mb
one day (_TABLE_SUFFIX = '20210131') 26,489 2.5
one week (the query above) 273,507 26.4

One day downloads to your computer. A week does not: save it to Google Drive instead, which takes up to 1 GB, or to Cloud Storage. The estimate leaves out quotes and the header row, so treat anything close to 10 MB as too big. The week scanned 86 MB of the public table.

Hand this to your AI

Your query result is too big for Save results, and the nested columns won't go into a CSV at all.

Paste this into your AI assistant. It picks the export route that fits your result's size, flattens your nested columns and writes the EXPORT DATA statement for your own query.

The prompt

export my BigQuery query result to CSV: flatten the nested and repeated columns, pick the route that fits its size, and dry-run the EXPORT DATA statement first

Nested and repeated columns: flatten first

Nested and repeated columns: flatten first

Google is direct about it: BigQuery “doesn’t support exporting nested or repeated data” to CSV, and a STRUCT that looks flat still counts as nested (Export query results to a file, read in October 2026). Saving SELECT * from the orders table as CSV returns that error; JSON keeps the nesting. There are two ways to fix the query for CSV.

One row per array element. Pick record fields with dot notation and UNNEST the array:

SELECT
  o.order_id,
  o.order_date,
  o.customer.email AS customer_email,
  o.customer.country AS country,
  item.sku,
  item.qty,
  item.price
FROM orders AS o, UNNEST(o.items) AS item
ORDER BY o.order_id, item.sku;
order_id order_date customer_email country sku qty price
1001 2026-10-01 anna@example.com DE mug 2 12.5
1001 2026-10-01 anna@example.com DE tee 1 20.0
1002 2026-10-01 ben@example.com FR cap 1 15.0
1003 2026-10-02 cara@example.com DE cap 2 15.0
1003 2026-10-02 cara@example.com DE mug 1 12.5
1003 2026-10-02 cara@example.com DE tee 3 20.0

Three orders became six rows. Every order-level column now repeats once per line: whoever opens the CSV and counts rows gets six orders, and anyone who sums an order-level value (shipping, an order total) multiplies it. Say in the file name or the first column what one row is.

One row per order, the array packed into a cell. Aggregate the array inside the row, or turn it into a JSON string:

SELECT
  order_id,
  customer.email AS customer_email,
  (SELECT STRING_AGG(CONCAT(i.sku, ' x', CAST(i.qty AS STRING)), ', '
          ORDER BY i.sku) FROM UNNEST(items) AS i) AS items_list,
  (SELECT SUM(i.qty * i.price) FROM UNNEST(items) AS i) AS order_total,
  TO_JSON_STRING(items) AS items_json
FROM orders
ORDER BY order_id;
order_id customer_email items_list order_total items_json
1001 anna@example.com mug x2, tee x1 45.0 [{"sku":"mug","qty":2,"price":12.5},{"sku":"tee","qty":1,"price":20}]
1002 ben@example.com cap x1 15.0 [{"sku":"cap","qty":1,"price":15}]
1003 cara@example.com cap x2, mug x1, tee x3 102.5 [{"sku":"mug","qty":1,"price":12.5},{"sku":"cap","qty":2,"price":15},{"sku":"tee","qty":3,"price":20}]

The row count matches the order count, the total is right, and the JSON column keeps every detail for whoever needs it. GA4 export tables are built the same way, with event_params as an array; the UNNEST guide for GA4 event parameters has the patterns, and array functions covers the rest.

EXPORT DATA: query results to Cloud Storage

EXPORT DATA: query results to Cloud Storage

For anything over 1 GB, or for an export you want written down as code, use the EXPORT DATA statement. It runs a query and writes the result as files in a Cloud Storage bucket:

EXPORT DATA OPTIONS (
  uri = 'gs://your-bucket/exports/order-items-*.csv',
  format = 'CSV',
  header = true,
  overwrite = true
) AS
SELECT
  o.order_id,
  o.order_date,
  o.customer.email AS customer_email,
  o.customer.country AS country,
  item.sku,
  item.qty,
  item.price
FROM orders AS o, UNNEST(o.items) AS item
ORDER BY o.order_id, item.sku;

With the orders CTE placed after AS, a dry run accepted this statement and returned the seven flat columns. The options that matter for CSV (EXPORT DATA statement, read in October 2026):

Option Default Note
uri required must contain one *; BigQuery replaces it with a 12-digit number per file
format required CSV, JSON, AVRO or PARQUET
header false the opposite of bq extract, where headers are on by default
field_delimiter , use ';' or '\t' for European Excel or tab-separated files
compression none GZIP for CSV
overwrite false without it, the statement fails if the destination bucket path is not empty; with it, old files that match the wildcard but are not overwritten stay in the folder

The dry runs turned up four things worth knowing before you run one for real:

  1. A URI without a wildcard is rejected. uri = 'gs://your-bucket/exports/orders.csv' returned “Option ‘uri’ value must be a wild card URI.” Even a ten-row export needs the *.
  2. Wildcard tables are rejected. The same GA4 query over events_* returned “EXPORT DATA statement cannot reference meta tables in the queries.” Name the daily table (events_20210131) or put the result in a regular table first. Google’s page says the query also cannot read INFORMATION_SCHEMA views.
  3. A dry run does not catch nested columns. SELECT * on orders into a CSV passed the dry run, with customer and items in the schema as RECORD columns. The CSV rule is enforced when the job runs, so flatten before you rely on the dry run.
  4. A dry run does not check the bucket. gs://your-bucket does not exist, and every statement above still passed. Bucket, permissions and location are checked at run time.

ORDER BY is kept across files: the sorted result is spread over the files in sequence. Google advises against LIMIT in EXPORT DATA because it often makes the export slow. File sizes are not guaranteed; if each file must stay under a size, Google’s export guide suggests partitioning the data by a random export_id and exporting each partition (read in October 2026).

You pay for the query. The four-column GA4 export over one day, events_20210131, would process 8,239,720 bytes (about 7.9 MiB), according to its dry run. The writing of files itself is not billed; storage in the bucket is.

Export a whole table: console or bq extract

Export a whole table: console or bq extract

When the data is already a table, an extract job copies it to Cloud Storage without running a query.

In the console (Export table data to Cloud Storage, read in October 2026): open the table in the Explorer, click Export / sync > Cloud Storage, and in the Export to Google Cloud Storage dialog set the location, Export format (CSV) and Compression. For CSV the console offers GZIP or nothing. The job appears in Job history with the type EXTRACT.

From the command line:

bq extract \
  --location=US \
  --destination_format=CSV \
  --compression=GZIP \
  --field_delimiter=',' \
  --print_header=true \
  'your-project:your_dataset.orders_flat' \
  'gs://your-bucket/exports/orders_flat-*.csv.gz'

CSV is the default format and --print_header is true by default; they are spelled out here so the command reads clearly. --location must match the table’s. Client libraries (Python’s extract_table, and the Java, Go and Node equivalents) start the same job from code.

An extract job has the same CSV rule: a table with RECORD or REPEATED columns has to be flattened into a new table first, or exported as JSON, Avro or Parquet, which keep nesting.

Limits in one place

Limits in one place

From Google’s export guide and quotas page (both read in October 2026):

Limit Value
Local download of query results 10 MB
Query results saved to Google Drive 1 GB
One exported file 1 GB of table data; above that, use a wildcard URI
Wildcard URIs per extract job 500
Tables per extract job one
Extract bytes per day, shared pool 50 TiB per project
Extract jobs per day, shared pool 100,000 per project
Row order guaranteed only with EXPORT DATA plus ORDER BY
Rows loaded during the job not included; start a new job

Two more from the same page: the dataset and the bucket must be in the same location, and Google “strongly” recommends turning off Bucket Lock and Soft Delete on the bucket during the export, because a retention policy that blocks a rewrite fails the job and adds charges.

What it costs

What it costs

  • Extract jobs (console Export, bq extract, client libraries): “There is no processing charge for exporting data from a BigQuery table using an extract job” (BigQuery pricing, read in October 2026). The free shared pool has a hard limit of 50 TiB a day; to go past it, assign a PIPELINE reservation (billed at capacity rates) or use EXPORT DATA or the Storage Read API instead. Cross-region exports pay data transfer.
  • EXPORT DATA: you pay for the query, on-demand or capacity, as for any query. In exchange it is exempt from the extract quotas.
  • Save results: no export charge; you already paid for the query that produced the result.
  • Cloud Storage: the files themselves are billed as storage. BigQuery pricing covers query costs in detail.

Permissions

Permissions

To export a table you need bigquery.tables.export (in BigQuery Data Viewer, Data Editor, Data Owner and Admin) and bigquery.jobs.create (in BigQuery User and Job User). To write to the bucket you need storage.objects.create and storage.objects.delete, which come with Storage Object Admin or Storage Admin. Storage Object Creator alone does not have the delete permission. The IAM roles guide explains where each role is granted.

BigQuery export to Excel

BigQuery export to Excel

Excel has no route of its own in BigQuery. A CSV from Save results opens in Excel, and for a European locale a ; delimiter in EXPORT DATA or bq extract saves a round of text-to-columns. For data that has to stay current, an ODBC driver with Power Query or the OWOX Data Marts add-in for Excel pulls it into a workbook directly; the four options are compared in how to connect BigQuery to Excel.

When the CSV goes out every week

When the CSV goes out every week

A manual export is fine once. The third time someone asks for “the same CSV, updated”, it becomes a job: run the query, save, rename, send, and hope nobody opens last week’s file.

OWOX Data Marts replaces that loop. You publish the query as a Data Mart once, in your own BigQuery project, and it is delivered on a schedule to Google Sheets, Excel, Data Studio, email or Slack. OWOX Data Marts runs your SQL as written and does not transform the data, so the number in the sheet is the number your query returns, and the flattening decision above is made once, in SQL, instead of in every download. If you are weighing it against Google’s own spreadsheet link, see OWOX Data Marts vs Connected Sheets.

Going the other way, from a CSV into BigQuery, is covered in loading data into BigQuery.

Hand this to your AI

Your query result is too big for Save results, and the nested columns won't go into a CSV at all.

Paste this into your AI assistant. It picks the export route that fits your result's size, flattens your nested columns and writes the EXPORT DATA statement for your own query.

The prompt

export my BigQuery query result to CSV: flatten the nested and repeated columns, pick the route that fits its size, and dry-run the EXPORT DATA statement first

FAQ

Frequently Asked Questions

Can BigQuery export a CSV on a schedule?

The console exports (Save results, Export to Cloud Storage) are one-off. For a recurring file, run an EXPORT DATA statement or a bq extract command from your own scheduler, or publish the query as a Data Mart in OWOX Data Marts and have it delivered to Google Sheets, Excel or Data Studio on a schedule.

How big is the export limit for BigQuery?

Query results download to your computer up to 10 MB and save to Google Drive up to 1 GB. Exports to Cloud Storage write up to 1 GB per file, so larger exports need a wildcard URI that splits them into many files; an extract job can have up to 500 wildcard URIs.

How do I read a CSV file in BigQuery?

Load it into a table from your computer, Google Drive or Cloud Storage with the Create table dialog or bq load, using schema auto-detection or a schema you define. You can also define an external table over a CSV in Cloud Storage and query it without loading it.

What is the size limit for a CSV exported from BigQuery?

A single exported file holds up to 1 GB of table data. Above that, use a URI with one asterisk, such as gs://bucket/file-*.csv, and BigQuery writes numbered files whose sizes vary.

Why won't BigQuery export nested or repeated fields to CSV?

CSV is flat, so BigQuery returns an error for STRUCT and ARRAY columns. Select record fields with dot notation and UNNEST arrays to get one row per element, or pack the array into one cell with `STRING_AGG` or `TO_JSON_STRING`; JSON, Avro and Parquet exports keep the nesting.

How many files does a wildcard export create?

BigQuery decides the number of files from the data size, replacing the asterisk with a 12-digit number for each file, and the file sizes vary. If each file must stay under a set size, partition the data by a random `export_id` and export each partition separately.

Can I export BigQuery results directly to my computer or Google Drive?

Yes, for query results: click Save results in the console to download a CSV or JSON file up to 10 MB, or to save one up to 1 GB to the root of My Drive. Whole tables export only to Cloud Storage, and these Save results options are not available from bq or the API.

Who wrote this

Ievgen Krasovytskyi

Ievgen Krasovytskyi Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.