how to avoid [Missing close double quote (") character.] error in google bigquery

Question

I am receiving the error below when I want to upload a data to google big query from a csv file. I have read many similar questions but still couldn't find a simple answer around how to fix this. Any help is much appreciated. Regards,

Error while reading data, error message: Error detected while parsing row starting at position: 1451. Error: Missing close double quote (") character.

Could you please add data from row 1451? Is there really an entry without closing double quote character? Or do you think that the problem is in BigQuery itself? — Kyrylo Bulat, Jan 18 '21 at 22:23
I checked all the entries and there's no double quote character. — Cagdas Kanar, Jan 18 '21 at 22:27
Is there an entry that contains an additional double quote inside the column value? Like: "first column"|"second " column"|"third column"? It might cause a problem too. Will the answer help you, if it helps you to avoid the error, but the "error" entry will be missing in the table? — Kyrylo Bulat, Jan 18 '21 at 22:33

score 12 · Answer 1 · answered Jan 18 '21 at 23:40

I think I found a solution. When I try to upload the data to google bigquery directly it doesn't show those options in the advanced section, however when I first upload the file into google storage area and then import the data into google query from cloud, I can see different options in the advanced section. I simply enabled the "Allow quoted newlines" in advanced section and it made the trick.

score 9 · Answer 2 · edited Aug 29 '22 at 04:25

9

Encountered the same issue and figure out on my own as well. It has something to do with the Advanced Options under the Write Preference change it to "Append to table" and checked the "Quoted Newlines"

error

edited Aug 29 '22 at 04:25

Markus Meyer

3,327
10
22
35

answered Aug 24 '22 at 09:50

oj_udz08

91
1
4

1

Quoted newlines is what fixed it for me! – bordeaux Aug 31 '22 at 11:51

score -1 · Answer 3 · edited Jan 07 '22 at 14:39

-1

I think I found the answer. While file size is definitely a reason, another is to choose the right Table type while uploading your bucket to the Bigquery.

edited Jan 07 '22 at 14:39

Suraj Rao

29,388
11
94
103

answered Jan 07 '22 at 14:33

Ashish M

1
1

how to avoid [Missing close double quote (") character.] error in google bigquery

3 Answers3