Loading csv data into Big Query via API

Question

I'm trying to upload a CSV file to a big query database, however i keep getting the following message.

RuntimeError: [{'reason': 'invalid', 'location': 'kid=70943:mkey=customer_encrypt_cns/file-00000000', 'message': 'Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the error stream for more details.'}, {'reason': 'invalid', 'location': 'kid=70943:mkey=customer_encrypt_cns/file-00000000', 'message': 'Error while reading data, error message: CSV table references column position 1, but line starting at position:0 contains only 1 columns.'}]

The data in the csv matches the schema and as a test im only uploading 5 rows of 8columns e.g.

2018-02-02  x   0.1 2.4 0   0   0.88    0.4
2018-02-03  y   0.1 3   0   0   0.87    0.21
2018-02-04  z   0.1 2.8 0   0   0.86    0.21
2018-02-05  a   0.1 2.4 0   0   0.91    0.21
2018-02-06  b   0.1 1.9 0   0   1.00    0.4

Why does it say i only have 1 column?

This looks like a tab separated file instead of a comma separated file. Are you specifying the appropriate separator? — Elliott Brossard
– Elliott Brossard, Commented Apr 25, 2018 at 15:48
CSV is a comma delimited file. but in your data sample there are no Commas. Are you sure you are uploading a comma delimited file ? — Ratnesh Sharma
– Ratnesh Sharma, Commented Apr 26, 2018 at 5:13

Steeve · Accepted Answer · 2018-05-03 20:59:26Z

2

It says that you have 1 column because your data isn't formatted/parsed into a proper CSV file as there's no clear field delimiter. I've properly formatted the data to be tab-separated and was able to load it into BigQuery allowing me to obtain 5 rows of 8 columns.

Here is the data used:

2018-02-06  x   0.1 2.4 0   0   0.88    0.4
2018-02-03  y   0.1 3   0   0   0.87    0.21
2018-02-04  z   0.1 2.8 0   0   0.86    0.21
2018-02-05  a   0.1 2.4 0   0   0.91    0.21
2018-02-06  b   0.1 1.9 0   0   1.00    0.4

answered May 3, 2018 at 20:59

Steeve

3933 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gautier Couvrat · Accepted Answer · 2018-04-30 13:24:42Z

0

Can you provide the request body ?

Looks like BigQuery cannot parse the file with the provided options. Regarding you file, you should specify "fieldDelimiter":"\t" option. You may review BigQuery API documentation for details.

BigQuery also supports schema auto-detection for comma (,), pipe(|) and tab(\t) separated fields.

answered Apr 30, 2018 at 13:24

Gautier Couvrat

1

Collectives™ on Stack Overflow

Loading csv data into Big Query via API

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related