![]() |
![]() |
We are currently migrating this forum over to our HelpSystems domain. Please post all new threads in our new HelpSystems Community Portal. |
![]() |
|
![]() |
AutoMate Discussion | ![]() |
help :
faq :
home
|
||
Latest News:
|
latest topics : statistics |
Topic Title: Parse Xml To excel Topic Summary: XMl To Excel Created On: 03/07/2011 12:06 PM Status: Read Only |
Linear : Threading : Single : Branch |
![]() |
![]()
|
![]() |
||||
Hi I need to parse a xml and extract certain nodes to excel is it possible to explain how this is done. Im also struggling to see how i can get all the xml in the folder then loop for each one Any help is appreciated
Many thanks Grant
|
||||
|
||||
![]() |
||||
Hi Grant,
If you can provide some specific details about yout XML files, the names of the node(s) that you wish to extract, and where in a spreadsheet you wanted to place the extracted node data. The general approach would be to use the Loop Folder action to loop your folder for *.XML files (or all files if the folder only contains XML files that you wish to extract). You would have the XML filename stored in a variable at this point. The Read XML Node action would get the data from the file referenced in that variable. Here you would retrieve that node data into another variable. The Set Cell action would place the node data into an Excel cell. If you were to include an example XML file and some of your requirements, we would be able to provide you more specific assistanmce.
------------------------- - Rick Johnson Network Automation |
||||
|
||||
![]() |
||||
Hi Rick I have attched the xml that is produced by our system , its a xml that is produced by the workflow we use and from this i need to know how to read specific tags and then populate that into excel and i also need to create folders based on criteria within the xml I currently use another automation program which is fairly simple to use and when i read the xml via this its a simple task to parse the xml and populate excel and create folders But this is a very cheap program which is ok for a few hundred files but i am talking many thousand a week and this is causing some grief I understand the logic in how i would need to do this but cant see how i say to read all xml and perform the tasks i need thereafter as the setup is different to what i have used previously The main difference for me is the program i use i create the task and from there i create the variable i want to populate but automate is slightly different so im stumbling a bit in how to achieve this sorry cant attach file so copied instead Many thanks for your input <?xml version="1.0" encoding="UTF-8"?> Grant
|
||||
|
||||
![]() |
||||
Hi Grant,
I'm not sure yet which data from your XML wanted to be migrated into Excel, and what the scheme is for the folders to be created. Please state explicitly which data are to be read from the XML, how it should be placed into Excel, and how the folder names should be formatted. To work with existing XML files, the initial step would be to use the Start XML Session action ans select the XML File (or reference from a variable within a loop as previously described). There are a ouple of options at this point, the Read Node/Set Cell approach or the XML Node to Dataset/Dataset to Excel approach - once more is known about the mechanics of your procedures, the proper reccommendation can be given. You can use the Attach Code button to paste any XML/Task Step/Script data.
------------------------- - Rick Johnson Network Automation |
||||
|
||||
![]() |
||||
Hi Rick I need to read all the values in the Xml and paste all to excel So the first value would be to column a the second value to column b etc i have worked the logic out with writing to excel now but still struggling with the reading of Xml Im sure once i see how its done i can work it out Grant
|
||||
|
||||
![]() |
||||
Some general code to help you get started. I actually use a .vbs script outside of AutoMate, but it should translate well to BASIC. I believe what I use this for is a .xls that's written entirely in xml, so as long as xml is the whole body of a document, you shouldn't have any problem.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||
|
||||
![]() |
||||
Hi Grant,
I saved your XML example into a file called QA.XML to use with the task steps whoch I have provided. The steps presume that the nodes which you want to access are always inside the /PV-DataCheck-Results/Header/ node, although that excludes the MedicalHIstory and MHLine nodes, but you can change the XPath to //* if needed. The loop at the end displays each node's name and value, you could use the same fields in that loop to set Excel cell data.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) ------------------------- - Rick Johnson Network Automation Edited: 03/08/2011 at 04:23 PM by Rick Johnson |
||||
|
||||
![]() |
||||
Hi Joseph Many thanks for this Im currently learning the scripting side of this and this is a great help many thanks again for your help Grant
|
||||
|
||||
![]() |
||||
Hi Rick Many thanks again for your help Once is see how you can pull files and read etc im sure i can follow the rest easy enough Well for the simple tasks at any least Also when you attached the project where would i see this as im unable to locate Grant
|
||||
|
||||
![]() |
||||
Hi Grant,
I edited my previous message and re-pasted the steps. Are you able to see them?
------------------------- - Rick Johnson Network Automation |
||||
|
||||
![]() |
||||
Sorry rick im unable to see
|
||||
|
||||
![]() |
||||
I able to see now Rick many thanks for this Im sure it will solve a lot of issues Thanks again Grant
|
||||
|
||||
![]() |
||||
I able to see now Rick many thanks for this Im sure it will solve a lot of issues Thanks again Grant
|
||||
|
||||
![]() |
||||
I able to see now Rick many thanks for this Im sure it will solve a lot of issues Thanks again Grant
|
||||
|
||||
![]() |
||||
Rick i have pasted this and it works great but only if i select a individual xml How do i select all Xml within that folder have written the Xml to excel and its great, just what i need but as mentioned i need to grab all
|
||||
|
||||
![]() |
||||
You'll loop the files using automate.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||
|
||||
![]() |
||||
|
AutoMate Discussion
» AUTOMATE » AutoMate 7
»
Parse Xml To excel
|
![]() |
FuseTalk Enterprise Edition v4.0 - © 1999-2021 FuseTalk Inc. All rights reserved.