How to Connect BigQuery to Excel

Progress DataDirect has extensive experience in building ODBC drivers for accessing data sources from Microsoft 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 source, 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.

You can use the Progress DataDirect for ODBC for Google BigQuery driver to import Google BigQuery data into Microsoft Excel. Importing Google BigQuery data into Microsoft Excel is a 3-step process:

1. Determine your Excel version


2. Install and configure the Progress DataDirect for Google BigQuery for ODBC driver


3. Connect to Google BigQuery from Excel and import its data
   
   

Determine whether your version of Excel is a  32-bit installation or a 64-bit installation:

1. Open your workbook in Excel.
2. On the File menu, click Account, and then click About Excel. The About Microsoft Excel window appears.

The version information is available at the top of the About Microsoft Excel window.

1. Navigate to the Google BigQuery ODBC Driver page and select the driver you want to download.
• If you are using 32-bit Excel, select the Windows 32-bit driver.
• If you are using 64-bit Excel, select the Windows 64-bit driver.
2. When prompted, provide your details, such as name and email address; then, click DOWNLOAD.
3. Extract the driver zip file contents into a temporary directory; then, double-click the installer program:

For 32-bit drivers: PROGRESS_DATADIRECT_ODBC_8.0_WIN_32_INSTALL.exe
For 64-bit drivers: PROGRESS_DATADIRECT_ODBC_8.0_WIN_64_INSTALL.exe

4. Follow the prompts to complete the installation.
5. Start the ODBC Administrator by selecting its icon from the Progress DataDirect for ODBC program group.
6. On the ODBC Administrator, select the User DSN tab, and then configure a data source. If you want to configure:
• An existing data source, select the data source name and click Configure. The driver Setup dialog box appears.
• A new data source, click Add to display a list of installed drivers. Select the Google BigQuery driver and click Finish. The driver Setup dialog box appears.

7. Specify values for the following connection options to authenticate using the OAuth 2.0 authentication; then, click Test Connect.
• Data Source Name
• Project
• Dataset
• Authentication Method
• Client ID
• Client Secret
• Refresh Token

Note: To know more about the connection options and authentication methods supported by the driver, refer to the user’s guide.

1. Open your workbook in Excel.
2. From the Data menu, select Get Data>From Other Sources>From ODBC.
3. The From ODBC dialog appears. Select your data source from the Data Source Name (DSN) drop-down list; then, click OK.



Note: If you want to connect using a connection string and want to execute a SQL statement immediately after establishing the connection, you can use the Advanced options section.

4. Select the Default or Custom tab and then click Connect. Leave the Credential connection string properties field blank, as the required connection options have already been configured in the DSN.



5. The Navigator window appears.



From the list, select the tables you want to access. A preview of your data appears in the pane on the right. Optionally, click Edit to modify the results using the Query Editor. Refer to the Microsoft Excel product documentation for detailed information on using the Query Editor.

6. Load your data:
   1. Click Load to import your data into your worksheet. Skip to the end.
   2. Click Load>Load To to specify a location to import your data. Proceed to the next step.
7. The Import Data window appears. Select the desired view and insertion point for the data. Click OK.

Result: The data for the selected tables is imported into Excel at the insertion point.

To start a trial of DataDirect for Google BigQuery to import data into Microsoft Excel please visit: https://www.progress.com/odbc/google-bigquery.