All resources
Google BigQuery (GBQ) doesn’t require additional maintenance costs and processes your data in less than a minute. You can learn about uploading data to GBQ from CSV and JSON files, using the BigQuery API, Google Analytics 4, or from other Google services in our previous article. You can also learn more about setting up the OWOX BI BigQuery Reports Add-on and automating reports in Google Sheets based on information from Google BigQuery. Today, we’ll tell you how to upload data from BigQuery to your all-time favorite – Excel.
To learn more about the benefits and ease of use of Google’s cloud-based data storage, read our detailed overview of BigQuery features. You can also watch the video “What is BigQuery?” by DevBytes, and read about five reasons why building reports is better in BigQuery.
Note: This post was originally published in Feb 2020 and was completely updated in March 2024 for accuracy and comprehensiveness.
BigQuery, developed by Google, is a cloud-based big data analytics platform tailored for large-scale enterprises. It enables the swift querying of vast datasets, often comprising thousands of rows, thanks to its powerful processing capabilities. By integrating BigQuery with your applications, you gain the ability to ingest and analyze extensive data volumes in real time, facilitating up-to-the-minute insights for business initiatives.
Signing up for an account grants access to BigQuery's features, with the option of a monthly flat-rate payment plan. This plan offers businesses a predetermined data import and querying capacity, supporting multiple concurrent project analyzes.
Connecting BigQuery to Microsoft Excel opens up enhanced capabilities for businesses by leveraging Excel's robust data analytics and visualization tools alongside BigQuery's scalable cloud data warehouse for handling vast datasets in real time. This integration offers several key benefits:
This synergy not only amplifies data handling efficiency but also enriches the analytical outcomes available to businesses, making it a strategic move for leveraging data-driven insights.
Connecting Google BigQuery with Microsoft Excel opens up a box of opportunities for data analysts and professionals to harness the combined power of BigQuery's extensive data analytics capabilities and Excel's versatile data manipulation and visualization tools.
There are several methods available to establish this connection, each tailored to meet different needs and technical requirements. From utilizing Power Query – a feature within Excel that simplifies data import and transformation – to leveraging Directly Import data from Google BigQuery to Excel Using IQY file for a more direct data import, the choice of method depends on factors such as the need for real-time data updates, ease of setup, and specific project requirements.
This introduction explores the various approaches to seamlessly import data from BigQuery into Excel, enhancing data analysis and reporting workflows.
If necessary, you can always create a new key.
You can also expire your current key using the Revoke Key button or in your Google profile settings.
Note that if your query exceeds 256 characters, Excel won’t run it. In this case, you should split your query into parts and insert them into adjacent cells.
It’s ready! Your BigQuery data will now appear in Excel.
As an alternative way to connect to Excel, you can use the Magnitude Simba ODBC driver for BigQuery, which is explained down below. Detailed instructions can be found on YouTube: How to Connect Google BigQuery to Microsoft Excel.
Connecting BigQuery to Excel using the API involves a technical process that leverages BigQuery's REST APIs for integration with external applications, necessitating a good grasp of programming and API usage. Given its technical nature, this method is best suited for those with engineering or analytical expertise, offering a powerful way to leverage their skills for advanced data integration tasks. Here's how to establish this connection:
Here's a simplified Python code example for fetching data from BigQuery and exporting it to Excel:
from google.cloud import bigquery
import pandas as pd
from io import BytesIO
# Authenticate using the service account JSON key
client = bigquery.Client.from_service_account_json('path/to/your_service_account.json')
# Configure the job to export data in CSV format
job_config = bigquery.ExtractJobConfig()
job_config.destination_format = 'CSV'
job_config.print_header = False
# Specify your GCS bucket and file path
bucket_name = 'your-bucket-name'
destination_uri = f'gs://{bucket_name}/your-data.csv'
# Reference your BigQuery table
table_ref = client.dataset('your_dataset').table('your_table')
# Initiate the export job
extract_job = client.extract_table(table_ref, destination_uri, job_config=job_config)
# Assuming the CSV file is now in your GCS bucket, download and convert it to a DataFrame
bucket = client.get_bucket(bucket_name)
blob = bucket.blob('your-data.csv')
csv_string = blob.download_as_string()
df = pd.read_csv(BytesIO(csv_string))
# Export the DataFrame to an Excel file
df.to_excel('your-data.xlsx')
This code snippet outlines fetching data from BigQuery, converting it to a CSV format, downloading it, and then exporting it to Excel. Modify the code according to your specific requirements. For comprehensive information, consult the BigQuery REST API documentation.
Power Query enhances Excel by enabling connections to various external data sources, a capability seamlessly integrated into Excel under the "Get & Transform Data" section in the Data tab.
For this example, we'll utilize the ODBC driver as a BigQuery Excel connector, one of the data source options supported by Excel's Get Data feature, to connect to Google BigQuery. Google, in partnership with Simba – a leader in data connectivity solutions, provides ODBC and JDBC drivers for connecting BigQuery with Excel and also other applications.
To connect your BigQuery account to Excel, first, ensure you download and install the most recent version of the Google BigQuery ODBC driver on your system. Following the installation, proceed with the steps below to establish a connection with your BigQuery account.
After setting up the ODBC driver as a BigQuery connector for Excel on your system, you're ready to utilize it as a data source for exporting data from BigQuery into Excel. Begin by opening a new Excel worksheet, and then proceed with the following steps to establish a connection between BigQuery and Excel.
Here's how you can load the data from BigQuery to Excel:
You've now adeptly navigated the process of connecting BigQuery to Excel via ODBC and have successfully imported your data into Excel.
Automatically updating data from BigQuery in Excel is a powerful feature that ensures your analysis is always based on the most current information. After you have successfully imported data from BigQuery into Excel, it's crucial to maintain the freshness of this data to reflect any changes or updates that occur in the BigQuery database.
Excel facilitates this through its ability to refresh data both manually and automatically. Manual refreshes are useful for ad-hoc updates, while automatic refreshes are ideal for maintaining up-to-date data without constant manual oversight. Setting up automatic data refreshes can save significant time and effort, allowing you to focus on analysis rather than data management.
This feature is especially beneficial in dynamic environments where data changes frequently, ensuring that your Excel workbook reflects the latest data with minimal effort. After importing data from BigQuery into Excel, ensuring the data remains current is essential.
Manually refreshing data in Excel involves a simple process to ensure your workbook contains the latest information from BigQuery. Navigate to the Data tab and locate the refresh icon positioned above the "Refresh All" option within the "Queries & Connections" section.
Clicking this icon initiates an immediate data update. Alternatively, for a quicker method, you can utilize the shortcut key Alt + F5. This action forces Excel to fetch and update the imported data, ensuring your analysis is based on the most current dataset available.
For continuous data updates without manual intervention, Excel allows you to set up automatic data refresh at specified intervals. Here’s how you can activate this feature:
With these steps, Excel will automatically refresh and update the BigQuery data at the intervals you've set, ensuring your data analysis remains up-to-date without needing manual refreshes.
Now you know how to load data from Google BigQuery cloud storage to Excel.
And if you want to learn more about GBQ and other analytics tools, subscribe to our newsletter. Every month you’ll get useful tips for modern marketers and analysts.
🚀 BigQuery data structure in Google: How to get started with cloud storage
☕️ Top 6 BigQuery Visualization Tools
🚀 Automate Reports in Google Sheets Using Data from Google BigQuery
🚀 Standard SQL in Google BigQuery: Advantages and Examples of Use in Marketing
