Connecting to BigQuery From Excel and Python

This tutorial will show you how to connect to BigQuery from Excel and Python using ODBC Driver for BigQuery. Also, if you’re completely new to ODBC, read this tutorial to learn how to set up a DSN for the driver.

Connecting to BigQuery from Excel

There are several ways to connect to BigQuery from Excel through ODBC, including the Data Connection Wizard in older versions of Excel. This tutorial provides the instructions for connecting to the data source with Get & Transform (Power Query) in Excel 2016, 2019.

1. Click the Data tab in Excel.
2. Expand the Get Data dropdown and click From Other Sources > From ODBC.
3. In the From ODBC dialog window, select the previously configured dialog box and click OK.
4. In the window that appears, click Default or Custom, then Connect.
5. In the next window, select the table to retrieve data from and click Load.
6. The data from the table will be displayed in an Excel spreadsheet.

Connecting to BigQuery from Python

This guide assumes that you have already set up a Python development environment and installed the pyodbc module with the pip install pyodbc command.
You can type the code directly in the Python Shell or add the code to a .py file and then run the file. Below is a simple script to connect to BigQuery, insert records and query the dataset.

1. Import the pyodbc module and create a connection to BigQuery.

   import pyodbc
cnxn = pyodbc.connect('DRIVER={Devart ODBC Driver for Google BigQuery};Project ID=myprojectid;DataSet Id=mydatasetid;Refresh Token=myrefreshtoken')

2. Insert a new record to the table_name

   cursor = cnxn.cursor()
cursor.execute("INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, 'value3')")

3. Retrieve and print the data

   cursor = cnxn.cursor()
cursor.execute("SELECT * FROM table")
row = cursor.fetchone()
while row:
print (row)
row = cursor.fetchone()

To view the list of tools that can work with BigQuery through ODBC, visit the compatibility page.