CSV comma issue in sink (Azure DW)

Question

Hi,

I want to move my file from azure blob to azure DW.
In azure blob, one of the table having column value like Naperville, IL.
I want this value to be written in to azure dw.
But when I try to write it into DW using (polybase copy), I am getting column count mismatch error and I believe this single column Naperville, IL is getting treated as two columns.
I tried with the source(blob) setting. But I am getting validation error, when try to control in source by making Escape(/) and Quote character (" double quote)
Validation error :

source setting:

In sink (azure DW), I could not able to see any setting.
sink setting :

can you please guide me, how can i achieve it?

Thanks.

Answer 1

@Gowri Shankar Thanks for using Microsoft Q&A.

You get think kind of message with Polybase when your source data is not compatible with Polybase and thus you need to enable staging in your copy data via Azure blob or Azure Gen2 and ADF automatically converts the data to meet the data format requirements of Polybase. Please go to Settings tab of your copy data activity and select "Enable Staging" by providing storage path for your staging blob/Gen2. (See screenshot below).
I, however, do not feel that doing that will fix your actual requirement to load comma separated texts as Text - Naperville, IL will be treated as two different columns. You may need to do some data scrubbing by using data flow ( for example - replacing , in the text with - or some other character OR using a different delimiter (other than ,)for the CSV file.

Answer 2

Hi @Gowri Shankar ,

As you are having issue loading the CSV file, just a workaround, I was wondering, if you can create a different type of file e.g., .parquet instead of .csv and in the CopyActivity, use the parquet file(s) as a source and load them to DW.

Thanks!