Data Factory Loading multiple csv files. Is there any way of dealing with column header differences?

Question

I have csvs in a datalake.

Im coping them into SQL using the copy activity

the File Path type is a wildcard file path

Very occasionally there are slight differences between the column headers.
Is there any way that this can be dealt with rather than going into the file and changing them at source?

For example ACC_ID and ACCS_ID are the same thing, in the same column order but it errors in data factory

Answer 1

Hello anonymous user,

Thanks for the ask and using the forum .

The situation in which you are is something i see many can face when the do an wildcard option . Here are my thoughts

1. I am assuming that you are using copy activity , i tried using the Ordinal option as called out here , it doesn't worrk .
2. I tried to use the addition column option to see if that helps , with no luck ,
3. I tried something with mapping data flow and i think it will work ( as per the test you mentioned ) , but since you mentioned that since the issue mentioned "happens occasionally" not sure if you want to use , but i will go ahead and share the same .

I am using the the derived column actvity and the coalesce funnction . I am sharing the below animation which will make thing very clear . The column3 and column33 needs attention .

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

thanks for that, I need to do this out side of a data flow though in a Standard Copy activity. And Im finding the video a hard to follow because I cant seem to pause it to check out each individual part so i'm not sure it works for what i want to do.

Is there any way I can pause the video or get screen shot of each section. Its just to quick for me to make any sense out of it