Skip to main content
Skip table of contents

OPT_FILE_FORMAT_OPTIONS

Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Google BigQuery

OPT_FILE_FORMAT_OPTIONS is a load option that overrides file formatting options in file loads.

Affected command per supported database management system:

Target DBMS

Command

Snowflake

COPY INTO

Amazon Redshift

COPY

Azure SQL Database

BULK INSERT

Azure Synapse SQL

COPY

Google BigQuery

LOAD DATA INTO


See also:


Usage

OPTION
OPT_FILE_FORMAT_OPTIONS: sql_code

Notes

OPT_FILE_FORMAT_OPTIONS can be used with file loads.

OPT_FILE_FORMAT_OPTIONS is also supported in OVERRIDE_FILE_LOAD load steps where it will be resolved in place of the <file_format> variable.


Examples

Snowflake file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"'

File load executed in Snowflake:

SQL
COPY INTO ...
FROM ...
FILE_FORMAT=(TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"');

Amazon Redshift file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF

File load executed in Redshift:

SQL
COPY ...
FROM ...
TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF;

Azure SQL Database file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n'

File load executed in SQL database:

SQL
BULK INSERT ...
FROM ...
WITH ( ... ,
FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n');

Azure Synapse SQL file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
CREDENTIAL=(IDENTITY='Managed Identity'), FILE_TYPE = 'CSV', FIELDQUOTE = '"', FIELDTERMINATOR=',', COMPRESSION = 'Gzip', ENCODING = 'UTF8', FIRSTROW = 2 

Note that OPT_FILE_FORMAT_OPTIONS overrides the entire WITH clause so also the CREDENTIAL argument must be defined.

File load executed in SQL database:

SQL
COPY INTO ...
FROM ...
WITH (CREDENTIAL=(IDENTITY='Managed Identity'), FILE_TYPE = 'CSV', FIELDQUOTE = '"', FIELDTERMINATOR=',', COMPRESSION = 'Gzip', ENCODING = 'UTF8', FIRSTROW = 2);

Google BigQuery file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8'

File load executed in BigQuery:

SQL
LOAD DATA INTO ...
FROM FILES(
  FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8', 
  uris=[...]
);

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.