How to Build Excel reports on Google BigQuery database using the DataDirect Google BigQuery driver for ODBC
Introduction
This blog will walk you through the steps to make an ODBC connection from MS Excel to Google BigQuery, followed by importing the data from Google BigQuery to 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 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.
Prerequisites
This tutorial assumes that you have ODBC drivers installed and a data source configured. Ensure that MS Excel and ODBC Driver architecture matches. For example, if you have a 64-Bit MS Excel installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed. Most likely, your Excel would be a 32-bit version and you would need a 32-bit driver. If you have not yet done these steps, please follow these instructions:
1. Download and install the Progress DataDirect Google BigQuery ODBC driver for your database using the 15-day trial software. In this tutorial, we are connecting to Google BigQuery.
2. Configure a data source for your database. See the DataDirect documentation for assistance with setting up your data source. The sample below uses a data source.
Configure Connection to Google BigQuery Driver Using the ODBC Data Source Administrator
1. After the DataDirect Google BigQuery ODBC driver has been installed, open ODBC Administrator to configure the connection and click on Add.
2. Choose the “DataDirect X.X Google BigQuery” driver and click on “Finish.”
3. You should now see the ODBC Google BigQuery Driver Setup pop-up window launched.
4. Add your Data Source Name, project, dataset, and other credentials, and click on “Apply” and then “Test Connect.”
5. A pop-up window will confirm that the connection is established.
Configure the DataDirect ODBC Google BigQuery Driver with Microsoft Excel
When the driver installation and configuration are completed, you can import data from Google BigQuery using MS Excel by following the mentioned steps:
1. 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.”
2. Select the Google BigQuery data source name (DSN) you want to connect to. Just in case 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.
3. A navigator will open. Choose the data you want to import and load the data into the workbook.
4. Once the data import is done, it will be available in the Workbook for you to work on.
5. Once you have the data in the workbook, you can start analyzing that with various analytical tools available as part of Microsoft Excel.
For example, the image below shows recommended Pivot table reports that can be built up on the populated data.
Conclusion
Get started today with a free 15-day trial of Progress DataDirect Google BigQuery ODBC driver and connect MS Excel to all your data! For any help, please feel free to contact us.