"Could not parse field" error while loading a csv file in Google Bigquery

While loading CSV fields into a BigQuery table you may stumble upon the below error message:

Could not parse 'columnname' as double for field columnname (position 27)
 starting at location 0 (error code: invalid)

Solution:

#1 If you are loading into pre-created tables, Check the file

• Check if the CSV file has headers. Chances are Bigquery might be trying to load the headers as well in the table. Try re-loading the CSV but remove the headers first (or skip them using the Header rows to skip option)
• Check the data. Make sure there are no incompatible data in any of the rows. You can split the file into smaller chunks and test it out.
• If you encounter this error often, try changing the target datatype in the table to STRING, so it allows any data without throwing any errors

#2 If you are using auto-detect to create the table DDL

BigQuery uses the first 100 rows of a file to determine the column names as well as the datatype for those columns. So, if the data in the subsequent rows (eg. Row 200) the load job may fail saying that the datatype is different. If the first 100 rows cannot be deterministic of the datatype, It is better to pre-create the table using DDL commands and not use Auto-detect functionality.

For example, If your first 100 rows has NULLS or empty string, Bigquery may not be able to choose the datatype accurately. In which case, you may stumble on this error. It is advisable to pre-create the tables before attempting to load the files

mCollins

posted on 03 Jan 22