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

Solution 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

Solution 2

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

Comments

[Tamir Klein]

Can you provide an example of your source file and tell us if your 1st row contains the headers or not

[Bhagesh Arora]

@Tamir Klein No, There is no header in CSV file,Above I already shown 1st line of CSV file

[Tamir Klein]

Sorry, I see your example. I also see that the 2nd field is defined as an Integer in your schema but in your example, you are attempting to insert a String (3b9888). You can try an use --max_bad_records flag and avoid the default value which is zero(0). See this link for more information cloud.google.com/bigquery/docs/reference/…

[Bhagesh Arora]

I specified --max_bad_records=20 in my bash file. But still I am getting error - [gs://project-name-staging/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. [gs://project-name-staging/sample_file.csv] Error while reading data, error message: CSV table references column position 15, but line starting at position:195 contains only 1 columns.

[Bhagesh Arora]

Here I am trying to load only one Row for testing purpose.

[Tiago Medici]

probably you have empty rows