Excel or CSV file to XML - ADF

Question

Hi, I want to convert excel/csv file into XML format. This xml is used as a body text for a rest api.

Basically i need to send excel data to a REST API using ADF. Rest API accepts xml format. excel file is present in storage container.

What are the possible ways to achieve this? Python script or data flow has inbuilt support excel to xml conversion? any other ways? If using python script, what are the dependancies for executing python script? what are the infra requirements for the same? From where this script will run and how?

Answer 1

@Santhi Dhanuskodi

Thanks for using Microsoft Q&A platform and thanks for posting your query here.

I understand that you want to convert excel/csv to XML using ADF.

• You can use Azure Function to write python code convert the Excel/CSV file to XML format and run the azure function from ADF pipeline.

Kindly refer to the below resources:

• https://www.geeksforgeeks.org/how-to-convert-excel-to-xml-format-in-python/
• https://blog.groupdocs.cloud/conversion/convert-xml-to-excel-and-excel-to-xml-in-python/
• You can write Pyspark code in Azure synapse notebook.
  • To read data from excel file:

    import pandas as pd
    account_key_value="your_storage_acc_key"
    df = pd.read_excel('abfs://******@jadls2.dfs.core.windows.net/xl_files/sample.xlsx', storage_options = {'account_key' : account_key_value})
    s_df=spark.createDataFrame(df)
    display(s_df)    

Instead of account key, you can also use these options:

storage_options = {'sas_token' : 'sas_token_value'}
storage_options = {'connection_string' : 'connection_string_value'}
storage_options = {'tenant_id': 'tenant_id_value', 'client_id' : 'client_id_value', 'client_secret': 'client_secret_value'}

• To write data intot xml file:

    df = spark.read.format('xml').options(rowTag='book').load('books.xml')
    (df.select("author", "_id").write
      .options(rowTag='book', rootTag='books')
      .xml('newbooks.xml')
    )
    

Here are the resources you can refer:

https://learn.microsoft.com/en-us/azure/databricks/query/formats/xml

https://stackoverflow.com/questions/76812820/how-to-read-excel-file-in-synapse-notebook-which-is-in-azure-datalake-gen-2-stor

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Answer 2

XML format is supported for the following connectors: Amazon S3, Amazon S3 Compatible Storage, Azure Blob, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure Files, File System, FTP, Google Cloud Storage, HDFS, HTTP, Oracle Cloud Storage and SFTP. It is supported as source but not sink. Consider exploring the "Transform XML" feature in Azure Logic Apps to see if it aligns with your needs :

1. A guide on using Azure Logic Apps for importing CSV files to SQL Server: http://blogs.recneps.net/post/Using-Azure-Logic-Apps-to-Import-CSV-to-SQL-Server
2. A discussion on Stack Overflow about converting CSV to XML in Logic Apps: https://stackoverflow.com/questions/61417713/how-to-convert-a-csv-to-xml-in-logic-apps
3. A forum post on MSDN about converting CSV/Excel to XML in Logic Apps: https://social.msdn.microsoft.com/Forums/en-US/dfce78db-ebd6-41d9-ac7b-423a1f83f186/convert-csvexcell-to-xml-in-logic-app Additionally, it's strongly encouraged to support existing feature requests on the ADF User Voice forum by upvoting or commenting. This can help prioritize the development of features such as:

• XML support at the sink side
• Support for XML files for ADLS as a sink If you have the possibility to use python :

import pandas as pd
import xml.etree.ElementTree as ET

# Load the Excel/CSV file
df = pd.read_csv('path_to_your_file.csv')

# Convert the DataFrame to XML format
root = ET.Element("Root")
for _, row in df.iterrows():
    record = ET.SubElement(root, "Record")
    for col in df.columns:
        child = ET.SubElement(record, col)
        child.text = str(row[col])

# Convert the XML tree to a string
xml_str = ET.tostring(root, encoding='utf8', method='xml')

# Save or use the XML string as needed