Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Parsing data from xml file in folders and subfolders

    Hi,
    I am newbie and not a programmer.
    I have an XML files in folder and sub folders and I'm trying to parse particular information from this file. I can do this manually but can't figure out a macro that will allow me to do this. I attached the lines tags that I will have to pull out of many files from folders and sub folders. Search criteria is "Amount, Serial No,TruncatingRTNo".Get the search from userform, Any Ideas?

    The search folders tree:
    Main folder
    Folder1 ---> *.xml data file
    Folder2----> *.xml data file
    Folder3----> *.xml data file

    XML Data Sample:
    Code:
    <?xml version="1.0" encoding="utf-8"?>
    <FileHeader xmlns="urn:schemas-xxx-com:xxxxx:xxx:FileStructure:020001" VersionNumber="020001" TestFileIndicator="P" CreationDate="13012009" CreationTime="172852" FileID="0000000001">
      <Item ItemSeqNo="09011340010009" PayorBankRoutNo="00704524" Amount="398000" AccountNo="000003850010205" SerialNo="000512" TransCode="03" PresentingBankRoutNo="00400019" PresentmentDate="13012009" CycleNo="01"NumOfImageViews="2" ClearingType="01" DocType="D" CurrencyInd="LYD" IQAIgnoreInd="0" CashValueInd="1" TruncatingRTNo="00405117" SpecialHandling="00" RepresentmentCnt="0" MICRRepairFlags="000000">
        <AddendA BOFDRoutNo="00400019"BOFDBusDate="13012009" DepositorAcct="0000534983"/>
                    
    SOME MORE STUFF HERE............
    
    <FileHeader xmlns="urn:schemas-xxx-com:xxxxx:xxx:FileStructure:020001" VersionNumber="020001" TestFileIndicator="P" CreationDate="13012009" CreationTime="172852" FileID="0000000001">
      <Item ItemSeqNo="09011340010010" PayorBankRoutNo="00704524" Amount="880000" AccountNo="000003450010876" SerialNo="0000036" TransCode="13" PresentingBankRoutNo="00400019" PresentmentDate="13012009" CycleNo="01"NumOfImageViews="2" ClearingType="01" DocType="D" CurrencyInd="LYD" IQAIgnoreInd="0" CashValueInd="1" TruncatingRTNo="00405118" SpecialHandling="00" RepresentmentCnt="0" MICRRepairFlags="000000">
        <AddendA BOFDRoutNo="00400019"BOFDBusDate="13012009" DepositorAcct="0000534983"/>

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Could you elaborate on exactly what you want the code to do? To get the 3 items manually, to me does not seem a large issue.
    Copy the selection in Excel
    Data-text to columns
    delimited on spaces and equal signs
    The amount is in column H, the serial # in Col L and the TruncatingRTN in Col AG.
    You get it by filtering the rows that start with "<Item" in Col B

    Steve

  4. #3
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Could you elaborate on exactly what you want the code to do? To get the 3 items manually, to me does not seem a large issue.
    Steve
    OK, i agree that can be taken for 1 xml file, i have lots of xml files in folder and sub folders & i am searching a particular entry from that xml files based on one of the criteria from the 3 mentioned serial #, amount or TruncatingRTN.

    Infact i need a search form where i will put a serial, amount or TruncatingRTN and it will search the folder and sub-folders for xml file and return the result of search found or not found, and if found it will state the file name in which it has found the search entry.

    fjoham

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    fjohan,

    Here is a workbook that you can use to manage your entire database. It will search a parent folder and subfolders for xml files. Once found, the code will display the file path, file name, file size, and Last modified. Under each display it will perform a search for the three parameters you requested and display their values. You also have the option to search for an additional 5 parameters if you so desire. In the following image, I have added additional search parameters.

    files.png

    At the beginning of each search, a search setup dialogue box will appear and allow you to set the default parent folder, select or deselect the 3 default parameters, and add an additional 5. Please see the attached images. The code will remember your settings and you can have an unlimited number of files and/or sub folder levels.

    Setup files.png

    If you decide that you want to use it, let PM me and I will help you integrate it into your project , otherwise, it is ready to go!

    HTH
    Maud
    Attached Files Attached Files

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    fjohan,

    I was working on this endeavor while you were posting. Although I have presented a slightly different mechanism than what you have described in the above post, you can do a basic spreadsheet search on the returned results. BTW, the code assumes the folders are used for editable files only.

    Good luck,
    Maud
    Last edited by Maudibe; 2013-10-07 at 21:53.

  7. #6
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Maudibe
    Its very nice of you and once again i thank you very much for helping me.
    Sir, The codes are perfect but for more powerful and to get the dot results i want some small changes to it.

    1) Search Only .XML files
    2) Search based on input value of criteria, search for all .xml files in folder & sub-folder to the last row of each file for getting value.

    Thanks.
    search.png

  8. #7
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    fjohan,

    I had set it up to search for all values for a parameter instead of a certain values for a parameter. It makes sense with what you are looking to do. I had assumed that the folder would be a dedicated folder/subfolder for XML files. I will take a look at it tonight.

    Maud

  9. #8
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    fjohan,

    I had set it up to search for all values for a parameter instead of a certain values for a parameter. It makes sense with what you are looking to do. I had assumed that the folder would be a dedicated folder/subfolder for XML files. I will take a look at it tonight.

    Maud
    Hello Maudibe,
    If you have updated the file please forward me.

  10. #9
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    fjohan,

    I had set it up to search for all values for a parameter instead of a certain values for a parameter. It makes sense with what you are looking to do. I had assumed that the folder would be a dedicated folder/subfolder for XML files. I will take a look at it tonight.

    Maud
    Hi Maudibe,

    If you have updated the file please forward me.

    Thanks in advance.

  11. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts

    Revised Search

    Fjohan,

    Sorry for the delay. Hope this meets your needs better. It will search for the criteria that you selected (and their parameters) in only XML files and return only those files where matches are found.

    Maud

    searchxml1.png

    searchxml2.png
    Attached Files Attached Files
    Last edited by Maudibe; 2013-10-20 at 10:07.

  12. #11
    Lounger
    Join Date
    May 2013
    Posts
    42
    Thanks
    29
    Thanked 3 Times in 3 Posts
    Maudibe,
    VTE_STK_xxxxxxx.xml. Is that Venous ThromboEmbolism and Stroke?

  13. #12
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Alouso,

    Very astute!

  14. #13
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Maud,
    Sorry for delay reply, I did a test run on it & found it very useful, successful script on query search for which i am thankful to you and highly obliged.
    Maud, I need a small change if its possible, when a particular search is given it display the search result is it possible to display the other details of that tag. If like suppose search for SerialNo= '000512' it will also display the Amount, ItemSeqNo,PresentingBankRoutNo,AccountNo in the descending rows.
    Thanks
    Attached Images Attached Images

  15. #14
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    fjohan,

    Here is the revision. The matches for the criteria that you select (image #1) will be displayed along with the additional info you requested from the matched files as well. The criteria that matched will be bolded (image #2).

    Challenging project
    Maud

    Revised1.png

    Revised4.png
    Attached Files Attached Files

  16. #15
    New Lounger
    Join Date
    Oct 2013
    Posts
    17
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    fjohan,
    Challenging project
    Maud

    Hi Maud,
    Thanks for your efforts on this Challenging project, Highly obliged.

    Maud, Sorry to say but in-depth search the script fails to generate result on query.
    I think the import xml files on tab (file) imports only one xml and searches for our query, whereas it should import all the xml files from the folder and search our query. Please have a look into this.
    Thanks in advance.
    Last edited by fjohan; 2013-10-31 at 00:56.

Page 1 of 2 12 LastLast

Posting Permissions

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