I need to convert a CSV document to another one in XML format. This is what I have in my CSV file:

level;1;2;49;50;51; Position;8455;8930;9405;9880;10355;11015;11490;11965;12440;12915;13575;14050;14525;15000

So, for each level, I should have the same positions described in the field Position The output format should be like that:

And so on..until I fill all values from the Position table How can this be achived? Any example? Thanks.

CodePudding user response：

Your posted xml is not completely clear so I do not know exactly what is needed. The code below should get you started using Xml linq. I used StringReader but if you are reading from a file change to StreamReader.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.IO;

namespace ConsoleApplication40
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {

            string ident = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Root></Root>";
            XDocument doc = XDocument.Parse(ident);
            XElement root = doc.Root;

            string csvFile = "level;1;2;49;50;51;\n"  
                             "Position;8455;8930;9405;9880;10355;11015;11490;11965;12440;12915;13575;14050;14525;15000\n";

            int[] levels = null;
            int[] positions = null;

            StringReader reader = new StringReader(csvFile);
            string line = "";
            while((line = reader.ReadLine()) != null)
            {
                line = line.Trim();
                if (line.Length > 0)
                {
                    string[] splitArray = line.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    switch (splitArray[0])
                    {
                        case "level" :
                            levels = splitArray.Skip(1).Select(x => int.Parse(x)).ToArray();
                            break;
                        case "Position":
                            positions = splitArray.Skip(1).Select(x => int.Parse(x)).ToArray();
                            break;
                    }
                }
            }

            int aisleID = 111;
            foreach (int level in levels)
            {
                XElement TeachTable = new XElement("TeachTable", new object[] {
                        new XAttribute("AisleID", aisleID),
                        new XAttribute("LevelID", level)
                    });
                root.Add(TeachTable);
                for (int i = 0; i < positions.Length; i  )
                {
                    XElement pos = new XElement("pos", new object[]{
                            new XAttribute("idx", i),
                            new XAttribute("unit", "mm"),
                            positions[i]
                        });
                    TeachTable.Add(pos);

                }

            }
            doc.Save(FILENAME);
        }
    }
 
}

CodePudding user response：

I'm unable to see the structure of your xml. From your description I think you want this:

<Level_1>
  <Position_1>8455</Position_1>
  <Position_2>8930</Position_2>
  <Position_3>9405</Position_3>
  <Position_4>9880</Position_4>
      etc
      etc
      etc
</Level_1>
<Level_2>
  <Position_1>8455</Position_1>
  <Position_2>8930</Position_2>
  <Position_3>9405</Position_3>
  <Position_4>9880</Position_4>
      etc
      etc
      etc
</Level_2>
<Level_49>
  <Position_1>8455</Position_1>
  <Position_2>8930</Position_2>
  <Position_3>9405</Position_3>
  <Position_4>9880</Position_4>
      etc
      etc
      etc
</Level_49>
<Level_50>
  <Position_1>8455</Position_1>
  <Position_2>8930</Position_2>
  <Position_3>9405</Position_3>
  <Position_4>9880</Position_4>
      etc
      etc
      etc
</Level_50>
etc
etc
etc

You can create a subroutine that opens a file, reads each line by line. You can use the "split" function to put all 'levels' into an array and likewise for all 'Positions.' Then create the string code to put the "<" brackets and names drawn from the arrays. Then add a vbNewLine at the end of each line

Below is a sub I use to turn raw JavaScript into a VBA string that can be placed into the clipboard with the click of a button. It puts the raw JavaScript into the clipboard so I can paste it into a browser developer console. It's not what you need but you can see how it can be used to create what you need. Actually the clipboard code is a separate function. The following code puts all the javascript into a VBA readable string "myString" and save it in a separate file

Sub readWrite2()
Dim fileNum As Integer, dataLine As String, fileNum2 As Integer, fileName As String, myString As String



fileName = "C:/newText.txt"

fileNum = FreeFile()
Open fileName For Input As #fileNum

While Not EOF(fileNum)
    Line Input #fileNum, dataLine ' read in data 1 line at a time
    If dataLine <> "" Then
        dataLine = Replace(dataLine, """", """" & " & chr(34) & " & """", 1)
        dataLine = Replace(dataLine, "ÿþ", "", 1)
        myString = myString & "myString = myString & " & Chr(34) & dataLine & Chr(34) & " & vbNewLine" & vbNewLine
    End If
Wend

Close fileNum
    
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim Fileout As Object
Set Fileout = FSO.CreateTextFile("C:/newText2.txt", True, True)
Fileout.Write myString
Fileout.Close
Set Fileout = Nothing
'Debug.Print myString

End Sub