Read XML Export CSV - VisualCron - Forum

Community forum

beechc
2015-09-10T20:46:17Z
Good afternoon,

I'm looking for some assistance with a pet project where I would like to use VisualCron to read an XML file and flatten each of the records to a line in a CSV file.
I've attached a sample of the file I am using which uses an odd structure where all of the elements under <Document> are attributes with name/value pairs.

I have dabbled with the Read XML task an can see where an XPath expression can retrieve a single value, but am lost on the ability to retrieve each of the name/value pairs per record <Package> and then to loop through them all.

  SM1C_xml.txt (116kb) downloaded 69 time(s).

The result I would like is something like this:
1655,1,1,true,10,5,5,1,5,310577,1052783,2015-07-31,E,31G15,59120,1655,1,5,20.0,0.020000001,10,5,5,1
1656,2,1,true,6,3,3,1,3,310675,1052837,2015-07-31,E,31G15,59180,1656,2,3,12.0,0.012,6,3,3,1
1657,3,1,true,4,2,2,0,2,310822,1052931,2015-07-31,E,31G15,59279,1657,3,2,8.0,0.0080,4,2,2,0,
...

Before I invest too much time in my attempt to figure this out, is this something that VC is capable of doing with the Read XML task, or should I put my efforts elsewhere?

Any feedback or examples would be warmly welcomed.

Chris
ErikC
2015-09-11T07:30:08Z
Hi Chris,

With the XML read node task, you can read one node, so you could get one 'Package' out of it wit the use of xpath.
Having that result, you have a partial xml document without the rootelement 'Partition' so it's hard to parse it further with a next xml read node task.

As I'm a .NET progranmmer, I am able to solve this with the C# .NET task in a few easy steps:
using System;
using System.Text;
using System.Xml;
using System.Collections.Generic;

public class Test
{
  public static string ParseXML(string fileName)
  {
    StringBuilder sb = new StringBuilder();
    XmlDocument xDoc = new XmlDocument();
    xDoc.Load(fileName);

    foreach (XmlNode node in xDoc.DocumentElement.ChildNodes)
    {
      //use a temp list to store the values
      List<string> attrValues = new List<string>();

      //using xpath find all attr nodes and add each attribute value to the list
      foreach (XmlNode AttrNode in node.SelectNodes(".//Attr"))
      {
        attrValues.Add(AttrNode.Attributes["value"].Value.ToString());
      }

      //join all the values on the list to one line with a comma between the items
      sb.AppendLine(string.Join(",", attrValues.ToArray()));
    }            

    //return all the lines
    return sb.ToString();
  }
}


You need to add a reference System.Xml using the 'Edit Reference...' button.

If you call the method ParseXML and add the path to your xml file, the result of the .NET task will be:

1655,1,1,true,10,5,5,1,5,310577,1052783,2015-07-31,E,31G15,59120,,1655,1,5,20.0,0.020000001,10,5,5,1
1656,2,1,true,6,3,3,1,3,310675,1052837,2015-07-31,E,31G15,59180,,1656,2,3,12.0,0.012,6,3,3,1
1657,3,1,true,4,2,2,0,2,310822,1052931,2015-07-31,E,31G15,59279,,1657,3,2,8.0,0.0080,4,2,2,0
1658,4,1,true,6,3,3,0,3,310800,1052914,2015-07-31,E,31G15,59259,,1658,4,3,12.0,0.012,6,3,3,0
1659,5,1,true,6,3,3,0,3,310893,1052983,2015-07-31,E,31G15,59339,,1659,5,3,12.0,0.012,6,3,3,0
1660,6,1,true,8,4,4,1,4,310889,1052979,2015-07-31,E,31G15,59336,,1660,6,4,16.0,0.016,8,4,4,1
1661,7,1,true,6,3,3,1,3,310936,1053011,2015-07-31,E,31G15,59369,,1661,7,3,12.0,0.012,6,3,3,1
1662,8,1,true,6,3,3,0,3,310960,1053027,2015-07-31,E,31G15,59390,,1662,8,3,12.0,0.012,6,3,3,0
1663,9,1,true,6,3,3,1,3,311091,1053110,2015-07-31,E,31G15,59477,,1663,9,3,12.0,0.012,6,3,3,1
1664,10,1,true,6,3,3,0,3,311101,1053115,2015-07-31,E,31G15,59483,,1664,10,3,12.0,0.012,6,3,3,0
1665,11,1,true,6,3,3,0,3,311095,1053112,2015-07-31,E,31G15,59479,,1665,11,3,12.0,0.012,6,3,3,0
1666,12,1,true,10,5,5,1,5,311014,1053061,2015-07-31,E,31G15,59426,,1666,12,5,20.0,0.020000001,10,5,5,1
1667,13,1,true,6,3,3,1,3,311105,1053117,2015-07-31,E,31G15,59485,,1667,13,3,12.0,0.012,6,3,3,1
1668,14,1,true,6,3,3,1,3,311163,1053147,2015-07-31,E,31G15,59517,,1668,14,3,12.0,0.012,6,3,3,1
1669,15,1,true,8,4,4,1,4,311155,1053141,2015-07-31,E,31G15,59511,,1669,15,4,16.0,0.016,8,4,4,1
1670,16,1,true,6,3,3,1,3,311180,1053160,2015-07-31,E,31G15,59529,,1670,16,3,12.0,0.012,6,3,3,1
1671,17,1,true,4,2,2,0,2,311263,1053212,2015-07-31,E,31G15,59587,,1671,17,2,8.0,0.0080,4,2,2,0
1672,18,1,true,6,3,3,0,3,311288,1053226,2015-07-31,E,31G15,59600,,1672,18,3,12.0,0.012,6,3,3,0
1673,19,1,true,8,4,4,0,4,311209,1053179,2015-07-31,E,31G15,59549,,1673,19,4,16.0,0.016,8,4,4,0
1674,20,1,true,6,3,3,0,3,311294,1053232,2015-07-31,E,31G15,59607,,1674,20,3,12.0,0.012,6,3,3,0
1675,21,1,true,6,3,3,0,3,311363,1053276,2015-07-31,E,31G15,59652,,1675,21,3,12.0,0

After this step you can save the task output to a CSV file with a file write task (or do it also in the .NET code 😎 ).

Good luck!

Regards,
Erik
Uses Visualcron since 2006.
beechc
2015-09-22T19:13:54Z
Thank you Erik you have made my day! This is perfect.

Chris
Scroll to Top