Converting "document format" / XML to CSV

Question

I'm trying to convert:

<doc id="123" url="http://url.org/thing?curid=123" title="title"> 
Title

text text text more text

</doc>

into a CSV file (the file has a ton of similarly formatted "documents"). If it was a regular XML file I think I would be able to figure it out with a solution like this but since the above code is not in regular XML format I'm stuck.

What I'm trying to do is import data into postgresql, and from what I gather it would be easier to import this information if it's in CSV format, (if there's another way, please let me know). What I need is to separate out "id", "url" "title" and "text/body".

Bonus question: the first line in the text/body is the title of the document, would it be possible to remove/manipulate this first line in the conversion?

Thanks!

Answer 1

As far as Python is concerned:

Given an XML file (thedoc.xml) like:

<?xml version="1.0" encoding="UTF-8"?>
<docCollection>
    <doc id="123" url="http://url.org/thing?curid=123" title="Farenheit451"> 
    Farenheit451

    It was a pleasure to burn...
    </doc>

    <doc id="456" url="http://url.org/thing?curid=456" title="Sense and sensitivity"> 
    Sense and sensitivity

    It was sensibile to be sensitive &amp; nice...
    </doc>        
</docCollection>

And a script (thecode.py) using lxml, as:

from lxml import etree
import pandas
import HTMLParser 

inFile = "./thedoc.xml"
outFile = "./theprocdoc.csv"

#It is likely that your XML might be too big to be parsed into memory,
#for this reason it is better to use the incremental parser from lxml.
#This is initialised here to be triggering an "event" after a "doc" tag
#has been parsed.
ctx = etree.iterparse(inFile, events = ("end",), tag=("doc",))

hp = HTMLParser.HTMLParser()
csvData = []
#For every parsed element in the "context"...
for event, elem in ctx:
    #...isolate the tag's attributes and apply some formating to its text
    #Please note that you can remove the cgi.escape if you are not interested in HTML escaping. Please also note that the body is simply split at the newline character and then rejoined to ommit the title.
    csvData.append({"id":elem.get("id"),
                    "url":elem.get("url"),
                    "title":elem.get("title"),
                    "body":hp.unescape("".join(elem.text.split("\n")[2:]))})
    elem.clear() #It is important to call clear here, to release the memory occupied by the element's parsed data.

#Finally, simply turn the list of dictionaries to a DataFrame and writeout the CSV. I am using pandas' to_csv here for convenience.
pandas.DataFrame(csvData).to_csv(outFile, index = False)

It will generate a CSV (theprocdoc.csv) that looks like:

body,id,title,url
        It was a pleasure to burn...    ,123,Farenheit451,http://url.org/thing?curid=123
        It was sensibile to be sensitive...    ,456,Sense and sensibility,http://url.org/thing?curid=456

For more information (and since I cannot format links within inline comments) please see lxml.etree.iterparse, cgi.escape, pandas.DataFrame.to_csv.

Hope this helps.