Dynamic Xml To Csv

[Bertrand Bloc'h]

Hi ! 

i'm trying to convert a Xml file To cvs but my xml file is a little bit special :

  <Tag name="APA_PIRCA811AAL" path="" type="OPC">

    <Property name="Datatype">6</Property>

    <Property name="Value">1</Property>

    <Property name="OPCServer">Ignition OPC-UA Server</Property>

    <Property name="OPCItemPath">[DEPOUSSIERAGE]DB40,X35.3</Property>

    <Property name="Scalemode">0</Property>

    <Property name="RawLow">0.000000</Property>

    <Property name="RawHigh">1.000000</Property>

    <Property name="ScaledLow">0.000000</Property>

    <Property name="ScaledHigh">1.000000</Property>

    <Property name="FormatString"></Property>

    <Property name="ToolTip">DEFAUT PRESSION SORTIE FOUR</Property>

    <Property name="EngUnit"></Property>

    <Property name="HistoryEnabled">0</Property>

    <Property name="PrimaryHistoryProvider">CIFC</Property>

    <Alarms>

      <Alarm name="DEFAUT PRESSION SORTIE FOUR">

        <Property name="priority">1</Property>

        <Property name="setpointA">1.000000</Property>

        <Property name="displayPath">HISTO_CIFC</Property>

      </Alarm>

      <Alarm name="DEFAUT PRESSION SORTIE FOUR">

        <Property name="priority">1</Property>

        <Property name="setpointA">1.000000</Property>

        <Property name="displayPath">HISTO_CIFC</Property>

      </Alarm>

    </Alarms>

  </Tag>

And i want i in a CSV file with columns like this :

TAG name;Tag Type;Value;DataType;OPCServer;OPCItemPath;ScaleMode;RawLow;RawHigh;ScaledLow;ScaledHigh;FormatString;Tooltip;EngUnit;HistoryEnabled;PrimaryHistoryProvider;Alarm name;priority;setpointA;displayPath;Alarm name;priority;setpointA;displayPath;Alarm name;priority;setpointA;displayPath;Alarm name;priority;setpointA;displayPath

My problem is i can't use multiple reader.Columns.Add because i've got a an error message which said "there is many times property".. Do you have an idea?

best regards

[shriop]

You'll have to set the Alarm node as your loop node, then use ../ based parent references to get the Tag Property values, which then requires you to use the InMemory option in the constructor.

Bruce Dunwiddie

[Bertrand Bloc'h]

Hi Bruce, thx a lot for your answer,but i wasn't enough precise with my request. 

So i will try a second time in 5min but more clearly i hope ^^

 

[Bertrand Bloc'h]

So, with this XML for an exemple, which is dynamic because for one tag u can maybe have an alarm or not, and not the same numbers off property, but all columns are define by the class.

Xml :

<?xml version="1.0" encoding="utf-8" ?> 

<Tags>

<Tag name="SO2EXTLATOUR12" path="" type="OPC">

<Property name="Datatype">6</Property>

<Property name="Value">1</Property>

<Property name="OPCServer">Ignition OPC-UA Server</Property>

<Property name="OPCItemPath">[DEPOUSSIERAGE]DB50,X35.2</Property>

</Tag>

<Tag>

<Property name="Datatype">6</Property>

<Property name="Value">1</Property>

<Property name="OPCServer">Ignition OPC-UA Server</Property>

<Alarms>

 <Alarm name="Defaut pression air instrument PAS803">

<Property name="priority">1</Property>

<Property name="setpointA">1.000000</Property>

<Property name="displayPath">HISTO_CIFC</Property>

 </Alarm>

</Alarms>

</Tag>

</Tags>

And my class :

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Xml;

using System.Xml.Linq;

using LINQtoCSV;

using DataStreams.Xml;

using DataStreams.Csv;

namespace ConvertisseurCsvToXml

{

    public class XmlToCsv

    {

        [STAThread]

        public static void ConvertirToXml()

        {

            using (CsvWriter writer = new CsvWriter(@"C:\CSV-TO-XML\test.csv"))

            using (XmlRecordReader reader = new XmlRecordReader(@"C:\CSV-TO-XML\test.xml", "Tags/Tag"))

            {

                reader.Columns.Add("Tags/Tag/@name", "TagName");

                reader.Columns.Add("Tags/Tag/@type", "TagType");

                reader.Columns.Add("property", "DataType");

                reader.Columns.Add("property", "Value");

                reader.Columns.Add("property", "OPCServer");

                reader.Columns.Add("property", "OPCItemPath");

                reader.Columns.Add("Tags/Tag/Alarms/Alarm/@name", "AlarmName");

                reader.Columns.Add("property", "priority");

                reader.Columns.Add("property", "setpointA");

                reader.Columns.Add("property", "displayPath");

                writer.Write("TAG NAME");

                writer.Write("TAG TYPE");

                writer.Write("DATATYPE");

                writer.Write("VALUE");

                writer.Write("OPCSERVER");

                writer.Write("OPCITEMPATH");

                writer.Write("ALARM NAME");

                writer.Write("PRIORITY");

                writer.Write("SETPOINTA");

                writer.Write("DISPLAYPATH");

                writer.EndRecord();

                while (reader.ReadRecord())

                {

                    writer.Write(reader["TagName"]);

                    writer.Write(reader["TagType"]);

                    writer.Write(reader["DataType"]);

                    writer.Write(reader["Value"]);

                    writer.Write(reader["OPCServer"]);

                    writer.Write(reader["OPCItemPath"]);

                    writer.Write(reader["AlarmName"]);

                    writer.Write(reader["priority"]);

                    writer.Write(reader["setpointA"]);

                    writer.Write(reader["displayPath"]);

                    writer.EndRecord();

                }

                reader.Close();

                writer.Close();

            }

        }

    }

}

But it doesn't work.. have you any idea? 

Best Regards,

[shriop]

What do you mean by it doesn't work? Is there an error message or is the output not what you expected?

I doubt that there's going to be an easy solution for what you're trying to do. Is there a finite number of properties, only some may not exist for certain tags, so you will have the same number of columns on each row in the CSV?

And if there is more than one alarm, you want to have more columns on that row, a set for each alarm?

About the only thing that might help you with the code is to use the Evaluate method on each record. You can give it any xpath and it will return a string. You can for instance give it an xpath like Count(Alarms/Alarm), to figure out how many Alarm instances are in that particular record, and then use array syntax to get values for each instance. For example you can use Alarms/Alarm[1]/@name to get the name attribute value of the first alarm, http://www.w3schools.com/xpath/xpath_syntax.asp .

I also don't think you should be referencing Tags/Tag at the beginning of xpath's like Tags/Tag/@name because you're already at Tags/Tag context so you should just use @name.

Bruce Dunwiddie

[Bertrand Bloc'h]

Hello Bruce, very thank's for your Answer! it works now! :D

But.. my xml file is very big, and when my program convert to cvs, after the 12 line, a lot of rows are completed by "DEMO" .. i thought that the purchase version was just for the redistributions right? i must to purchase in order to convert a lot of data?

Best Regards

[Bertrand Bloc'h]

And here is my class :

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Xml;

using System.Xml.Linq;

using LINQtoCSV;

using DataStreams.Xml;

using DataStreams.Csv;

namespace ConvertisseurCsvToXml

{

    public class XmlToCsv

    {

        [STAThread]

        public static void ConvertirToXml()

        {

                using (XmlRecordReader reader = new XmlRecordReader(@"C:\CSV-TO-XML\test.xml", "Tags/Tag"))

                using (CsvWriter writer = new CsvWriter(@"C:\CSV-TO-XML\test.csv", ';', Encoding.Default))

                {

                    reader.Columns.Add("@name", "TagName");

                    reader.Columns.Add("@type", "TagType");

                    reader.Columns.Add("Property[@name='Datatype']", "DataType");

                    reader.Columns.Add("Property[@name='Value']", "Value");

                    reader.Columns.Add("Property[@name='OPCServer']", "OPCServer");

                    reader.Columns.Add("Property[@name='OPCItemPath']", "OPCItemPath");

                    reader.Columns.Add("Property[@name='ScanClass']", "ScanClass");

                    reader.Columns.Add("Property[@name='ScaleMode']", "ScaleMode");

                    reader.Columns.Add("Property[@name='RawLow']", "RawLow");

                    reader.Columns.Add("Property[@name='RawHigh']", "RawHigh");

                    reader.Columns.Add("Property[@name='ScaledLow']", "ScaledLow");

                    reader.Columns.Add("Property[@name='ScaledHigh']", "ScaledHigh");

                    reader.Columns.Add("Property[@name='Deadband']", "Deadband");

                    reader.Columns.Add("Property[@name='EngUnit']", "EngUnit");

                    reader.Columns.Add("Property[@name='EngLow']", "EngLow");

                    reader.Columns.Add("Property[@name='EngHigh']", "EngHigh");

                    reader.Columns.Add("Property[@name='Tooltip']", "Tooltip");

                    reader.Columns.Add("Property[@name='HistoryEnabled']", "HistoryEnabled");

                    reader.Columns.Add("Property[@name='PrimaryHistoryProvider']", "PrimaryHistoryProvider");

                    reader.Columns.Add("Property[@name='HistoricalScanclass']", "HistoricalScanclass");

                    reader.Columns.Add("Property[@name='HistoricalDeadband']", "HistoricalDeadband");

                    reader.Columns.Add("Property[@name='HistoryMaxAgeMode']", "HistoryMaxAgeMode");

                    reader.Columns.Add("Property[@name='HistoryMaxAge']", "HistoryMaxAge");

                    reader.Columns.Add("Alarms/Alarm[1]/@name", "AlarmName1");

                    reader.Columns.Add("Alarms/Alarm[1]/Property[@name='mode']", "mode1");

                    reader.Columns.Add("Alarms/Alarm[1]/Property[@name='setpointA']", "setpointA1");

                    reader.Columns.Add("Alarms/Alarm[1]/Property[@name='ALM_Property']", "ALM_Property1");

                    reader.Columns.Add("Alarms/Alarm[1]/Property[@name='displayPath']", "displayPath1");

                    reader.Columns.Add("Alarms/Alarm[1]/Property[@name='notes']", "notes1");

                    reader.Columns.Add("Alarms/Alarm[1]/Property[@name='priority']", "priority1");

                    reader.Columns.Add("Alarms/Alarm[2]/@name", "AlarmName2");

                    reader.Columns.Add("Alarms/Alarm[2]/Property[@name='mode']", "mode2");

                    reader.Columns.Add("Alarms/Alarm[2]/Property[@name='setpointA']", "setpointA2");

                    reader.Columns.Add("Alarms/Alarm[2]/Property[@name='ALM_Property']", "ALM_Property2");

                    reader.Columns.Add("Alarms/Alarm[2]/Property[@name='displayPath']", "displayPath2");

                    reader.Columns.Add("Alarms/Alarm[2]/Property[@name='notes']", "notes2");

                    reader.Columns.Add("Alarms/Alarm[2]/Property[@name='priority']", "priority2");

                    reader.Columns.Add("Alarms/Alarm[3]/@name", "AlarmName3");

                    reader.Columns.Add("Alarms/Alarm[3]/Property[@name='mode']", "mode3");

                    reader.Columns.Add("Alarms/Alarm[3]/Property[@name='setpointA']", "setpointA3");

                    reader.Columns.Add("Alarms/Alarm[3]/Property[@name='ALM_Property']", "ALM_Property3");

                    reader.Columns.Add("Alarms/Alarm[3]/Property[@name='displayPath']", "displayPath3");

                    reader.Columns.Add("Alarms/Alarm[3]/Property[@name='notes']", "notes3");

                    reader.Columns.Add("Alarms/Alarm[3]/Property[@name='priority']", "priority3");

                    reader.Columns.Add("Alarms/Alarm[4]/@name", "AlarmName4");

                    reader.Columns.Add("Alarms/Alarm[4]/Property[@name='mode']", "mode4");

                    reader.Columns.Add("Alarms/Alarm[4]/Property[@name='setpointA']", "setpointA4");

                    reader.Columns.Add("Alarms/Alarm[4]/Property[@name='ALM_Property']", "ALM_Property4");

                    reader.Columns.Add("Alarms/Alarm[4]/Property[@name='displayPath']", "displayPath4");

                    reader.Columns.Add("Alarms/Alarm[4]/Property[@name='notes']", "notes4");

                    reader.Columns.Add("Alarms/Alarm[4]/Property[@name='priority']", "priority4");

                    writer.Write("TAG NAME");

                    writer.Write("TAG TYPE");

                    writer.Write("DATATYPE");

                    writer.Write("VALUE");

                    writer.Write("OPCSERVER");

                    writer.Write("OPCITEMPATH");

                    writer.Write("ScanClass");

                    writer.Write("ScaleMode");

                    writer.Write("RawLow");

                    writer.Write("RawHigh");

                    writer.Write("ScaledLow");

                    writer.Write("ScaledHigh");

                    writer.Write("Deadband");

                    writer.Write("EngUnit");

                    writer.Write("EngLow");

                    writer.Write("EngHigh");

                    writer.Write("Tooltip");

                    writer.Write("HistoryEnabled");

                    writer.Write("PrimaryHistoryProvider");

                    writer.Write("HistoricalScanclass");

                    writer.Write("HistoricalDeadband");

                    writer.Write("HistoryMaxAgeMode");

                    writer.Write("HistoryMaxAge");

                    writer.Write("Alarm1");

                    writer.Write("Mode");

                    writer.Write("setpointA");

                    writer.Write("ALM_Property");

                    writer.Write("DisplayPath");

                    writer.Write("Notes");

                    writer.Write("Priority");

                    writer.Write("Alarm2");

                    writer.Write("Mode");

                    writer.Write("setpointA");

                    writer.Write("ALM_Property");

                    writer.Write("DisplayPath");

                    writer.Write("Notes");

                    writer.Write("Priority");

                    writer.Write("Alarm3");

                    writer.Write("Mode");

                    writer.Write("setpointA");

                    writer.Write("ALM_Property");

                    writer.Write("DisplayPath");

                    writer.Write("Notes");

                    writer.Write("Priority");

                    writer.Write("Alarm4");

                    writer.Write("Mode");

                    writer.Write("setpointA");

                    writer.Write("ALM_Property");

                    writer.Write("DisplayPath");

                    writer.Write("Notes");

                    writer.Write("Priority");

                    writer.EndRecord();

                    while (reader.ReadRecord())

                    {

                        writer.Write(reader["TagName"]);

                        writer.Write(reader["TagType"]);

                        writer.Write(reader["DataType"]);

                        writer.Write(reader["Value"]);

                        writer.Write(reader["OPCServer"]);

                        writer.Write(reader["OPCItemPath"]);

                        writer.Write(reader["ScanClass"]);

                        writer.Write(reader["ScaleMode"]);

                        writer.Write(reader["RawLow"]);

                        writer.Write(reader["RawHigh"]);

                        writer.Write(reader["ScaledLow"]);

                        writer.Write(reader["ScaledHigh"]);

                        writer.Write(reader["Deadband"]);

                        writer.Write(reader["EngUnit"]);

                        writer.Write(reader["EngLow"]);

                        writer.Write(reader["EngHigh"]);

                        writer.Write(reader["Tooltip"]);

                        writer.Write(reader["HistoryEnabled"]);

                        writer.Write(reader["PrimaryHistoryProvider"]);

                        writer.Write(reader["HistoricalScanclass"]);

                        writer.Write(reader["HistoricalDeadband"]);

                        writer.Write(reader["HistoryMaxAgeMode"]);

                        writer.Write(reader["HistoryMaxAge"]);

                        writer.Write(reader["AlarmName1"]);

                        writer.Write(reader["mode1"]);

                        writer.Write(reader["setpointA1"]);

                        writer.Write(reader["ALM_Property1"]);

                        writer.Write(reader["displayPath1"]);

                        writer.Write(reader["notes1"]);

                        writer.Write(reader["priority1"]);

                        writer.Write(reader["AlarmName2"]);

                        writer.Write(reader["mode2"]);

                        writer.Write(reader["setpointA2"]);

                        writer.Write(reader["ALM_Property2"]);

                        writer.Write(reader["displayPath2"]);

                        writer.Write(reader["notes2"]);

                        writer.Write(reader["priority2"]);

                        writer.Write(reader["AlarmName3"]);

                        writer.Write(reader["mode3"]);

                        writer.Write(reader["setpointA3"]);

                        writer.Write(reader["ALM_Property3"]);

                        writer.Write(reader["displayPath3"]);

                        writer.Write(reader["notes3"]);

                        writer.Write(reader["priority3"]);

                        writer.Write(reader["AlarmName4"]);

                        writer.Write(reader["mode4"]);

                        writer.Write(reader["setpointA4"]);

                        writer.Write(reader["ALM_Property4"]);

                        writer.Write(reader["displayPath4"]);

                        writer.Write(reader["notes4"]);

                        writer.Write(reader["priority4"]);

                        writer.EndRecord();

                    }

                    reader.Close();

                    writer.Close();

               

            }

        }

    }

}

[shriop]

Yes, you must purchase to read more than ten lines. The download from the site is only for demonstration purposes. There are three different prices, the two more expensive of which also include redistribution rights.

Bruce Dunwiddie