CSV to XML script not showing data

  1. Incntr0l's Avatar
    Posts : 55
    Windows 10
       #1

    CSV to XML script not showing data


    Hello everyone,

    Firstly, I don't know if this thread belongs in Software and Apps, if it doesn't feel free to move it to the correct sub-forum so I know for later where to put problems like this.

    To the problem now... I have been trying to export a huge CSV file via Powershell Scripting into a XML file using Export-CliXML, scripting and pipelining and for some reason after exporting approximately twenty thousand lines of a single document (which is much, MUCH bigger) some of the data has gone missing. At first I thought it was because of duplicates, but I am not so sure anymore because each line of data has a separate ID to it.

    An example of this is where one data line's ID (this is also from a false-file so to speak, a file that I made up to see what would happen if it was the duplicates combining) was "X9775KU2", the second was "K86HU17S" and the third was as shown in the first. Each one was separate and all were put in a XML file after exporting it, and was how I found out that it wasn't a duplicate problem.

    The twenty thousand lines of data that I had taken out of that huge document as a baseline set of data but each time I export as an XML from that twenty thousand between three and five lines of data (different each export) disappear from it. I have searched around the internet to see if some other sites had a way to solve this problem, but all of them were just about setting up a script to import a CSV then export it as XML.

    I am not sure where to go from here for the script. I should also say that my knowledge of Powershell scripts is quite poor as I am only just getting into it so I may have missed something obvious to most. If anyone has a way to solve this, please let me know.

    Thanks, Incntr0l.
    Last edited by Incntr0l; 25 Nov 2018 at 17:28. Reason: Miss-spelled title
      My Computer

  2. bro67's Avatar
    Posts : 8,353
    Mac OS Catalina
       #2

    The first question is why are you taking the file from .csv to .xml. You can use Excel to convert .csv to .xml. I will let you know that there is always going to be issues with a .csv converted to .xml, with the .xml elements being incorrect to what the value "title" or "category" is supposed to be, so that if you are using say c#, will understand what the values will be for what category that you are going to be searching. Also see msdn.microsoft.com/en-us/library/dd460688(v=vs.110).aspx You may want to use https://www.nuget.org/packages/ChoETL/ From the author of ChoETL:

    "With Cinchoo ETL - an open source framework, you can read and write CSV/Xml large files quickly with few lines of code as below

    using
    (var csv =newChoCSVReader("NodeData.csv").WithFirstLineHeader(true)
    .WithFields("ID","NODE","PROCESS_STATE","PREV_TIME_STAMP"))
    {
    using (var xml =newChoXmlWriter("NodeData.xml").WithXPath("data-set/PDA_DATA"))
    xml
    .Write(csv);
    }

    The output xml look like

    <data-set>
    <PDA_DATA>
    <ID>206609474</ID>
    <NODE>2175</NODE>
    <PROCESS_STATE>47</PROCESS_STATE>
    <PREV_TIME_STAMP>31.03.2015 00:01:25</PREV_TIME_STAMP>
    </PDA_DATA>
    <PDA_DATA>
    <ID>206609475</ID>
    <NODE>2175</NODE>
    <PROCESS_STATE>47</PROCESS_STATE>
    <PREV_TIME_STAMP>31.03.2015 00:02:25</PREV_TIME_STAMP>
    </PDA_DATA>
    <PDA_DATA>
    <ID>206609476</ID>
    <NODE>2175</NODE>
    <PROCESS_STATE>47</PROCESS_STATE>
    <PREV_TIME_STAMP>31.03.2015 00:03:25</PREV_TIME_STAMP>
    </PDA_DATA>
    </data-set>
      My Computers

  3. Incntr0l's Avatar
    Posts : 55
    Windows 10
    Thread Starter
       #3

    Thanks for the quick response. I am using Powershell for two reasons:
    1. The script is set to be a proof-of-concept that I will be using later.
    2. I did not know that Excel allowed me to save sheets as a .xml format, when I tried to save the file I'm using as a .xml file it came up with a error.

    I have tried out Cinchoo and it has worked real well, the data that I am missing has been put in the .xml sheet I had been using and it has worked on other CSV fives I have. Thank you for recommending it, I will be using it a lot in the future now.
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 13:44.
Find Us




Windows 10 Forums