XML Import help

[mnowinski]

Using Excel 2007

I am having problems with reformatting imported XML data.

First step is to retrieve an XML data file from a web location. The data is loaded into a separate worksheet. Unfortunately some of the data fields are not in a usable format. To correct the data for my use, I have a separate worksheet where I copy, and if necessary, manipulate some of the data fields in each input row. Each row of this new sheet reads a corresponding row in the XML table.This new sheet is then used to create pivot tables.

The problem I have is that the XML data is dynamic, often changing every time I refresh the input. What this also does is change the XML table range, most importantly the number of rows brought in. When this happens, it causes the formulas in the second worksheet to get scrambled,. By this I mean that one row will have all of it's formulas pointing to a specific row in the XML table. The next row should be offset to the next row, but somehow it gets out of sequence, often jumping any number of rows.depending on how many input rows the XML feed has each time.

Small example:

........Col A....................Col B........................Col c
=XMLTable!A3.......= XMLTable!B3........=XMLTable!C3
=XMLTable!A4.......= XMLTable!B4........=XMLTable!C4
=XMLTable!A10.... = XMLTable!B10......=XMLTable!C10 Here's where the jump takes place.

I can't come up with a way to get around this issue. Any help appreciated.

[Alf]

Perhaps you could upload an XML sample file?

Be aware the Forum uploading setting will not accept XML files so changing file extension to XLS will make it possible to upload. You also need to make forum members who may wish to help you aware this fact so they can rename it back to XLM.

Alf

[mnowinski]

Thanks for the reply Alf. Unfortunately the data is sensitive and I'm not allowed to release it. If there is another way without giving out the data, I'd be glad to do it.

I realize trying to define a situation like this is a bit confusing. If anyone needs further info, I can try to reword it.

Mike

[slx]

you can open the xml file in notepad or word, and edit the sensitive data. Then upload the xml. No one will be able to help you unless they see the xml structure.

[mnowinski]

you can open the xml file in notepad or word, and edit the sensitive data. Then upload the xml. No one will be able to help you unless they see the xml structure.

I've managed to get some data from the file and hope this will suffice. It includes the XML header and one line of data. The actual file will have anywhere from 50-75 similar lines od data.

<?xml version="1.0" encoding="UTF-8"?>

-<AssignmentItems xmlns:schemaLocation="http://www.website.net/datafeed_assignments.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.website.net" total="87">


-<Assignment>

<Id>166757488</Id>

<Status>Ready</Status>

<Location>RJAH</Location>

<From>RJAH</From>

<Destination>RKNW</Destination>

<Assignment>Restricted</Assignment>

<Amount>5</Amount>

<Units>Units</Units>

<ExpireDateTime>2014-03-05 12:34:59</ExpireDateTime>

<Locked>CG</Locked>

<Comment>Some Comment</Comment>

</Assignment>
</AssignmentItems>

[slx]

the file starts and ends with assignmentitem? is the structure like this?
<assignmentitems ..blah...blah...>
<assignment>
...stuff...
</assignment>
<assignment>
...stuff...
</assignment>
<assignment>
...stuff...
</assignment>
</assignmentitems>

The solution to this would be a macro that can parse the xml and will look for specific data pieces to pull, i.e. Units/Status/Location.

[mnowinski]

Yes, that's correct. Each [Assignment]xxxx[/Assignment] set is a unique data row. The {/Assignmentitems] code says that there is no more input data.

I don't have any problem with getting the XML data into columns on the sheet. The problem I have is I have to reformat and parse some of the data and I can't just add it on the end of the XML tables So, what I do is create a new sheet and copy/reformat/parse the individual data fields row by row from the XML table using "=" functions. Ex. =XML!E1 As the XML table is dynamic, there are sometimes more, or less rows when I refresh. When this happens, the copied data formulae somehow get out of sync with the XML table and I can't figure out why, or how to stop it.