BigQuery error when loading csv file from Google Cloud Storage

8
votes

I'm trying to load the data of a csv file that is saved in GCS into BigQuery. The csv file is in the UTF-8 format and it contains 7 columns. I've specified these columns in the data scheme (all strings and nullable) and I've checked the contents of the csv file which seems fine.

When I try to load the data I get the following error:

Too many errors encountered. (error code: invalid) gs://gvk_test_bucket/sku_category.csv: CSV table references column position 1, but line starting at position:1750384 contains only 1 columns. (error code: invalid)

The weird thing is that the file only contains 680228 rows.

When I check the allow jagged lines options the table is being generated, but only the first column is filled with the entire comma separated string.

Can someone help me?

Example row

119470,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts,Long Sleeve Shirts

4
csvgoogle-bigquerygoogle-cloud-storage
Have you checked line 1750384 in the file? If you set number of allowed errors to 1, does it load? Can you share a sample row? - Graham Polley
Yes, that row is empty, the file only contains 680228 rows. There is no option to say how many rows the file contains right? I've set the number to 1 and after that to 100, but then it will return the same error for another row. - gvkleef
Here is a sample row: 119470,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts,Long Sleeve Shirts - gvkleef
Best to clarify the question by editing the question itself in this case, not adding comments. A cut/paste of a row or two into code sample in the editor will work well. BTW.. the last time something like this happened to me, I had a comma in the actual field which gummed the works. - Paulb
Thanks for your comment. I've set the number of allowed errors to 1000000000 and after the that the table is being filled with data. Is there a way to avoid errors on empty rows after the last record? Furthermore I also saw that, even with Allow Quoted Newlines set to true, quoted values containing comma's are not included in the table. - gvkleef

4 Answers

8
votes

For me, it was an issue with the presence of new line and carriage return characters, try replacing the special characters. I have replaced the characters using below code and it resolved the loading part.

df= df.applymap(lambda x: x.replace("\r"," "))
df= df.applymap(lambda x: x.replace("\n"," "))

I have used lambda function as I don't know which column is string in my case. If you are sure about columns then replace its column wise.

Try to replace the characters and it will work for you as well.

3
votes

You cannot have empty rows in your file without delimiters, otherwise BigQuery (and pretty much every other ingest engine) will think it's just one column.

For example, this will fail on row 3 with the error you describe:

119470,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts,Long Sleeve Shirts

119471,Fashion,Fashion Own,Womenswear,Womensswear Brands Other,Formal Shirts,Long Sleeve Shirts

This will succeed:

119470,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts,Long Sleeve Shirts
,,,,,,,    
119471,Fashion,Fashion Own,Womenswear,Womensswear Brands Other,Formal Shirts,Long Sleeve Shirts
1
votes

You either have an empty line 

119470,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts

119472,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts

Or a line with quotes

119470,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts
"119471,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts"
119472,Fashion,Fashion Own,Menswear,Menswear Brands Other,Formal Shirts

I think there is a bug in the BigQuery response. The line number in the error is in fact the number of character before the error.

0
votes

In my case, I was facing this issue due to an extra blank line after the last row of data. try removing that extra line and it should work.