Table of Contents

Google BigQuery is a robust, serverless, and highly scalable data warehouse that allows businesses to analyze massive datasets in real-time. For data professionals working in mid-market companies, the ability to connect Excel to BigQuery opens up new possibilities for combining the advanced data manipulation capabilities of Excel with the powerful analytics engine of BigQuery to pass to downstream applications like Tableau or Power BI. This connection allows you to push Excel data into BigQuery for storage and further processing.

In this tutorial, we will cover the different methods to connect Excel to BigQuery, ensuring smooth data transfer, effective reporting, and efficient data analysis. Next, we will explore step-by-step ways for each method, their limitations, and answer common FAQs that data analysts might encounter while working with Excel and BigQuery.

Why Connect Excel to BigQuery?

Excel is an essential tool for day-to-day data analysis, and connecting it to BigQuery allows you to:

  • Analyze large datasets from BigQuery in a familiar Excel environment.

  • Leverage the scalability of BigQuery without overloading Excel with large data volumes.

  • Run ad-hoc SQL queries on BigQuery data.

  • Push data from Excel into BigQuery for further analysis and visualization, machine learning models, or long-term storage.

This combination allows data professionals to work with the best of both worlds—BigQuery’s power for handling large-scale big data and Microsoft Excel’s flexibility for analysis and reporting.

Methods to Connect Excel to BigQuery

1. Using Google Sheets as an Intermediary

Google Sheets workbook provides a simple, no-code method for moving data from Excel to BigQuery. By uploading Excel data to Google Sheets and then connecting Google Sheets to BigQuery, you can easily transfer the data.

Steps:

  • Convert Excel to Google Sheets:

    • Open Google Sheets, go to File > Import, and upload your Excel file (.xlsx).

    • The data will be converted into a Google Sheets format.

  • Connect Google Sheets worksheet to BigQuery:

    • In Google Sheets, navigate to Data > Data connectors > Connect to BigQuery.

    • Authenticate your Google account and select the relevant BigQuery project and dataset.

  • Export Data to BigQuery account:

    • Select the data range in Google Sheets that you want to connect BigQuery.

    • Use the connector tool to push the data directly into your BigQuery dataset.

Limitations:

  • Data size limitations: Google Sheets has a 10 million cell limit.

  • Manual process: Not ideal for recurring transfers or large-scale automation.

2. Using BigQuery Web UI for CSV Upload

If your data is in Excel, you can save it as a CSV file and upload it directly to BigQuery using BigQuery's Web UI. This method works for simple data transfers where you don't need automated processes or complex configurations.

Steps:

  • Convert Excel to CSV:

    • In Excel Spreadsheets, save the file as a CSV (File > Save As > CSV (Comma delimited)).

  • Upload CSV to BigQuery:

    • Open the BigQuery web interface and navigate to your project.

    • Click Create Table, then select Upload as the source and upload your CSV file.

    • Specify the target dataset and table in BigQuery where the data should be stored.

    • Configure schema settings (either auto-detect or manually configure schema fields).

  • Import Data:

    • Complete the configuration and click Create Table. The data from the CSV file will be uploaded into the BigQuery table.

Limitations:

  • Manual process: Needs manual conversion to CSV and setup each time.

  • No automation: Not ideal for regular, recurring data transfers.

3. Using BigQuery Data Transfer Service

BigQuery Data Transfer Service (DTS) automates data imports into BigQuery. While DTS natively supports Google products like Google Ads and YouTube, it can be extended for Excel data by first loading the data into Google Sheets or Google Cloud Storage.

Steps:

  • Convert Excel Data to CSV and Upload to Google Cloud Storage:

    • Save your Excel file as a CSV file.

    • Upload the CSV file to a Google Cloud Storage (GCS) bucket.

  • Configure BigQuery Data Transfer:

    • In the BigQuery Web UI, navigate to BigQuery Data Transfers.

    • Create a new data transfer, selecting Google Cloud Storage as the source.

  • Schedule the Transfer:

    • Specify the GCS bucket and file location.

    • Set the target dataset in BigQuery and configure the table schema.

    • Schedule the transfer (one-time or recurring) to move the data from GCS to BigQuery.

Limitations:

  • Requires using Google Cloud Storage: You must store the CSV file in GCS first.

  • Setup complexity: Involves more configuration using advanced options compared to direct uploads.

4. Using Google Apps Script

If your Excel data is stored in Google Sheets, you can use Google Apps Script to automate the data transfer from Sheets to BigQuery. Google Apps Script is a lightweight scripting platform that allows you to write JavaScript code within Google Sheets.

Steps:

  • Convert Excel to Google Sheets:

    • Import your Excel data into Google Sheets by going to File > Import.

  • Open Google Apps Script service account:

    • In Google Sheets, go to Extensions > Apps Script to open the scripting interface.

  • Write a Google Apps Script to Transfer Data:

    • Write a script that retrieves data from the Google Sheet and inserts it into BigQuery.

function loadDataToBigQuery() {
  var projectId = 'your_project_id';
  var datasetId = 'your_dataset_id';
  var tableId = 'your_table_id';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var bigqueryData = [];
  for (var i = 1; i < data.length; i++) {
    bigqueryData.push({
      field1: data[i][0],  // Replace with your column mappings
      field2: data[i][1]
    });
  }
    var bigqueryTable = {
    projectId: projectId,
    datasetId: datasetId,
    tableId: tableId,
    rows: bigqueryData.map(row => ({json: row}))
  };
BigQuery.Tabledata.insertAll(bigqueryTable);
}

  • Run the Script:

    • Execute the script to transfer the data from Google Sheets to BigQuery.

Limitations:

  • Limited scalability: Best for small to medium datasets.

5. Using Python and BigQuery API

For advanced users familiar with coding, you can write Python scripts to automate the transfer of data from Excel to BigQuery using the BigQuery API. This method offers the most flexibility and can handle large datasets and complex workflows.

Steps:

  • Install Required Python Libraries:

    • Install the required libraries to work with Excel and BigQuery:

pip install pandas google-cloud-bigquery openpyxl

  • Write a Python Script:

    • Use pandas to read the Excel file and google-cloud-bigquery to load data into BigQuery.

import pandas as pd
from google.cloud import bigquery

# Read Excel file into pandas DataFrame
df = pd.read_excel('your_file.xlsx')

# Authenticate and initialize BigQuery client
client = bigquery.Client()

# Specify BigQuery dataset and table
dataset_id = 'your_project.your_dataset'
table_id = f"{dataset_id}.your_table"

# Load DataFrame into BigQuery
job = client.load_table_from_dataframe(df, table_id)
job.result()  # Wait for the job to complete
print(f"Loaded {job.output_rows} rows into {table_id}.")

  • Run the Script:

    • The script will read the Excel file, convert it to a DataFrame, and load it into the specified BigQuery table.

Limitations:

  • Requires coding knowledge: Suitable only for users comfortable with Python.

  • Manual script execution: Unless automated with a scheduler, the script must be run manually.

6. Using Third-Party Data Integration Platforms

Third-party integration platforms like Integrate.io provide low-code solutions for transferring data from Excel to BigQuery. These platforms allow you to automate and schedule data flows, making the process easy for non-technical users.

Steps:

  • Sign Up for a Data Integration Platform:

  • Connect Your Data Sources:

    • Set up connections for both your Excel data (stored locally or in cloud storage like Google Drive) and your BigQuery project.

  • Configure the Data Pipeline:

    • Define the data pipeline, specifying the Excel file as the source and BigQuery as the destination.

    • Map the fields between the Excel data and BigQuery table.

  • Configure automation of the Data Transfer:

    • Schedule recurring data transfers or trigger transfers based on specific events or conditions.

Conclusion

Connecting Excel and other sources to Google BigQuery provides data professionals with the ability to analyze large datasets efficiently while leveraging the power of BigQuery’s cloud-based data warehousing. Whether you’re looking for a simple, one-time data transfer or an automated solution for regular data syncs and get data to BigQuery, the methods outlined above cover a wide range of use cases. To get started with automating your Excel to BigQuery now, schedule a time to speak with one of our Solution Engineers here. 

FAQs

1. Can I automate data sync between BigQuery and Excel?

  • Yes, using third-party tools like Integrate.io, you can set up automated workflows to sync data between BigQuery and Excel on a schedule or based on triggers. Power Query in Excel also supports manual refreshes, but full automation requires additional configuration.

2. Why is my BigQuery data taking a long time to load into Excel?

  • The performance of data transfers from BigQuery to Excel depends on the size of the dataset, your network speed, and the complexity of your queries. Using filters and running optimized queries in BigQuery can help reduce data loading time. Additionally, Excel has a row limit of 1,048,576 rows, so ensure that you're not exceeding this limit.

3. Can I push data from Excel into BigQuery?

Yes, you can push data from Excel into BigQuery using third-party integration platforms like Integrate.io or by manually uploading CSV files to BigQuery after saving them from Excel. Some platforms also allow you to automate this process for regular uploads.