How to convert CSV file to XML using Azure synapse analytics or ADF.

Question

How to convert CSV file to XML using Azure synapse analytics or ADF.

Archana Metkari 0

I have csv input file stored in the azure datalake , want to convert the csv file to .XML formatted file. How to achieve that using Azure Data factory / azure synapse dedicated/serverless sql pool

Subashri Vasudevan 11,226 Reputation points

2023-06-18T04:54:54.5066667+00:00

Would be better if you can share the file structure / sample file. And expected output @Archana Metkari
Archana Metkari 0 Reputation points

2023-06-19T11:02:53.9066667+00:00

Input file is in the form of comma separated. Like below

LINENUMBER, ACCOUNTDISPLAYVALUE,ACCOUNTTYPE,COMPANY,CREDIT,CURRENCY,DATE,DEBIT,INVOICE,INVOICEDATE.

Output I need is XML file with columnname=data .. just like below
<VENDINVOICEJOURNALLINEENTITY LINENUMBER="" ACCOUNTDISPLAYVALUE="" ACCOUNTTYPE="" COMPANY="" CREDIT="" CURRENCY="" DATE="" DEBIT="" INVOICE="" INVOICEDATE=""/>
QuantumCache 20,366 Reputation points Moderator

2023-06-22T21:41:47.44+00:00

Hello @Archana Metkari,

Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

If the response is helpful, please click "Accept Answer" and upvote it. So that we can close this thread.

1 answer

Your answer

Subashri Vasudevan 11,226 Reputation points

2023-06-18T04:54:54.5066667+00:00

Would be better if you can share the file structure / sample file. And expected output @Archana Metkari
Archana Metkari 0 Reputation points

2023-06-19T11:02:53.9066667+00:00

Input file is in the form of comma separated. Like below

LINENUMBER, ACCOUNTDISPLAYVALUE,ACCOUNTTYPE,COMPANY,CREDIT,CURRENCY,DATE,DEBIT,INVOICE,INVOICEDATE.

Output I need is XML file with columnname=data .. just like below
<VENDINVOICEJOURNALLINEENTITY LINENUMBER="" ACCOUNTDISPLAYVALUE="" ACCOUNTTYPE="" COMPANY="" CREDIT="" CURRENCY="" DATE="" DEBIT="" INVOICE="" INVOICEDATE=""/>
QuantumCache 20,366 Reputation points Moderator

2023-06-22T21:41:47.44+00:00

Hello @Archana Metkari,

Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

If the response is helpful, please click "Accept Answer" and upvote it. So that we can close this thread.

Answer 1

Hello @Archana Metkari,

There is no direct out-of-the-box transformation in ADF, but I have done it using the Derived Column Expression, Please have a look at the below DataFlow, I hope it may be helpful with your initial query!

In the Derived Column, i have mentioned something similar to capture the XML formatted tag value.

'<salesheader SysRowID="' + toString(SysRowID) + '" LSN="' + LSN + '" LastProcessedChangeDateTime="' + toString(LastProcessedChangeDateTime) + '" DataLakeModifiedDateTime="' + toString(DataLakeModifiedDateTime) + '" RecID="' + RecID + '" LINEDISC="' + LINEDISC + '"/>'

Please check the below Data Flow for more info.

Source is as usual to read the CSV file.

Use the Derived Column Transformation.

Use the Sink as usual to map the Derived Column in the Sink.
User's image

User's image

Sink: Mapping:

User's image

As you can see my output is in XML tag format which I will use for further processing!
User's image

User's image

If the response is helpful, please click "Accept Answer" and upvote it. So that we can close this thread.

QuantumCache 20,366 Reputation points Moderator

2023-06-30T17:55:24.71+00:00

Hello @Archana Metkari,

Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

If the response is helpful, please click "Accept Answer" or Click 'Yes'. So that we can close this thread.

Share via

How to convert CSV file to XML using Azure synapse analytics or ADF.

1 answer

Your answer