Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,624 questions
azure data flow source: how to deal with csv files with columns in different order
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(76.8, 38%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJC%3C/text%3E%3C/svg%3E)
javier cohenar
211
Reputation points
I'm importing different CSV files in a Data Flow activity. All source files have the same columns, but I realized that sometimes columns are in different order leading data flow to mix data from different columns
Example : say file1.csv is
Col_A Col_B, Col_C
A1 B1 C1
and file2.csv
Col_A Col_C, Col_B
A2 C2 B2
Result in data flow activity is:
Col_A Col_C, Col_B
A2 C2 B2
A1 B1 C1
However expected result is
Col_A Col_C, Col_B
A2 C2 B2
A1 C1 B1
Is there a way to deal with this situation?
Azure Data Factory
{count} vote
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 12%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESV%3C/text%3E%3C/svg%3E)
Subashri Vasudevan 11,226 Reputation points2023-06-18T04:53:10.1166667+00:00 Expected result Seems same as actual result?
-
javier cohenar 211 Reputation points
2023-06-18T05:45:43.1733333+00:00 No, second row differs
-
Subashri Vasudevan 11,226 Reputation points
2023-06-20T04:27:34.2833333+00:00 Are you using union to club the records from both the files? I tried to replicate your scenario, but for me it takes the columns perfectly, even when the order is not the same.
-
Subashri Vasudevan 11,226 Reputation points
2023-06-20T04:36:03.02+00:00 Screenshots for your reference,
-
javier cohenar 211 Reputation points
2023-06-20T23:52:31.8766667+00:00 Hi, thanks but no, i am not using Union to merge the files.
For each file I have 90-100 different suppliers. Although, all files have the same columns, they differ in terms of column order.
Since there are so many data suppliers, I cannot create an Union operation for each file.
The solution I found elsewhere is to write a python script to read each file an save it again with a standarized column order.
I was wondering whether it was possible to handle this column reordering without relying on a python script, but it doesn't seem to be the case.
-
Subashri Vasudevan 11,226 Reputation points
2023-06-21T12:11:15.67+00:00 Hello,Union doesn't validate any data. It just merges the data. And column order doesn't matter if the column names are all same in all the files.
Could you please post more details about your data flow, screenshot/ along with data set settings, so that we could decide if it's doable in ADF.
Thanks.
Awaiting your response.
-
javier cohenar 211 Reputation points
2023-06-21T15:10:00.7566667+00:00 Thanks, please find attached the pipeline
-
Subashri Vasudevan 11,226 Reputation points
2023-06-22T01:07:11.4566667+00:00 Hi, thanks for providing screenshot. I was in an assumption that you have multiple sources. But looking at your screenshot it seems like you are already merging the data before bringing it to data flow. Pl confirm if my understanding is correct. Try to use a select transformation before sink and decide which order you need in the sink. Hope it helps
-
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator2023-06-23T07:34:25.3966667+00:00 Hi javier cohenar, Just checking if you get chance to share updates. Thank you.
-
javier cohenar 211 Reputation points
2023-06-23T18:04:44.62+00:00 Hi, I'm afraid the Select transformation didn't work. I decided to implement the python script mentioned before
-
Subashri Vasudevan 11,226 Reputation points
2023-06-24T00:53:48.6766667+00:00 Hi, please provide us the source file or a dummy source file so that we can figure out if there's an issue. And in python, how are you fixing it? Could you pls tell us so that it may be helpful for us to identify issue.
-
javier cohenar 211 Reputation points
2023-06-27T14:42:39.04+00:00 Please find attached the script for applying a schema to the files i am usingtest-format.txt
Don't have the original files anymore since i changed their schema, but the following examples seem similar to the original situation
Sign in to comment
Sign in to answer