Step 1. Create File Format Objects¶
A named file format object provides a convenient means to store all of the format information required for loading data from files into tables.
Execute CREATE FILE FORMAT to create a file format that you can reference throughout the remainder of the tutorial.
In regular use, this step is optional, but is recommended when you plan to load large numbers of files of a specific format.
CSV File Format¶
The following example creates a file format named mycsvformat
with the following parameters:
- TYPE = CSV
File format type. The default value is
CSV
.- FIELD_DELIMITER = ‘|’
Character that separates fields in an input file. The default value is
','
.- SKIP_HEADER = 1
Number of header lines at the start of the file. The COPY command skips these lines when loading data. The default value is
0
.CREATE OR REPLACE FILE FORMAT mycsvformat TYPE = 'CSV' FIELD_DELIMITER = '|' SKIP_HEADER = 1;
JSON File Format¶
The following example creates a file format named myjsonformat
with the following parameters:
- TYPE = JSON
File format type. The default value is
CSV
.- STRIP_OUTER_ARRAY = TRUE
Instructs the JSON parser to remove the root brackets
[ ]
.
CREATE OR REPLACE FILE FORMAT myjsonformat
TYPE = 'JSON'
STRIP_OUTER_ARRAY = TRUE;