I'm in the process of figuring out a way of eliminating a unwanted "copy/paste" process.
I used to copy paste the data from a HTML tabular report into my excel file that is calculating;
- a suitable clash name,
- assigning who is responsible.
- time scheduleing.
- 2D Plot of selected clashes
I'm hoping to use Power Query to eleminate the copy paste process.
I've exported the clashes that have been detetcted in navisworks manage.
I Prefer to use the XML - Format. This file format includes more usable data than a HTML report.
I've tried to export XML as individual files and as combined. Same issues for both.
The XML files combined with Power Query should eliminate the copy paste process, and simply replace the data "automaticly" when exporting new reports from naviswork.
However the XML Data doesent seem to be faulty.
Using Power Query Indeks and modulus functions i've been able to expand/reformat the XML Data into a table.
Column 1 = Testname
Column 2 =Status
Column 3 = Clash Name
Column 4 =Date Detected
Column 5 = Comments
Column 6 = Distance
Column 7 = Image file name & Location
Column 8 = X
Column 9 = Y
Column 10 = Z
Column 11 = Element 1 ID
Column 12 = Element 1 Layer/Level
Column 13 = Element 1 Type Comment
Column 14 = Element 1 Revit Category
Column 15 = Element 1 Revit Family
Column 16 = Element 1 Revit Type
Column 17 = Element 1 Revit Workset
Column 18 = Element 1 Revit File
Column 19 = Element 1 Revit GUID
Column 20 = Element 1 ID
Column 21 = Element 1 Layer/Level
Column 22 = Element 1 Type Comment
Column 23 = Element 1 Revit Category
Column 24 = Element 1 Revit Family
Column 25 = Element 1 Revit Type
Column 26 = Element 1 Revit Workset
Column 27 = Element 1 Revit File
Column 28 = Element 1 Revit GUID
This works perfectly for the first 30% of the clashes.
But at one point the data is beginning to be inserted into the wrong columns.
I suspect it is because of Power Query Modulus function VS inconsistency in number of "rows" when exporting XML.
Any suggestions as to how i can get the data from my XML reformated to a excel table correctly?
Solved! Go to Solution.
Solved by dgorsman. Go to Solution.
Don't know about "Power Query" - sorry. When I deal with XML data it's usually programmatically using either the MSXML DOM (ActiveX) or System.XML namespace (dotNET), using XPath to select nodes.
XML doesn't have "rows" like a table in Excel, which is one of it's benefits. You don't need to have exactly the same data in every element; if the element doesn't apply it doesn't have to be present, or if other elements are applicable they can be added without affecting the others.
You should be searching by element name, content, and relative position and then iterating the results. See https://www.w3schools.com/xml/xpath_intro.asp for syntax.
Thanks for the inputs - I managed to find a way based on your "search for names" comment.
I was able to make it work using some conditional if formulas based on the element names.
Works like a charm.
Can't find what you're looking for? Ask the community or share your knowledge.