Parsing data from xml file in folders and subfolders

Topic

New Reply

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:

  
    
                
SOME MORE STUFF HERE…………


  
    

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

35085-files

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.

35083-Setup-files

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

Reply | Quote

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.
35091-search

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

35187-searchxml1

35188-searchxml2

Reply | Quote

Maudibe,
VTE_STK_xxxxxxx.xml. Is that Venous ThromboEmbolism and Stroke?

Reply | Quote

Alouso,

Very astute!

Reply | Quote

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

Reply | Quote

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

35265-Revised1

35267-Revised4

Reply | Quote

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.

Reply | Quote

Will check it out.

Maud

Reply | Quote

fjohan,

As you have described, the code does load only one xml file at a time on to the File sheet and does a search. It then clears it and loads the next, and repeats the search and so on until all the xml files have been searched. if matches are found, it lists them on the XML File sheet. I have tested it repeatedly with different folders and subfolders and with different files; it returns an accurate query. In the search form, are you sure you are pointing to the correct folder? Does the folder contain files with the extension .XML or .xml? In the recoding, the only changes that I made was after a match was discovered so it listed the additional parameters you were looking for. So it still should work for you.

Give it another try and let me know. If not, we will find the problem. If so, could you send a sample file different from the one in your opening post?

Maud

Reply | Quote