# Importing tabular data that has been exported from Evernote

In Evernote you can embed HTML tables in the note. You can then export them to an xml format with the extension enex. I am trying to get the data from the table imported into Mathematica. Here is the data from a small test note I made and exported as test.enex:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE en-export SYSTEM "http://xml.evernote.com/pub/evernote-export.dtd">
<en-export export-date="20120812T214410Z" application="Evernote/Windows" version="4.x">
<note><title>test</title><content><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE en-note SYSTEM "http://xml.evernote.com/pub/enml2.dtd">

<en-note style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><table border="1" cellpadding="2" cellspacing="0" width="100%"><tbody><tr><td valign="top"><b>heading 1</b></td><td valign="top"><b>heading 2</b></td><td valign="top"><b>heading 3</b></td></tr><tr><td valign="top">text 1</td><td valign="top">12.34</td><td valign="top">5</td></tr><tr><td valign="top">text 2</td><td valign="top"><br/></td><td valign="top">9</td></tr><tr><td valign="top">text 3</td><td valign="top">13.33</td><td valign="top">10</td></tr></tbody></table></en-note>]]></content><created>20120812T144746Z</created><updated>20120812T214402Z</updated><tag>testtag</tag></note></en-export>


I am not really sure how to get this imported into Mathematica and then get the data extracted from the embedded HTML table. Any help would be appreciated.

EDIT: Slightly changed the test data so that there is a missing data point, which it turns out happens in my real dataset.

-

Below I use ImportString as a proxy for Import so that we don't need external files for the demonstration, but Import should work similarly.

imp = ImportString["<- paste your XML code between the double quotes ->", {"XML", "CDATA"}]

(* ==>
{"test", "<?xml version=\"1.0\" encoding=\"UTF-8\"?> <!DOCTYPE \
en-note SYSTEM \"http://xml.evernote.com/pub/enml2.dtd\"> <en-note \
style=\"word-wrap: break-word; -webkit-nbsp-mode: space; \
-webkit-line-break: after-white-space;\"><table border=\"1\" \
valign=\"top\">text 1</td><td valign=\"top\">12.34</td><td \
valign=\"top\">5</td></tr><tr><td valign=\"top\">text 2</td><td \
valign=\"top\">12.1</td><td valign=\"top\">9</td></tr><tr><td valign=\
\"top\">text 3</td><td valign=\"top\">13.33</td><td \
valign=\"top\">10</td></tr></tbody></table></en-note>", \
"20120812T144746Z", "20120812T144859Z", "testtag"}
*)


Apparently we need the second element here. We'll try to import that as HTML and get the data out of it:

ImportString[imp[[2]], {"HTML", "Data"}]


{{"heading 1", "heading 2", "heading 3"}, {"text 1", 12.34, 5}, {"text 2", 12.1, 9}, {"text 3", 13.33, 10}}

-
It works almost perfectly. The one edge case it stumbles upon is the one I didn't think to include in the text example: missing data. If there is a value missing in a row it just leaves it out making that particular sub-list shorter, which mean the columns end up misaligned. –  Mr Alpha Aug 12 '12 at 21:56
@MrAlpha How is the missing data marked? –  Sjoerd C. de Vries Aug 12 '12 at 22:02
The cell is empty, which means Evernote puts a simple <br /> tag in the cell. –  Mr Alpha Aug 12 '12 at 22:05
@MrAlpha I think using "FullData" instead of "Data" might help. –  Sjoerd C. de Vries Aug 12 '12 at 22:07
That works! It ends up wrapping it in a bunch of {}, but that is just and excuse for First@First@First@First. Thank you. I must have spent hours trying to get this to work, and what I could come up with was much more brittle and complex and still kept messing up the data. I must admit the second argument for Import remains a mystery for me. –  Mr Alpha Aug 12 '12 at 22:14