The columns of my imported table are not recognized and are named as "string_field_0" ? What can I do?
Question Description:
I have imported a table (via Google Sheets integration, Sharepoint, CSV, Excel, S3 ...) but the headers are not recognized, instead, all columns are named as "string_field_0", "string_field_1", "string_field_2" etc. What can I do?
Answer:
BigQuery can't differentiate between the header and actual valid rows. BigQuery infers headers by comparing the first row of the file with other rows in the dataset. If the first line contains only strings, and the other lines contain other data types, BigQuery assumes that the first row is a header row. Otherwise, BigQuery assumes the first row is a data row, and assigns generic column names such as "string_field_1".
Workaround:
A workaround to resolve this, is adding a dummy column in your sheet with numbers like:
Read more here:
- https://cloud.google.com/bigquery/docs/schema-detect#schema_auto-detection_for_csv_data
- https://medium.com/google-cloud/bigquery-create-table-from-google-sheets-causing-incorrect-column-names-string-field-0-134f6ecd3fc8