How to Export Data from BigQuery to Excel?

Image Source: https://www.cardinalpath.com/blog/how-to-use-the-bigquery-connector-for-excel

Hi,

As Trendyol DWH team, it may sometimes be necessary to send our data to business units as an Excel file.

We wrote a small Python code to take action quickly when such a request was received.

With this Python code; we run the SQL given in BigQuery and convert this SQL result to Excel. Finally, it sends the Excel file as an attachment in the mail. :)

So let’s move on to the code;

Let me explain the code; code takes two parameters while running. The first is the SQL file path, the second is the YAML config file path.

• getYamlParams(yaml_param)
  This function takes the given yaml config file path and provides the necessary variables to be set.

• runSQL(sql_param)
  This function executes SQL in the given SQL file path in BigQuery and returns the result as Pandas DataFrame.

• write2Excel(today, excel_file_name, df)
  This function creates Pandas DataFrame as an Excel file with Pandas ExcelWriter function.
• sendEmail(today, yaml_params, row_count)
  This function performs the mailing process by taking the Excel file and the mail information we provide in the config file. When the mail sending is finished, it deletes the created Excel file.

If your query result is greater than the maximum number of lines per sheet, the code will not work. This problem can also be solved with a small script in the code. Everything is in your hands :)

With this code, we were able to send our BigQuery data as Excel by preparing a small config and SQL file. :)

Have a nice coding! :)