I have created a text file by de constructing a number of different xml style documents. I have recorded the tag::text together as each file has the tags in a slightly different order. The key is the name of the original xml file.

Is it possible to reconstruct this data in excel so that all files have the columns properly ordered.

An excel sample and raw data is available here.

While the data in the example is localized the final script will be useful for anyone who has a complex set of xml files to convert to a text database as the process will work on any input xml regardless of the attribution.

So if given the following (or any text with unique delimiters) can I run through and capture the tags?

:;:^1^L:\Vector_Data\Administrative\Boundaries\Federal_Govt\COM_ELB_region.shp_BaseMetadata.xml^citeinfo::None^descript::None^timeperd::None^status::None^distinfo::None^dataqual::None^cntinfo::None^metainfo::None^uniqueid::None^title::COM_ELB_region.shp^origin::None^custod::Tablelands Regional Council ^jurisdic::None^;:;

(in this case ^ is the column separator and :: is the tag::text separator)

I need it recreate the dataset in a new worksheet where each record (xml name/file path is unique) has just the text in the appropriate row for it's tag pair. I can not hard code the tag::text pairs or field order as the xml's are different. So it will need to create the basic worksheet based on the first dataset and then add to this when new tags are found.