Office365 Excel as source for GCP BigQuery

Question

We are using Office365 Excel and manually creating some data that we need in BigQuery. What solution would you create to automatically load the data from this excel to a table in bq? We are not allowed to use Google Sheets (which would solve all our problems).

We use Matillion and GCP products.

I have no idea how to solve this, and I don't find any information about this, so any suggestion or idea is appreciated.

Cheers, Cris

score 0 · Answer 1 · answered Apr 26 '21 at 09:53

0

You can save your data as csv and then load them to BigQuery.

Then, you can load data by using one of the following:

The Google Cloud Console The bq command-line tool's bq load command The API The client libraries

Here you can find more details Loading data from local files

answered Apr 26 '21 at 09:53

M.Eshra

181
4

Thanks. But, we need to automate the process. Is there a way to automate the CSV download from excel? – i_am_cris Apr 26 '21 at 10:41
You just have to save as your excel as CSV – M.Eshra Apr 26 '21 at 11:54
We need to automate the process. To do it manually is easy. We are looking for a solution where the loading is automated. – i_am_cris Apr 26 '21 at 12:44
To automatize the conversion from XLS to CSV, this Stackoverflow thread might be useful for you: https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line – Javier Bóbeda May 10 '21 at 12:42
Thanks @JavierBóbeda – i_am_cris May 12 '21 at 09:18

score 0 · Answer 2 · answered May 10 '21 at 12:35

0

As a different approach, you can also try this other option: BigQuery: loading excel file

For this you will need to use Google Drive and federated tables.

Basically you will upload your Excel files to Google Drive with the option "Convert uploaded files to Google Docs editor format" checked in your settings, and upload to BigQuery from Google Drive.

answered May 10 '21 at 12:35

Alvaro Rodriguez

43
7

Thanks @alvaro didnt think about that. – i_am_cris May 12 '21 at 09:18

Office365 Excel as source for GCP BigQuery

2 Answers2