Data between quotation marks and field delimiter

Question

In the example below, the last line is not loaded. I get an error message:

Data between close double quote (") and field separator: 

This looks like an error, since all data between pipe symbols should be treated as one field.

Pattern: one: string, two: string, three: string, four: string

Upload file:

 This | is | test only | to check quotes second | line | "with quotes" | no text third line | with | "start quote" and | a word after quotes 

Processing the first and second lines. But not the third.

---

Update:

Can someone explain why the next job is except the third line ?

 This | is | test only | to check quotes second | line | "with quotes" | no text third line | with | "start quote" and | a word after quotes forth line | enclosed | {"GPRS","MCC_DETECTED":false,"MNC_DETECTED":false} | how does this work? fifth line | with | {"start quote"} and | a word after quotes 

It could be some kind of bizarre explanation. From an end user perspective, this is absurd.

Answer 1

On the CSV RFC4180 page: "If double quotation marks are used for applying fields, then the double quotation mark appearing inside the field must have slipped, preceded by another double quote."

You might want to do this:

 This | is | test only | to check quotes second | line | "with quotes" | no text third line | with | " ""start quote"" and " | a word after quotes 

Read more about our CSV input format here .

Answer 2

-- quote worked great.

 bq load --source_format CSV --quote "" --field_delimiter \t --max_bad_records 10 -E UTF-8 destination table Source files 

Answer 3

You can use other flags when loading data. I used the bq tool with the following flags

 bq load -F , --source_format CSV --skip_leading_rows 1 --max_bad_records 1 --format csv -E UTF-8 yourdatset gs://datalocation. 

Answer 4

• V2 API https://cloud.google.com/bigquery/docs/reference/v2/jobs#configuration.load.quote

• Bq command --quote: A character character used to include entries. Default: "To avoid quotation marks at all, use an empty string.

Answer 5

Try this as an alternative:

• Download the MySQL backup files to the Cloud SQL instance.
• Read data in BigQuery directly from MySQL.

More like

• https://medium.com/google-cloud/loading-mysql-backup-files-into-bigquery-straight-from-cloud-sql-d40a98281229

Answer 6

Try loading every time with bq shell.

I had to download 1,100 columns. When I tried to use the console with all the error options, it produced a lot of errors. Ignoring errors in the console means losing records.

Therefore, I tried with the shell and managed to load all the records.

Try the following:

 bq load --source_format CSV --quote "" --field_delimiter \t --allow_jagged_rows --ignore_unknown_values --allow_quoted_newlines --max_bad_records 10 -E UTF-8 {dataset_name}.{table_name} gs://{google_cloud_storage_location}/* {col_1}:{data_type1},{col_2}:{data_type2}, .... 

Recommendations:

 https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv#bigquery_load_table_gcs_csv-cli https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv#csv-options