+ Reply to Thread
Results 1 to 7 of 7

Pulling in data to excel from XML messages

  1. #1
    Forum Contributor mccrimmon's Avatar
    Join Date
    02-19-2004
    Location
    Scotland
    MS-Off Ver
    2003
    Posts
    249

    Pulling in data to excel from XML messages

    Hello

    Im hoping someone can help point me in the right direction and start me off on this one.

    I have a folder of XML messages and I would like to bring these XML messages into Excel and create a summary report.

    I am wondering how you work with XML messages in Excel and how I only pull in certain fields as the messages contain a lot of information which is irrelevant.

    I have attached a copy of a dummy XML message and also attached an example output.

    From the example you will see I am interested in only pulling in the following tags from the XML message.
    • Status
    • DealRef
    • TradeRef
    • Qty

    Any pointers would be greatly appreciated.

    I am really only interested in how you cut the XML message up at this point as Im sure I can work out how to loop through the folder etc.

    Thanks

    McCrimmon
    Attached Files Attached Files
    McCrimmon

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Pulling in data to excel from XML messages

    Please Login or Register  to view this content.



  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,068

    Re: Pulling in data to excel from XML messages

    Does the attached example help ....



    Regards
    Attached Files Attached Files
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor mccrimmon's Avatar
    Join Date
    02-19-2004
    Location
    Scotland
    MS-Off Ver
    2003
    Posts
    249

    Re: Pulling in data to excel from XML messages

    Quote Originally Posted by snb View Post
    Please Login or Register  to view this content.
    fantastic work snb.

  5. #5
    Forum Contributor mccrimmon's Avatar
    Join Date
    02-19-2004
    Location
    Scotland
    MS-Off Ver
    2003
    Posts
    249

    Re: Pulling in data to excel from XML messages

    A further question snb.

    Should I want to bring in further fields I obviously expand the array and add in the additional start and end tags, however, is it possible to put wild cards in the start tags.


    For example, the below will always start as <NetSttlmAmt ccy=
    However, could have any other currency such as GBP, USD etc before closing out the tag.

    <NetSttlmAmt ccy="EUR">1051698.6</NetSttlmAmt>

    Is there away so that I can have it so its using a wild card on the 3 digit currency code?
    <NetSttlmAmt ccy="*">

    Thanks

    McC

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Pulling in data to excel from XML messages

    No you can't, and in this case it's an advantage:

    Please Login or Register  to view this content.
    results in "<NetSttlmAmt ccy="EUR">1051698.6"

    Please Login or Register  to view this content.
    results in ""EUR">1051698.6"

    if you adjust the resulting string c01 using

    Please Login or Register  to view this content.
    ""EUR">1051698.6" will result in:

    EUR 1051698.6

    I would be very pleased with this result.

  7. #7
    Forum Contributor mccrimmon's Avatar
    Join Date
    02-19-2004
    Location
    Scotland
    MS-Off Ver
    2003
    Posts
    249

    Re: Pulling in data to excel from XML messages

    Quote Originally Posted by snb View Post
    No you can't, and in this case it's an advantage:

    Please Login or Register  to view this content.
    results in "<NetSttlmAmt ccy="EUR">1051698.6"

    Please Login or Register  to view this content.
    results in ""EUR">1051698.6"

    if you adjust the resulting string c01 using

    Please Login or Register  to view this content.
    ""EUR">1051698.6" will result in:

    EUR 1051698.6

    I would be very pleased with this result.
    Thanks for the reply.
    I'll take a look.

    Rep added for this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1