When you create an external table, you need to define the format file that specifies the type of data file being loaded and the method of data loading. This includes the field terminator and string delimiter.
For your case, if you have control over the format of the CSV file, the most straightforward solution would be to ensure the file is correctly formatted to escape special characters. Typically, CSV standards require a quote character within a field to be escaped by preceding it with another quote character. It looks like this has been done in your case, but there's an error in reading the file.
CREATE EXTERNAL FILE FORMAT csv_file_format
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
STRING_DELIMITER = '"',
USE_TYPE_DEFAULT = FALSE
)
);
When you create the external table, use the file format you defined above.
CREATE EXTERNAL TABLE your_table_name (
c1 INT,
c2 NVARCHAR(4000)
)
WITH (
LOCATION = 'your_file_path.csv',
DATA_SOURCE = your_external_data_source,
FILE_FORMAT = csv_file_format,
REJECT_TYPE = VALUE,
REJECT_VALUE = 0
);
If you still encounter errors, you might need to pre-process your CSV file to escape special characters properly or to change the delimiters to something more unique that does not appear in your data.