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

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

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

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

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

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)
Dim theFile theFile = (put the full path of your document here) Set rsData = CreateObject("ADODB.Recordset") Set rsTN = CreateObject("ADODB.Recordset") Set oConn = CreateObject("ADODB.Connection") 'connection string for html doc With oConn .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties ("Extended Properties").Value = "HTML Import;HDR=No;IMEX=1""" .Open theFile End With Set rsTN = oConn.OpenSchema(20) 'use this to get schema info from a document - basically returns the sheet name if not a .txt or .csv file Do Until rsTN.EOF iCount = iCount + 1 ReDim Preserve TableNames(iCount) TableNames(iCount) = "[" & rsTN(2) & "]" Result = TableNames(1) rsTN.Movenext Loop 'The following code outputs the text in csv format, but you can stick it in an xlsx file by using the SQL INSERT command. Let me know if you need me to write that out for you Set objFile = myFSO.OpenTextFile(filePath & theFileName & ".txt", 2, True) Set rsData = oConn.Execute("SELECT * FROM " & Result) While Not rsData.EOF For each field in rsData.Fields details = details & chr(34) & field & chr(34) & "," next details = Left(details, Len(details)-1) objFile.WriteLine(details) details = "" rsData.movenext Wend

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)
<AMVARIABLE NAME="vXMLName"></AMVARIABLE> <AMVARIABLE NAME="vXMLValue"></AMVARIABLE> <AMXMLSTARTSESSION XMLDOCUMENTFROM="ExistingFile" FILE="D:\QA\Grant\QA.XML" /> <!-- Get a list of all nodes --> <AMXMLNODEITERATOR XPATH="//*" RESULTDATASET="MyXMLNodes" VARVALUETYPE="XpathValue" /> <!-- Process the list --> <AMLOOP TYPE="DATASET" DATASET="MyXMLNodes"> <AMSET VARIABLENAME="vXMLName">%MyXMLNodes.Value%</AMSET> <AMXMLREADNODE XPATH="%MyXMLNodes.Value%" RESULTVARIABLE="vXMLValue" /> <AMSHOWDIALOG>Node: %vXMLName% Value: %vXMLValue%</AMSHOWDIALOG> </AMLOOP> <AMXMLENDSESSION />

-------------------------
- 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

Category Survey

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

Hi Grant,

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

Are you able to see them?

Category Survey

-------------------------
- Rick Johnson
Network Automation

Sorry rick im unable to see

Category Survey

I able to see now Rick

many thanks for this

Im sure it will solve a lot of issues

Thanks again

Grant

Category Survey

I able to see now Rick

many thanks for this

Im sure it will solve a lot of issues

Thanks again

Grant

Category Survey

I able to see now Rick

many thanks for this

Im sure it will solve a lot of issues

Thanks again

Grant

Category Survey

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

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)
<AMLOOP TYPE="FOLDER" FOLDER="\\(folder path))" RESULTVARIABLE="thefile"> (parse xml sheet, then move to the next one in the file until all are processed) </AMLOOP>

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

Zene

Category Survey AutoMate 7 version: Windows version: