How to fix error : ErrorCode=DelimitedTextMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException

Question

Facing an error while to load data from a csv file from fileshare to SQL table.

Error message:

ErrorCode=DelimitedTextMoreColumnsThanDefined, 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=Error found when processing 'Csv/Tsv Format Text' source 'file1.csv' with row number 5: found more columns than expected column count 17.,Source=Microsoft.DataTransfer.Common,'

Value of row number 5 is:

KPY,2524YT,PLOTTED,0,2524YT,PLOTTED,2524YT,PLOTTED,NULL,NULL,NULL,NULL,A,2,2,231,ATON,ＩＮＣ.

Quote character is currently set as double quote (""), escape character is currently set as backslash (\)

I tried changing the escape character to double quote, but it didn't help

Answer 1

Your csv file has extra , in your last column of that row. As your dataset delimiter also ,, it is taking it as extra column which is causing the above error.

To overcome the above error, first your file should have different delimiter. Copy activity can't handle the row operations. So, use dataflow for that. But dataflow currently does not have support for File share as source.

So, first take a copy activity and copy the file from file share to temp storage like Data lake gen2 or blob storage. Use binary datasets as source and sink for this copy activity.

After that, take dataflow and source dataset should be this dataset with below configurations. Give Pipe(|) as column delimiter and uncheck the First row as header.

This takes all the columns as a single column with a column name like _col0_.

Next, take derived column in dataflow and use below expression on that column to replace the delimiter.

replace(replace({_col0_}, ',', '|'),'ATON|','ATON,')

In sink settings, give settings as one single file and give the filename.

This sink dataset should have below configurations.

This will generate the csv file like below.

Now, after dataflow activity, use another copy activity to copy the above file to your target.

For the source dataset of this copy activity, use another dataset with Pipe(|) delimiter settings like below.

Result in SQL table: