Solved: BigQuery error in load operation: Field delimiter must be a single character

How to use Extended ASCII Characters as file delimiters in Bigquery load files:

While trying to load a file into Bigquery with a delimiter 0x1f. Bigquery throws the below error message:

BigQuery error in load operation: Field delimiter must be a single 
character, found:"U+001F".

Solution: Using Control Characters as delimiters

Specify field delimiter within a printf command like shown below:

bq load --autodetect --field_delimiter=$(printf '\x1f') [DATASET].[TABLE] gs://[BUCKET]/simple.csv

More examples:

Using Control M character as the delimiter:

bq load --location=US --max_bad_records 100000 --field_delimiter=$(printf '║') [DATASET].[TABLE] gs://[BUCKET]/simple.csv

victor

posted on 21 Apr 19