How to Add text qualifier and Escape char while Doing Bigquery Export

Question

I m trying to Export one table into GCP Csv File using below Query

EXPORT DATA
  OPTIONS ( uri = 'gs://Filepath/Filename*.csv',
    format = 'CSV',
    OVERWRITE = TRUE,
    header = TRUE,
    field_delimiter = ',') 
  AS (
  SELECT {Columns}
  FROM `tablename` order by {Columns} limit 10
  );

here as we can see we are able to Specify field_delimeter , but can not set textqualifier/ escap char - as it doesn't have this feature as per documentation

how to overcome with this issue? how to make perfect CSV export from big Query to GCS?

Were you ever able to solve this @jeegnesh? If you're not aware, BQ EXPORT DATA does add double quotes around any field that includes the delimiter. It's clumsy and ugly, but should work in most use cases. — Brad, Aug 03 '23 at 18:34

score 0 · Answer 1 · answered Apr 12 '23 at 01:08

String or byte literals are qouted with double " or single ' quotation marks, and also with a three single ''' or a three double """ quotation marks which can be also classified as text qualifier. It is based on the lexical structure and syntax documentation.

Regarding escape char, you can use REGEXP_CONTAINS and backslash(\) before your specified character to escape char. Here is a simple syntax:

SELECT
  {column}
FROM
  `projectname.datasetname.tablename`
WHERE REGEXP_CONTAINS(message, r"\.")

In this example code, dot(.) will be read as the literal value rather than as an expression.

How to Add text qualifier and Escape char while Doing Bigquery Export

1 Answers1