External file formats

When you work with files such as CSVs, or even big data files such as ORC or Parquet, you need to create an external file format to tell SQL Server how to interpret the file. In the following example, the external file format is configured to interpret a CSV file using a caret as a field terminator. Other options exist for data compression, string termination, and file encodings:

CREATE EXTERNAL FILE FORMAT azure_adventureworks_eff  
WITH 
(FORMAT_TYPE = DELIMITEDTEXT  
,FORMAT_OPTIONS( FIELD_TERMINATOR = '^')  
);

Once created, you can reuse the external file format for all the external tables of that type. You can also check to see which ones you already have created using the catalog view:

SELECT * FROM sys.external_file_formats
;

Remember, external file formats are only required when creating an external table over a file. They are not needed for other relational or non-relational external data sources.

...