Error while reading data, error message: CSV table references column position 15, but line starting at position:0 contains only 1 columns

Question

I am new in bigquery, Here I am trying to load the Data in GCP BigQuery table which I have created manually, I have one bash file which contains bq load command -

bq load --source_format=CSV --field_delimiter=$(printf '\u0001') dataset_name.table_name gs://bucket-name/sample_file.csv

My CSV file contains multiple ROWS with 16 column - sample Row is

100563^3b9888^Buckname^https://www.settttt.ff/setlllll/buckkkkk-73d58581.html^Buckcherry^null^null^2019-12-14^23d74444^Reverb^Reading^Pennsylvania^United States^US^40.3356483^-75.9268747

Table schema -

When I am executing bash script file from cloud shell, I am getting following Error -

Waiting on bqjob_r10e3855fc60c6e88_0000016f42380943_1 ... (0s) Current status: DONE   
BigQuery error in load operation: Error processing job 'project-name- 
staging:bqjob_r10e3855fc60c6e88_0000ug00004521': 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.
Failure details:
- gs://bucket-name/sample_file.csv: Error while
reading data, error message: CSV table references column position
15, but line starting at position:0 contains only 1 columns.

What would be the solution, Thanks in advance

Answer 1

You are trying to insert wrong values to your table per the schema you provided

Based on table schema and your data example I run this command:

./bq load --source_format=CSV  --field_delimiter=$(printf '^') mydataset.testLoad  /Users/tamirklein/data2.csv

1st error

Failure details: - Error while reading data, error message: Could not parse '39b888' as int for field Field2 (position 1) starting at location 0

At this point, I manually removed the b from 39b888 and now I get this

2nd error

Failure details: - Error while reading data, error message: Could not parse '14/12/2019' as date for field Field8 (position 7) starting at location 0

At this point, I changed 14/12/2019 to 2019-12-14 which is BQ date format and now everything is ok

Upload complete. Waiting on bqjob_r9cb3e4ef5ad596e_0000016f42abd4f6_1 ... (0s) Current status: DONE

You will need to clean your data before upload or use a data sample with more lines with --max_bad_records flag (Some of the lines will be ok and some not based on your data quality)

Note: unfortunately there is no way to control date format during the upload see this answer as a reference