How to escape/handle string delimiter value present in a string while using external table

Priya Jha 896 Reputation points
2024-01-08T11:08:20.8533333+00:00

Hi All,

I have a file with following values:

c1,c2

1,"123,abc""pqr,787"

In this file in the 2nd column c2, we are getting field delimiter which is comma as well as quotes which is string delimiter

How can we read this file using external table in Azure SQL Dedicated pool?

Getting this error when trying to read via external table:

User's image

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,172 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Amira Bedhiafi 28,381 Reputation points
    2024-01-09T12:08:10.3766667+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.