CODEWITHSUNDEEP
google-drive-apigoogle-bigquery
Daniel
Daniel

Reputation: 11

How to specify sheet when importing a file from google drive into bigquery

When I create a new table in bigquery by importing data from an excel file in my google drive I am unable to specify the sheet from which I want the data to be from.
I've already been here: https://cloud.google.com/blog/big-data/2016/05/bigquery-integrates-with-google-drive
But it wasn't very helpful since it doesn't even mention the possibility of a file having multiple sheets (which is basically 99% of excel files)
Has anyone run into the same issue, how have you solved it?
Thanks!

Upvotes: 1

Views: 2071

Answers (2)

Matt Laz
Matt Laz

Reputation: 230

You can now specify the sheet and/or range of a Google Sheet in the new BigQuery UI which you would like to use as your federated data source.

Specify sheet range

From Google:

Optionally indicate which sheet and cell range in the Google Sheets spreadsheet to create a table from. You can specifiy: [sheet_title] for the whole sheet, or [sheet_title]![top_left_cell_id]:[bottom_right_cell_id]for a rectangular cell range in a sheet, such as "Sheet1!A1:B20". When this is not specified, the first sheet in the spreadsheet is used.

Upvotes: 2

Shahin Vakilinia
Shahin Vakilinia

Reputation: 355

As Daniel mentioned and as it explained here, BigQuery only imports the first worksheet of the spreadsheet. One more complicated way to import the data from Google Drive to BigQuery is to use worksheet ID (gid) in Google-drive SDK to import the excel file different spreadsheets(like the way that has been done here) and then use BigQuery Client Libraries to export data to your desired table.

Upvotes: 0

Related Questions