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.
External table in Azure Synapse Analytics Dedicated SQL Pool
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 53%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVK%3C/text%3E%3C/svg%3E)
Vivek Komarla Bhaskar
956
Reputation points
Hi,
In Azure Synapse Analytics, I have an external table pointing to a file on my ADLS gen2 with two columns (Date, Product). I am experiencing an issue where my query to the external table fails whenever I receive a product with comma-separated values. Is there a way to overcome this problem when the requirement for us is only to use external tables in this case?
Here below are a few examples from my CSV file-
Date, Product
2023-01-01, Apple
2023-01-02, Samsung, Galaxy
2023-01-02, Apple,iPhone
Azure Synapse Analytics
{count} votes
-
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator2023-03-14T11:38:46.05+00:00 Hey,
In this scenario the 2nd column value needs to be under quotes for synapse to read properly.
meaning your file should be in the below format :
Date, Product
2023-01-01, "Apple"
2023-01-02, "Samsung, Galaxy"
2023-01-02, "Apple,iPhone"
-
Vivek Komarla Bhaskar 956 Reputation points
2023-03-14T11:48:16.63+00:00 Let me try, in this case, the external table results will also show quotes " ". Isn't it?
-
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator
2023-03-14T12:13:18.76+00:00 -
Vivek Komarla Bhaskar 956 Reputation points
2023-03-15T12:16:49.76+00:00 Hi @Nandan Hegde ,
I created an external file format with the following parameters, but now I have a situation in which the source data itself has a few quotation marks between the data samples.
CREATE EXTERNAL FILE FORMAT DelimitedText_CommaTerminator_QuoteDelimiter_FirstRowAsHeader WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2));Here is an example and highlighted cell, resulting in an error when querying my external table - HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: Could not find a delimiter after string delimiter.
Sign in to comment
Sign in to answer