Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
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
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 03/07/2011 12:06 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

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



 Category Survey
 03/07/2011 03:33 PM
User is offline View Users Profile Print this message

Author Icon
Rick Johnson
AUTOMATE EMPLOYEE

Posts: 2757
Joined: 07/14/2008

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.

 Category Survey


-------------------------
- Rick Johnson
Network Automation
 03/08/2011 06:18 AM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

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"?>
<!-- TFOMS-PM-180924609 :: Document Genetics :: 14043 -->
<!-- Produced by Formate Vr 2.15.5 at 13:59:10 16-Nov-2010 -->
<!-- coments if required -->
<PV-DataCheck-Results>
 <Header>
  <PMID>PMID- 3447940</PMID>
  <Date>23/11/2010 00:00:00</Date>
  <Title>Iodine in contrast agents and skin disinfectants is the major cause forhypothyroidism in premature infants during intensive care.</Title>
  <Country>   </Country>
  <NOP>  </NOP>
  <Drug>  Omnipaque</Drug>
  <Expidite>No</Expidite>
  <Age> </Age>
  <Sex>  </Sex>
  <DateRecived>23/11/2010 00:00:00</DateRecived>
  <Manufacturer> GE HealthCare Ltd</Manufacturer>
  <PVMJob>PVM000000653</PVMJob>
  <PVCorSJob>PVM000000653</PVCorSJob>
  <AdverseEvent> </AdverseEvent>
  <NoExpiditeReson> </NoExpiditeReson>
  <Auther>l Allemand D</Auther>
  <PubDate></PubDate>
  <CaseORstudy>Journal article</CaseORstudy>
  <AuditPath>C:\PV-Website\JobAttachments\Audit\PVS000005065-Audit.pdf</AuditPath>
  <Status>Approved</Status>
  <Login>PVSOLUTIONS\gmohammed</Login>
  <LitRef>l Allemand DIodine in contrast agents and skin disinfectants is the major cause forhypothyroidism in premature infants during intensive care.Horm Res. 1987;28(1):42-9.</LitRef>
  <CofO> </CofO>
  <Notes> GE Healthcare-Omnipaque pediatric-17-Nov-2010  Selected by PVSOLUTIONS\gmohammed on 23/11/2010  -----------------------------------  Published  Selected by PVSOLUTIONS\gmohammed on 23/11/2010  -----------------------------------  </Notes>
  <AtoN> </AtoN>
  <SafetyReort> Hypothyroidism reported</SafetyReort>
  <Prioroty>Yes</Prioroty>
  <ClinicalStudy>No</ClinicalStudy>
  <StudyTYpe>Journal article</StudyTYpe>
  <Comments> Pediatric</Comments>
  <NoSelectReason>N/A</NoSelectReason>
  <JOB>PVS000005065</JOB>
 </Header>
 <MedicalHistory>
  <MHLine></MHLine>
 </MedicalHistory>
</PV-DataCheck-Results>

Grant



 Category Survey
 03/08/2011 09:48 AM
User is offline View Users Profile Print this message

Author Icon
Rick Johnson
AUTOMATE EMPLOYEE

Posts: 2757
Joined: 07/14/2008

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.


 Category Survey


-------------------------
- Rick Johnson
Network Automation
 03/08/2011 11:50 AM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

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



 Category Survey
 03/08/2011 02:00 PM
User is offline View Users Profile Print this message

Author Icon
JosephArndt
Journeyman (50-99)

Posts: 61
Joined: 10/29/2010

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.



 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 03/08/2011 02:16 PM
User is offline View Users Profile Print this message

Author Icon
Rick Johnson
AUTOMATE EMPLOYEE

Posts: 2757
Joined: 07/14/2008

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.

 Category Survey



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
 03/08/2011 03:29 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

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



 Category Survey
 03/08/2011 03:32 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

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



 Category Survey
 03/08/2011 04:24 PM
User is offline View Users Profile Print this message

Author Icon
Rick Johnson
AUTOMATE EMPLOYEE

Posts: 2757
Joined: 07/14/2008

Hi Grant,

I edited my previous message and re-pasted the steps.

Are you able to see them?

 Category Survey


-------------------------
- Rick Johnson
Network Automation
 03/08/2011 04:36 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

Sorry rick im unable to see



 Category Survey
 03/08/2011 04:37 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

I able to see now Rick

many thanks for this

Im sure it will solve a lot of issues

Thanks again

Grant



 Category Survey
 03/08/2011 04:37 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

I able to see now Rick

many thanks for this

Im sure it will solve a lot of issues

Thanks again

Grant



 Category Survey
 03/08/2011 04:37 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

I able to see now Rick

many thanks for this

Im sure it will solve a lot of issues

Thanks again

Grant



 Category Survey
 03/08/2011 04:56 PM
User is offline View Users Profile Print this message

Author Icon
GrantM
Student (5-19 posts)

Posts: 12
Joined: 11/15/2010

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



 Category Survey
 03/09/2011 06:19 AM
User is offline View Users Profile Print this message

Author Icon
JosephArndt
Journeyman (50-99)

Posts: 61
Joined: 10/29/2010

You'll loop the files using automate.



 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 02/13/2016 12:30 AM
User is offline View Users Profile Print this message

Author Icon
ZeneDee
n00b (>6 posts)

Posts: 1
Joined: 02/13/2016

Try rhis one......XML to Excel

Zene



 Category Survey
AutoMate 7 version:
Windows version:
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1350 guests browsing this forum, which makes a total of 1350 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog