1

I am trying to analyze millions of search queries in a webshop and aggregate it in Google Data Studio. Unfortunately the XLSX in my Google Cloud Storage due to the errors:

Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details.

Error while reading data, error message: Error detected while parsing row starting at position: 0. Error: Bad character (ASCII 0) encountered.

I uploaded an XLSX file from my computer to my Google Cloud Storage and I tried to create a table from it with Google BigQuery and while importing it, I get those error messages.

I want to use BigQuery as a data source for my Google Data Studio and aggregate the search strings from the XLSX file in a way that I may see which queries occur regularly without any results and which search strings are being searched for most often.

I would very much appreciate you for helping me fix the error and find an efficient way to aggregate the data without long loading time necessary.

Nes Elm
  • 87
  • 1
  • 3
  • 12
  • 1
    You can't load an Excel file as if it's a CSV--they are different formats. See if the suggestion in https://stackoverflow.com/a/46799866/6253347 helps though. – Elliott Brossard May 27 '19 at 23:45

1 Answers1

3

The supported data formats does not include XLSX.

A good way to achieve this would be to convert the XLSX to a CSV, and then load it to BigQuery.

Here you have an example on how to use Python with pandas to converted the file. Once is converted, you can upload it to Google Cloud Storage, and then load it to BigQuery.

Alternatively, you could upload it directly from your computer without using Google Cloud Storage.

OscarR
  • 103
  • 6
  • Thanks so much! Eventually I could change it to CSV-format, put all fields on STRING NULLABLE and allow all errors. That helped me get it into Google Data Studio. :-) – Nes Elm Jul 10 '19 at 11:49