I have created a python script to create a text file which has the Tag:Value of Tag read from thousands of xml datasets. Then I created an excel form to allow me to view the data in individual columns. Unfortunately as the xml's are not structured the same, a simple excel clean up routine does not work.

I want to create a macro or formulae (maybe with vlookup) that will first run through the whole dataset and then create a unique list of tags. It should then run through the text file and populate the value of each tag in to the relevant tag column.

Can anyone help?

I have attached.

xml_log.txt - a sample text dataset. Each record starts with (:;:) and ends with (;:;) and (^) is the delimeter. Tag and value of tag is seperated by (::). The first 2 records are the number and the file path. These are unique.

xml.xls - a sample excel sheet. anzmeta_xml_log worksheet is the raw data import. Cleaned is the best I could do for cleaning up the data.

An added complication is that some of the xml's have non-unique tag names so the tag that it is within needs to be also considered.

Issue row examples in excel file. Row/Column refers to the log worksheet not the cleaned worksheet.

Row 10: Column A0 : Item 17 - the tag should be (nondig) but is (acconst)
Row 60: Column L : Item 113 - the tag should be (uniqueid) but is (Esri)