Data Factory cannot copy `csv` with comma after last column to sql data warehouse

Question

I have CSV files that I want to copy from a blob to DW, the CSV files have comma after the last column (see example below). Using ADF, I tried to copy csv files to a SQL table in DW. However, I got this error, which I think it's because of the last comma (as I have 15 columns):

few rows of csv file:

Code,Last Trading Date,Bid Price,Bid Size,Ask Price,Ask Size,Last Price,Traded Volume,Open Price,High Price,Low Price,Settlement Price,Settlement Date,Implied Volatility,Last Trade Time,
BNH2021F,31/03/2021,37.750000,1,38.000000,1,,0,,,,37.750000,29/03/2021,,,
BNM2021F,30/06/2021,44.500000,6,44.700000,2,44.400000,4,44.300000,44.400000,44.300000,44.500000,29/03/2021,,15-55-47.000,
BNU2021F,30/09/2021,46.250000,2,47.000000,1,47.490000,2,47.490000,47.490000,47.490000,46.920000,29/03/2021,,15-59-10.000,

Note that CSVs are the original files and I can't change them. I also tried different Quote and Escape characters in the dataset and it didn't work. Also I want to do this using ADF, not azure functions.

I couldn't find any solution to that, please help.

Update: It's interesting that the dataset preview works:

Answer 1

I think you can use data flow to achieve that.

1. Azure data factory will interpret last comma as a column with null value. So we can use Select activity to filter last column.

2. Set mapping manually at sink.

3. Then we can sink to our DW or SQL table.