I am wondering how I can upload an .xlsx file to BigQuery. I get data in this format once a week and would like to upload it manually. So far I have been copying this data to Google Sheet and from there with a connector to BigQuery, however Google Sheet is starting to hang up more and more with this amount of data and I am looking for another solution. I know that the easiest way would be to convert the data to csv, but the problem is the data itself contains a lot of commas, semicolons and other characters so every time I tried to convert it to csv the data was wrong. I'm wondering if there is an easy way to upload xlsx or convert it so that the data can be easily uploaded to BigQuery.

CodePudding user response：

Save your excel file with a different change your delimiter such as pipe ¦ or tilde ~

The value for the separator that is used within Excel when you save a csv is stored with

• Region (not region settings)

• Additional setting

• List Separator

Change to something that your data will not be using, apply inside Customise Format and apply again inside the Region dialog. Next time you save a csv this list separator will be used.

In BigQuery, you can then define your custom separator on table load under additional setting as shown below