Skip to main content
Skip table of contents

OPT_FILE_FORMAT_OPTIONS

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

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 INTO

Azure Synapse DW

COPY INTO

Google BigQuery

LOAD DATA INTO

Databricks SQL

COPY 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 Synapse:

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

Microsoft Fabric file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
FILE_TYPE = 'CSV', CREDENTIAL=(IDENTITY='SOURCE_DATA_WITH_SIGNATURE', SECRET='<secret>'), FIELDQUOTE = '\"', FIELDTERMINATOR=';', ENCODING = 'UTF8', FIRSTROW = 1

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

File load executed in Fabric:

SQL
COPY INTO ...
FROM ...
WITH (FILE_TYPE = 'CSV', CREDENTIAL=(IDENTITY='SOURCE_DATA_WITH_SIGNATURE', SECRET='<secret>'), FIELDQUOTE = '\"', FIELDTERMINATOR=';', ENCODING = 'UTF8', FIRSTROW = 1);

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=[...]
);

See OPT_LOAD_WITH_BQ_API to use bq load instead of LOAD DATA INTO.


Databricks SQL file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL
FORMAT_OPTIONS('header'='false','quote'='\"', 'skipRows'='0', 'delimiter'=';')

File load executed in Databricks:

SQL
COPY INTO ...
FROM ...
FORMAT_OPTIONS('header'='false','quote'='\"', 'skipRows'='0', 'delimiter'=';');

JavaScript errors detected

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

If this problem persists, please contact our support.