CODEWITHSUNDEEP
csvgoogle-bigquerygoogle-cloud-storage
Bhagesh Arora
Bhagesh Arora

Reputation: 547

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

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 -

BigQuery 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

Upvotes: 3

Views: 37332

Answers (4)

ZhefengJin
ZhefengJin

Reputation: 1112

Today, I have faced on the same problem.
The reason was that some field values contains CRLF. example

Upvotes: 0

Aung Myint Thein
Aung Myint Thein

Reputation: 400

We had the same problem while importing data from local to BigQuery. After testing so many options, we found out that the reason of the error is the mismatch of the number of columns in the data with the number of columns in the schema. Hope this helps some 🙂

Upvotes: 0

Nicat Nasiroiv
Nicat Nasiroiv

Reputation: 31

We had the same problem while importing data from local to BigQuery. After researching the data we saw that there data which starting \r or \s enter image description here

After implementing ua['ColumnName'].str.strip() and ua['District'].str.rstrip(). we could add data to Bg.

Thanks

Upvotes: 2

Tamir Klein
Tamir Klein

Reputation: 3642

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

Upvotes: 2

Related Questions