Simple CSV to XML Conversion - Python

Question

I am looking for a way to automate the conversion of CSV to XML.

Here is an example of a CSV file, containing a list of movies:

Here is the file in XML format:

<collection shelf="New Arrivals">
<movietitle="Enemy Behind">
   <type>War, Thriller</type>
   <format>DVD</format>
   <year>2003</year>
   <rating>PG</rating>
   <stars>10</stars>
   <description>Talk about a US-Japan war</description>
</movie>
<movietitle="Transformers">
   <type>Anime, Science Fiction</type>
   <format>DVD</format>
   <year>1989</year>
   <rating>R</rating>
   <stars>8</stars>
   <description>A schientific fiction</description>
</movie>
<movietitle="Trigun">
   <type>Anime, Action</type>
   <format>DVD</format>
   <episodes>4</episodes>
   <rating>PG</rating>
   <stars>10</stars>
   <description>Vash the Stampede!</description>
</movie>
<movietitle="Ishtar">
   <type>Comedy</type>
   <format>VHS</format>
   <rating>PG</rating>
   <stars>2</stars>
   <description>Viewable boredom</description>
</movie>
</collection>

I've tried a few examples where I am able to read the csv and XML format using Python using DOM and SAX but yet am to find a simple example of the conversion. So far I have:

import csv              
f = open('movies2.csv')
csv_f = csv.reader(f)   

def convert_row(row):
   return """<movietitle="%s">
   <type>%s</type>
   <format>%s</format>
   <year>%s</year>
   <rating>%s</rating>
   <stars>%s</stars>
   <description>%s</description>
</movie>""" % (
   row.Title, row.Type, row.Format, row.Year, row.Rating, row.Stars, row.Description)

print ('\n'.join(csv_f.apply(convert_row, axis=1)))

But I get the error:

 File "moviesxml.py", line 16, in module
   print ('\n'.join(csv_f.apply(convert_row, axis=1)))
AttributeError: '_csv.reader' object has no attribute 'apply'

I am pretty new to Python, so any help would be much appreciated!

I am using Python 3.5.2.

Thanks!

Lisa

Please [edit] your question and include the Python code you have tried. Does it work? — , Dec 09 '16 at 11:23
hi, for some reason my code for DOM and SAX corrupted and I forgot where I found the sample code. I've included the import csv method — L Marfell, Dec 09 '16 at 11:34
Future readers, avoid most answers on this post. See [What''s so bad about building XML with string concatenation?](https://stackoverflow.com/q/3034611/1422451). XML is not a text file to be built with string formatting. Use appropriate DOM libraries like Python's etree or lxml. — Parfait, May 27 '23 at 17:48

score 23 · Accepted Answer · edited May 23 '17 at 11:52

A possible solution is to first load the csv into Pandas and then convert it row by row into XML, as so:

import pandas as pd
df = pd.read_csv('untitled.txt', sep='|')

With the sample data (assuming separator and so on) loaded as:

          Title                   Type Format  Year Rating  Stars  \
0  Enemy Behind           War,Thriller    DVD  2003     PG     10   
1  Transformers  Anime,Science Fiction    DVD  1989      R      9   

             Description  
0          Talk about...  
1  A Schientific fiction

And then converting to xml with a custom function:

def convert_row(row):
    return """<movietitle="%s">
    <type>%s</type>
    <format>%s</format>
    <year>%s</year>
    <rating>%s</rating>
    <stars>%s</stars>
    <description>%s</description>
</movie>""" % (
    row.Title, row.Type, row.Format, row.Year, row.Rating, row.Stars, row.Description)

print '\n'.join(df.apply(convert_row, axis=1))

This way you get a string containing the xml:

<movietitle="Enemy Behind">
    <type>War,Thriller</type>
    <format>DVD</format>
    <year>2003</year>
    <rating>PG</rating>
    <stars>10</stars>
    <description>Talk about...</description>
</movie>
<movietitle="Transformers">
    <type>Anime,Science Fiction</type>
    <format>DVD</format>
    <year>1989</year>
    <rating>R</rating>
    <stars>9</stars>
    <description>A Schientific fiction</description>
</movie>

that you can dump in to a file or whatever.

Inspired by this great answer.

Edit: Using the loading method you posted (or a version that actually loads the data to a variable):

import csv              
f = open('movies2.csv')
csv_f = csv.reader(f)   
data = []

for row in csv_f: 
   data.append(row)
f.close()

print data[1:]

We get:

[['Enemy Behind', 'War', 'Thriller', 'DVD', '2003', 'PG', '10', 'Talk about...'], ['Transformers', 'Anime', 'Science Fiction', 'DVD', '1989', 'R', '9', 'A Schientific fiction']]

And we can convert to XML with minor modifications:

def convert_row(row):
    return """<movietitle="%s">
    <type>%s</type>
    <format>%s</format>
    <year>%s</year>
    <rating>%s</rating>
    <stars>%s</stars>
    <description>%s</description>
</movie>""" % (row[0], row[1], row[2], row[3], row[4], row[5], row[6])

print '\n'.join([convert_row(row) for row in data[1:]])

Getting identical results:

<movietitle="Enemy Behind">
    <type>War</type>
    <format>Thriller</format>
    <year>DVD</year>
    <rating>2003</rating>
    <stars>PG</stars>
    <description>10</description>
</movie>
<movietitle="Transformers">
    <type>Anime</type>
    <format>Science Fiction</format>
    <year>DVD</year>
    <rating>1989</rating>
    <stars>R</stars>
    <description>9</description>
</movie>

Why Pandas? The Python Standard Library contains a CSV module. — , Dec 09 '16 at 11:42
@LutzHorn No particular reason. OP didn't specify what method was used to load the data at first, and Pandas is a great library that I'm used to. — robertoia, Dec 09 '16 at 11:45
hi thanks for your post. I tried to install panda using 'sudo pip install pandas' but I get the message 'ImportError: no module named 'pandas'' — L Marfell, Dec 09 '16 at 11:48
Also (sorry i'm a real newbie) how to I save this into as an XML file - movies.xml — L Marfell, Dec 09 '16 at 11:51
@LMarfell I edited my answer to account for your loading method. No need for pandas (although I recommend it if you often manipulated csv files). — robertoia, Dec 09 '16 at 11:55
@RobertoIzquierdo brilliant! Thanks so much. How do I then write this data to an .xml file? — L Marfell, Dec 09 '16 at 12:05
@LMarfell `with open('out.xml', 'w') as f: f.write('\n'.join(df.apply(convert_row, axis=1)))`, don't forget to add the part with `` and so on, it's just string concatenation ;) — robertoia, Dec 09 '16 at 12:12
@RobertoIzquierdo Many thanks. It said df not defined, so I changed df for csv_f, but it says 'AttributeError: '_csv.reader' object has no attribute 'apply'', without apply it says '_csv.reader' object is not callable' — L Marfell, Dec 09 '16 at 12:43
@RobertoIzquierdo yes i am using import csv, rather than panda — L Marfell, Dec 09 '16 at 12:45
@LMarfell My bad, it should be something like with `with open('out.xml', 'w') as f: f.write('\n'.join([convert_row(row) for row in data]))` — robertoia, Dec 09 '16 at 12:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/130220/discussion-between-l-marfell-and-roberto-izquierdo). — L Marfell, Dec 09 '16 at 13:17

score 3 · Answer 2 · answered Feb 08 '22 at 20:34

I tried to generalize robertoia's function convert_row for any header instead of writing it by hand.

import csv  
import pandas as pd
            
f = open('movies2.csv')
csv_f = csv.reader(f)   
data = []

for row in csv_f: 
   data.append(row)
f.close()

df = pd.read_csv('movies2.csv')
header= list(df.columns)

def convert_row(row):
     str_row = """<%s>%s</%s> \n"""*(len(header)-1)
     str_row = """<%s>%s""" +"\n"+ str_row + """</%s>"""
     var_values = [list_of_elments[k] for k in range(1,len(header)) for list_of_elments in [header,row,header]]
     var_values = [header[0],row[0]]+var_values+[header[0]]
     var_values =tuple(var_values)
     return str_row % var_values

text ="""<collection shelf="New Arrivals">"""+"\n"+'\n'.join([convert_row(row) for row in data[1:]])+"\n" +"</collection >"
print(text)
with open('output.xml', 'w') as myfile: 
  myfile.write(text)

Of course with pandas now, it is simpler to just use to_xml() :

df= pd.read_csv('movies2.csv')
with open('outputf.xml', 'w') as myfile: 
  myfile.write(df.to_xml())

score 1 · Answer 3 · answered Sep 29 '22 at 13:13

I found an easier way to insert variables into a string or block of text:

'''Twas brillig and the slithy {what}
Did gyre and gimble in the {where}
All {how} were the borogoves
And the {who} outgrabe.'''.format(what='toves',
                                  where='wabe',
                                  how='mimsy',
                                  who='momeraths')

Alternatively:

'''Twas brillig and the slithy {0}
Did gyre and gimble in the {1}
All {2} were the borogoves
And the {3} outgrabe.'''.format('toves',
                                'wabe',
                                'mimsy',
                                'momeraths')

(substitute name of incoming data variable for 'toves', 'wabe', 'mimsy', and 'momeraths')

score 0 · Answer 4 · answered May 27 '23 at 07:43

Can be done without any libraries too simple script as below assumes the CSV file as cvs_to_xml_data.csv with single column value.

"""This script is to create xmlfile.txt file from a given csv_to_xml_data.csv file"""
row_list = []
with open('csv_to_xml_data.csv', 'r') as csvfile:
    row_list = csvfile.read().splitlines()

with open('xmlfile.txt', 'w') as txtfile:
    for row in row_list:
        txtfile.write(f"""<Student>
    <studentclass>8thstd</studentclass>
    <studentsubject>math</<studentsubject>
    <stuItem>{row}<stuItem>
    <stuItemSting>I am {row} and I like to be {row} because I am {row} 
</stuItemSting>
</Student>""")

Simple CSV to XML Conversion - Python

4 Answers4

Linked

Related