How to convert xml file to csv file using python

Question

I have a xml which i want to convert into csv but i am getting error.

In my xml file i wish to write only selected columns into csv.

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

root = ET.parse('D:\\Task\\09_ActionRecorder_0.XML').getroot()

tags =[]
for elem in root:
    for child in elem:
        try:
            tag = {}
            tag["TL"] = child.attrib['TL']
            tag["CN"] = child.attrib['CN']
            tag["DT"] = child.attrib['DT']
            tag["AN"] = child.attrib['AN']
            tags.append(tag)

        except KeyError:
            tags.append(tag)
print(tags)
df_users = pd.DataFrame(tags)
#df_users.head(20)


column_name_update = df_users.rename(columns = {"TL": "Title", 
                                  "CN":"Control Name", 
                                  "DT": "Date Time",
                                  "AN": "Application Name"}) 


#new_data.head(20)

column_name_update.to_csv("D:\\Tasks\\Sample.csv",index=False, columns=["Title", 'Control Name', 'Date Time', 'Application Name'])

From the given xml file i wish to write only limited no of columns(as shown in code).But whenever i execute above code i am getting key error and in csv file only one column is being getting written.Kindly help if any one know how to do so.

balderman · Accepted Answer · 2019-07-23 10:22:56Z

1

Loop over list of xml files and convert each of them to csv

import xml.etree.ElementTree as ET

ATTRIBUTES = ['TL', 'CN', 'DT', 'AN']
data = []
# TODO populate the list - https://docs.python.org/2/library/os.html#os.listdir
list_of_files = []
for file_name in list_of_files:
    root = ET.parse(file_name)
    recs = root.findall('.//Rec')
    for rec in recs:
        data.append([rec.attrib.get(attr, 'N/A') for attr in ATTRIBUTES])
    with open('{}.csv'.format(file_name), 'w') as f:
        f.write('Title,Control Name,Date Time,Application Name' + '\n')
        for entry in data:
            f.write(','.join(entry) + '\n')
   data = []

edited Jul 23, 2019 at 10:22

answered Jul 23, 2019 at 9:08

balderman

24k8 gold badges39 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Ani Over a year ago

Thanks but this code is for single xml file conversion. what I have to do if I have lets say 5 xml file which needed to be converted into individual csv file but i don't want to run code for those individually(agin and again).Is there any way where i just specify the folder which contain multiple xml file and write csv for those files individually.If you know anything about the same kindly let me know i will be highly thankful.

balderman Over a year ago

@Ani Do you need 1 csv per 1 xml or 1 csv for ALL xml files?

Ani Over a year ago

Well currently I am trying 1 csv per 1 xml but If Possible both the scenarios so that in future I might not face any problem because of the same.

balderman Over a year ago

@Ani I have modified the code to create 1 csv per xml file. You need to populate the list_of_files

Ani Over a year ago

And what about 1 csv for all xml if you know the same please add that also and thanks.

|

BillyN · Accepted Answer · 2019-07-23 06:59:54Z

-1

I ran into a similar problem a few months ago, what I ended up doing was just using just using excel to save the file as a CSV, however in your case I know this might not be practical. What I would recommend is using your python file to first convert it to CSV using bash script(would also work with power shell) Then iterate over your CSV file.

This is how to create the bash script

This is how you can run the script from your python file

Hope this helps

edited Jul 23, 2019 at 6:59

answered Jul 23, 2019 at 6:19

BillyN

1852 silver badges11 bronze badges

1 Comment

Ani Over a year ago

Thanks but i dont have any knowledge regarding bash I want an python program where I can specify which columns I wanted to write from xml to csv.

Collectives™ on Stack Overflow

How to convert xml file to csv file using python

2 Answers 2

13 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related