Azure ADF Copy Activity with Trailing Column Delimiter

Question

I have a strange source CSV file where it contains a trailing column delimiter at the end of each record just before the carriage return/new line.

When ADF is previewing this data, it displays only 2 columns without issue and all the data rows. However, when using the copy activity, it fails with the following exception.

ErrorCode=DelimitedTextColumnNameNotAllowNull,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The name of column index 3 is empty. Make sure column name is properly specified in the header

Now I understand why it's complaining about this due to trailing delimiter, but my question is whether or not there is a way to deal with this condition? I've tried including the trailing comma in the record delimiter (,\r\n), but then it just pivots the data where all the columns become rows.

Is there a way to address this condition in copy activity?

Answer 1

When preview the data in dataset, it seams correct:

But actually in copy actives, the data will derived to 3 columns by the column delimiter ",", the third column is empty or NULL value. This will cause the error.

If you use Data Flow import projection from source, you can see the third column:

Just for now, copy active doesn't support modify the data schema. You must use Data flow Derived Column to create a new schema for the source. For example:

Then mapping the new column/schema to sink will solve the problem.

HTH.

Answer 2

Use a different encoding for your CSV. CSV utf-8 will do the trick.