1

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!

Shahin Vakilinia
  • 345
  • 1
  • 11
Daniel
  • 11
  • 2
  • 1
    Right now BigQuery can only read the first sheet. Reading the whole spreadsheet is tracked by https://issuetracker.google.com/issues/35905674. – Hua Zhang Mar 05 '18 at 07:03
  • @HuaZhang thanks for your reply :) I'll follow the issue on the link you sent. – Daniel Mar 05 '18 at 17:05

2 Answers2

2

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.

Matt Laz
  • 155
  • 1
  • 7
0

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.

Shahin Vakilinia
  • 345
  • 1
  • 11