How can I build an sqlite table from this xml/txt file using python?

155
August 07, 2017, at 1:49 PM

I have an xml/txt file like this:

<text id="32a45" language="ENG" date="2017-01-01" time="11:00" timezone="Eastern">
<s id="1">
foo
bar
</s>
<d>
11235
</d>
<text id="32a47" language="ENG" date="2017-01-05" time="1:00" timezone="Central">
<s id="2">
foo
bar
</s>
<d>
11235
</d>
<text id="32a48" language="ENG" date="2017-01-07" time="3:00" timezone="Pacific">
<s id="3">
foo
bar
</s>
<d>
11235
</d>

I want to build an sqlite table like the following using python:

id  language    date        timezone    s           d
32a45   ENG     2017-01-01  Eastern     foo bar     11235
32a47   ENG     2017-01-05  Central     baz qux     11235
32a48   ENG     2017-01-07  Pacific     foo bar     11235

Any idea how can I do this? I cannot use xmltree module because the xml tags in the original file is messed up. I would really appreciate the help. Thanks.

Edit: I can easily take each text as a list inside a list. Like this:

['<text id="32a45" language="ENG" date="2017-01-01" time="11:00" timezone="Eastern">', '<text id="32a47" language="ENG" date="2017-01-05" time="1:00" timezone="Central">', '<text id="32a48" language="ENG" date="2017-01-07" time="3:00" timezone="Pacific">']

But I don't know how to take the id, language etc. from each list separately.

Answer 1

Redirected from here:

How can I make sublists from a list based on strings in python?

import xml.etree.ElementTree as ET
import pandas as pd
strings = ['<text id="32a45" language="ENG" date="2017-01-01" time="11:00" timezone="Eastern">',
'<text id="32a47" language="ENG" date="2017-01-05" time="1:00" timezone="Central">',
'<text id="32a48" language="ENG" date="2017-01-07" time="3:00" timezone="Pacific">']
cols = ["id","language","date","time","timezone"]
data = [[ET.fromstring(string+"</text>").get(col) for col in cols] for string in strings]    
df = pd.DataFrame(data,columns=cols)
READ ALSO
MySQL Query join 3 Tables (2 Tables have Data and 1 dont have Data)

MySQL Query join 3 Tables (2 Tables have Data and 1 dont have Data)

I have make a Query To Find NetAmount2017, NetAmount2016 And Budget(2017) to represent it into a graph bar chart via HTMLHere is the Query

180
How to create parent and two child tables mysql

How to create parent and two child tables mysql

I have a parent table named grade:

185
preg_rlike not matching characters like &#242; , &#237; , &#232; etc

preg_rlike not matching characters like ò , í , è etc

Can you hep me to match special characters like ò , í , è with mysql preg_rlike

143