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-23T10:22:56.910

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 '19 at 10:22

answered Jul 23 '19 at 09:08

balderman

22,927
7
34
52

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. – Ani Jul 23 '19 at 10:07
@Ani Do you need 1 csv per 1 xml or 1 csv for ALL xml files? – balderman Jul 23 '19 at 10:11
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. – Ani Jul 23 '19 at 10:18
@Ani I have modified the code to create 1 csv per xml file. You need to populate the `list_of_files` – balderman Jul 23 '19 at 10:23
And what about 1 csv for all xml if you know the same please add that also and thanks. – Ani Jul 23 '19 at 10:35
@Ani I will let you experiment with that.. BTW - did you test the code? Does it work for you? – balderman Jul 23 '19 at 10:36
I am executing the same no file is getting created or even no error also.. – Ani Jul 23 '19 at 10:43
Did you populate `list_of_files`? If not - you need to do it – balderman Jul 23 '19 at 10:45
I added this line this is the folder where all log files exist os.listdir("D:\\Tasks\\Task_10\\ANT_LOG\\") – Ani Jul 23 '19 at 10:45
Add this to the code (after you populate the list). `print(list_of_files)` . What do you get? – balderman Jul 23 '19 at 10:46
print(list_of_files) [] Blank list – Ani Jul 23 '19 at 10:52
@Ani This is why nothing happens.. See this code `import os path = '.' # your folder here list_of_files = [os.path.join(path,f) for f in os.listdir( path )] print(list_of_files)` – balderman Jul 23 '19 at 10:59
If Possible would you please tell me how to convert multiple xml file into single csv file for the above problem... – Ani Jul 23 '19 at 11:12

BillyN · Answer 2 · 2019-07-23T06:59:54.053

-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 '19 at 06:59

answered Jul 23 '19 at 06:19

BillyN

185
2
10

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. – Ani Jul 23 '19 at 06:25

How to convert xml file to csv file using python

2 Answers2