How to Connect Google BigQuery to Excel with DataDirect

In this blog I will walk through the steps on how to make an ODBC connection from MS Excel to Google BigQuery and followed by importing the data from Google BigQuery to MS Excel.

Progress DataDirect has extensive experience of building ODBC connectors for MS Excel.

With the DataDirect suite of ODBC drivers, you can connect to a wide variety of high-value data sources in the market today. Whether you are using a traditional Relational Database Management System or a Big Data, Cloud or REST based sources, DataDirect has you covered.

Progress DataDirect ODBC drivers are the industry standard for ODBC drivers, providing the advanced functionality, performance, and reliability developers need to quickly deliver high-quality, database-independent applications.

Connecting to a Progress DataDirect ODBC driver from MS Excel is very straightforward. In this tutorial, we walk you through connecting MS Excel to Google BigQuery. These same steps can be used to connect MS Excel to any DataDirect ODBC driver.

Back to top

This tutorial assumes that you have ODBC drivers installed and a data source configured. If you have not yet done these steps, please follow these instructions. Ensure that Microsoft Excel and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Excel installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed.

1. Download and install the Progress DataDirect Google BigQuery ODBC driver.

2. Configure a data source for your database. See the DataDirect Google BigQuery ODBC driver documentation for assistance on setting up your data source.

3. Progress DataDirect Google BigQuery driver supports both OAUTH 2.0 authentication and Service Account authentication. While configuring your DSN you can choose the desired authentication mechanism to connect to Google BigQuery.

4. Please follow the link to the details on configuring OAUTH 2.0 and Service Account authentication with Google BigQuery.

Back to top

When the driver installation and configuration is completed, you can import data from Google BigQuery using MS Excel following the mentioned steps

Open MS Excel’s new workbook and navigate to the “Data” tab
To import data click on “Get Data” and navigate to “From Other Sources”-> ”From ODBC”
Select the Google BigQuery data source name (DSN) you want to connect to. Just in case if the ODBC DSN has not been set up, you can leverage the Advanced Options dialog box. Additionally, you can enter an SQL statement which will be executed immediately after the successful connection with Google BigQuery
As mentioned in the Prerequisites, you would have already configured the necessary credentials to authenticate and connect with Google BigQuery in the DSN. Therefore, you can select Default or Custom and click Connect. We will leave the “Credential connection string properties” text box blank.
As mentioned in the Prerequisites, you would have already configured the necessary credentials to authenticate and connect with Google BigQuery in the DSN. Therefore, you can select Default or Custom and click Connect. We will leave the “Credential connection string properties” text box blank
Once the import of the data completes the data will be available in the Workbook for you to work on.
Once you have the data in the workbook, you can start analyzing that with various analytical tools available as part of Microsoft excel e.g., below image shows recommended Pivot table reports that can be build up on the populated data