Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Extracting values from XML file

    Hi,

    I have around 50 to 60 .xml files in folder and need a macro to extract only 3 values from xml tags to spreadsheet.

    1. -<Item IQAIgnoreInd..... RoutNo="000002166" value "000002166" in Column A2 as Column A1 header RoutNo.
    This RoutNo="000002166" will be extracted from the first tag of the file.

    2. <FileSummary TotalItemCount="50" TotalAmount="113998000" /> value "50" in Column B2 header TotalItemCount & this "113998000" converted with 2 decimal places 1139980.00 in Column C2 header as TotalAmount
    This tag <FileSummary .... is located at the end of the file.

    After collecting all this values from multiple xml files sum up column C2 to the end.

    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    You can use XSL to do this. It will only import the values you need.
    http://aspalliance.com/471_Convert_X...et_Using_XSL.3
    http://stackoverflow.com/questions/3...-it-into-excel

    cheers, Paul

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Z,

    Do you have a sample .XML file you can post?

  4. #4
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maudibe for the reply.

    I have attached the sample xml file.

    Thanks in advance.
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Z,

    Here is the code that will allow you to open an XML file and extract the data you desire. Using a dialogue box, select the XML file. The values will be retrieved and formatted in the columns you specified.

    Note: Where indicated in the code, change the path to the folder where the XML files will reside. Make sure a "\" is at the end of the path as shown.

    HTH,
    Maud

    ZMagic_XML.png


    Code:
    Sub GetXML()
    '--------------------------------------
    'DECLARE AND SET VARIABLES
    Dim FileToOpen As String
    Dim OldDir As String, Path As String
    Path = "C:\Users\Maudibe\Desktop\"  'CHANGE PATH TO FOLDER LOCATION
    OldDir = CurDir
    With Worksheets("Base")
    '--------------------------------------
    'GET XML FILE
    ChDir (Path)
    FileToOpen = Application.GetOpenFilename _
        (Title:="Choose an XML file to import", _
        FileFilter:="XML Files *.xml (*.xml),")
    If FileToOpen = "False" Then
        Exit Sub
    Else
        ChDir (OldDir)
        ActiveWorkbook.XmlImport URL:=FileToOpen, _
            ImportMap:=Nothing, Overwrite:=True, Destination:=.Range("$A$1")
    End If
    '--------------------------------------
    'GET VALUES
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    LastCol = .Cells(1, Application.Columns.Count).End(xlToLeft).Column
    For I = 1 To LastCol
        Select Case .Cells(1, I)
            Case "RoutNo"
                Cells(LastRow, 1) = .Cells(2, I)
            Case "TotalItemCount"
                Cells(LastRow, 2) = .Cells(2, I)
            Case "TotalAmount"
                Cells(LastRow, 3) = .Cells(2, I)
        End Select
    Next I
    .Cells.ClearContents
    End With
    End Sub
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maud.

    The values are retrieved in order, but in column C decimal places are added it does not convert it. Please look to my sample.xml file the TotalAmount is 603320 when appended in spreadsheet it will be 6033.20

    Secondly i want all the xml files in the folder to be extracted one after the another and values to append in spreadsheet, whereas now i have to select individual file.

    And lastly if in column A [RoutNo] if more than one/similar RoutNo is present then to create a summary in Column D and E. And finally to sum Col B,C,D & E.

    example.png
    example.png

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Z,

    Couple of quick questions:

    1. Will the XML files always be in the same folder?
    2. If so, will the source folder for the XML files always be the same?
    3. Will the folder have any other types of files or XML files that you do not want included?
    4. If you want sums based on RoutNo in the format that you show, can I assume that the list needs to be sorted by RouteNo?

    The decimal was an easy fix by just dividing the retrieved number by 100 as well as summing the columns. Answer the above questions and I will get to it shortly after.

    Maud

  8. #8
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maud.

    Quote Originally Posted by Maudibe View Post
    Z,

    Couple of quick questions:

    1. Will the XML files always be in the same folder? YES
    2. If so, will the source folder for the XML files always be the same? YES
    3. Will the folder have any other types of files or XML files that you do not want included? No other type of files in the folder Only XML files will be there in the folder whose values we want to extract, around 50 to 60 xml files
    4. If you want sums based on RoutNo in the format that you show, can I assume that the list needs to be sorted by RouteNo? YES

    The decimal was an easy fix by just dividing the retrieved number by 100 as well as summing the columns. Answer the above questions and I will get to it shortly after.

    Maud
    Many Thanks for supporting.
    Thanks

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Z,

    Here is the revised file reflects the changes you specified.

    HTH,
    Maud

    ZMagic_XML_Revised2.png

    Addendum: Change cell H1 to the path of the folder where the XML files are located. This will be used in the code so you now have the ability to change folders if you like each run. Make sure the path ends with a \
    Attached Files Attached Files
    Last edited by Maudibe; 2015-01-25 at 17:32. Reason: Addendum

  10. #10
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Maud for helping, and sorry for delay reply.

    The summary in Col D & E are done at every alternate whether they are of group or not, Please group only similar's summary from column A [RoutNo] no need of Totals in column D & E. {D2 & E2}
    To create a empty row after every group or singles.
    To clear my point i upload 2 images.

    Thanks in advance.
    Error Summary.pngchanges required.png

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    z,

    Looks like you are using a different xml file than your sample. If they are variations in their formats, it could account for the groupings. Note the image in my post in post #10 compared to yours. Could you post several xml samples?

    Maud

  12. #12
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Maud,
    Thanks for reply,

    Uploaded sample files, hope to have a favourable response.,

    Thanks in advance.
    Attached Files Attached Files

  13. #13
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Uploaded sample files, hope to have a favourable response.

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Z,

    I have corrected the alternating groupings and have coded to insert a line between groupings.

    Maud

    ZMagic_XML1.png
    Attached Files Attached Files

  15. The Following User Says Thank You to Maudibe For This Useful Post:

    zmagic (2015-02-14)

Posting Permissions

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