Read data from csv file and create xml files.

Question

Hi

I Want to read data from csv file and want to store the data into different nodes of an xml file.

For ex: My Input CSV File consists below data.

pavan123,123456789011,X900116-001

pavan124,123456789012,X900116-022

The above values should reflect in the below nodes of an xml file. And should create that many files which means there are two rows in the csv it should create two xml files. Also if I place 5 rows it should create 5 xml files.

pavan123

<ns0:BODID>b4219458-25ac-4558-9c81-917a8b5c2c04</ns0:BODID>

123456789011

<ns0:SerialNumber schemeName="Original">000001111111</ns0:SerialNumber>

X900116-001

<ns0:Item>
            <ns0:CustomerItemID agencyRole="Product Material Number">
              <ns0:ID>X900116-001</ns0:ID>

Sample XML File

<?xml version="1.0" encoding="utf-16"?>
<ns0:ShowWIPStatus systemEnvironmentCode="Prod" xmlns:ns0="http://www.openapplications.org/oagis/9">
  <ns0:ApplicationArea>
    <ns0:Sender>
      <ns0:TaskID>CRP</ns0:TaskID>
      <ns0:ConfirmationCode>Always</ns0:ConfirmationCode>
    </ns0:Sender>
    <ns0:CreationDateTime>2015-10-30T11:52:03Z</ns0:CreationDateTime>
    <ns0:BODID>b4219458-25ac-4558-9c81-917a8b5c2c04</ns0:BODID>
  </ns0:ApplicationArea>
  <ns0:DataArea>
    <ns0:Show>
      <ns0:OriginalApplicationArea>
        <ns0:CreationDateTime>2015-10-30T11:52:03Z</ns0:CreationDateTime>
        <ns0:BODID>b4219458-25ac-4558-9c81-917a8b5c2c04</ns0:BODID>
      </ns0:OriginalApplicationArea>
    </ns0:Show>
    <ns0:WIPStatus>
      <ns0:WIPStatusHeader>
        <ns0:DocumentID agencyRole="Reference Number">
          <ns0:ID>1309876000</ns0:ID>
        </ns0:DocumentID>
        <ns0:DocumentDateTime>2015-10-30T11:52:22Z</ns0:DocumentDateTime>
      </ns0:WIPStatusHeader>
      <ns0:Operation>
        <ns0:DocumentReference>
          <ns0:OperationReference>
            <ns0:Status>
              <ns0:Code name="Repair Type">CSF</ns0:Code>
            </ns0:Status>
          </ns0:OperationReference>
          <ns0:Item>
            <ns0:CustomerItemID agencyRole="Product Material Number">
              <ns0:ID>X900116-001</ns0:ID>
            </ns0:CustomerItemID>
            <ns0:SerialNumber schemeName="Original">000001111111</ns0:SerialNumber>
          </ns0:Item>
        </ns0:DocumentReference>
      </ns0:Operation>
    </ns0:WIPStatus>
  </ns0:DataArea>
</ns0:ShowWIPStatus>

Thanks

Answer 1

There are lots of articles on Linq-2-XML

http://www.codeproject.com/Articles/32665/Make-it-easy-Convert-CSV-files-to-XML-with-LINQ

Answer 2

Your XML and CSV files don't line up at all.  In your first example you're automagically associating the first row with some arbitrary GUID.  The second set of XML seems completely made up.  You cannot automate this.  It sort of sounds like you wanted to create a separate XML file for each row in your CSV but you have left out all the details that make this possible.  Please provide an exact example of a CSV line and how it should be mapped to the corresponding XML.  Any magic values you make up in the XML have to be explained as to how you got them given just the CSV value.

One you do that you'll actually have the algorithm in place to implement it yourself but we can still help with the parts you don't understand.

Michael Taylor
http://blogs.msmvps.com/p3net

Answer 3

What i want is to pass the values in CSV to the sample xml.

If possible please pass these values from a notepad to SampleXML file.

pavan123,123456789011,X900116-001

pavan124,123456789012,X900116-022

For example pavan123 should replace the value in below xpath in SampleXML file

//ns0:ShowWIPStatus//ns0:ApplicationArea//ns0:BODID

and 123456789011 should replace below xpath 

//ns0:ShowWIPStatus//ns0:DataArea//ns0:WIPStatus//ns0:Operation//ns0:DocumentReference//ns0:Item//ns0:SerialNumber

Finally it should create a new XML file with the above changes 

Answer 4

So you want to replace particular XML element values with the given values from the CSV file.  It is still unclear where the remaining values are coming from. Based upon your description you are creating new XML files, not updating an existing XML file.  So where do the remaining values come from?

I'm going to assume you already have some static XML file with the structure you gave.  To replace the elements in it with the values from CSV file you really just need to manipulate the XML contents using XDocument or equivalent.  Whether you copy the file for each row or generate all the data in a single file doesn't really matter too much for this.  It simply impacts when you open/create the file.

Reading the data from CSV is also not really relevant for updating the XML. It depends upon whether you're trying to use simple file IO, one of the many CSV reader implementations or even ADO.NET. The only assumption I'll make is that you have loaded the CSV data into a list of strongly typed objects to make it easier to work with.

class Program
{
    static void Main ( string[] args )
    {
        var data = LoadCsvData();
            
        //TODO: For demo purposes create a new file for
        //each row in the CSV, inefficient but clean
        int counter = 0;
        foreach (var row in data)
            TransformXml(row, "sample.xml", String.Format("sample-{0}.xml", ++counter));
    }

    //TODO: Replace this with the code that loads the CSV and stores the data into an enumerable list
    static IEnumerable<CsvData> LoadCsvData()
    {
        //Load the CSV data using whatever technology you want
        return new[] {
            new CsvData() { BodId = "pavan123", SerialNumber = "123456789011", ItemId = "X900116-001" },
            new CsvData() { BodId = "pavan124", SerialNumber = "123456789012", ItemId = "X900116-022" },
        };            
    }

    static void TransformXml ( CsvData data, string sourceFile, string destinationFile )
    {
        //Inefficient to keep reloading the doc but it is clean
        var doc = XDocument.Load(sourceFile);
        var ns = new XmlNamespaceManager(new NameTable());
        ns.AddNamespace("ns0", "http://www.openapplications.org/oagis/9");

        //TODO: Example of how to replace a value using XPath, add more replacements for other values
        var bodId = doc.XPathSelectElement("//ns0:ShowWIPStatus/ns0:ApplicationArea/ns0:BODID", ns);
        if (bodId != null)
            bodId.Value = data.BodId;

        doc.Save(destinationFile);            
    }
}

//Sample type for normalizing CSV data
class CsvData
{
    public string BodId { get; set; }
    public string SerialNumber { get; set; }
    public string ItemId { get; set; }
}

Answer 5

According to what i understand, you want to convert data from CSV to XML. At first, you can convert the data from CSV to Data table. Secondly, you can convert from Data table to the XML format . Below is the code which converts data from CSV to data table.

   #region GET DATA TABLE FROM CSV FILE
       public static DataTable GetDataTabletFromCSVFile(string ZipFileDirPath)
       {
           string csv_file_path = ZipFileDirPath ;

           DataTable csvData = new DataTable();
           try
           {
               using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
               {
                   csvReader.SetDelimiters(new string[] { "," });
                   csvReader.HasFieldsEnclosedInQuotes = true;
                   //read column names
                   string[] colFields = csvReader.ReadFields();
                   foreach (string column in colFields)
                   {
                       DataColumn datecolumn = new DataColumn(column);
                       datecolumn.AllowDBNull = true;
                       csvData.Columns.Add(datecolumn);
                   }
                   while (!csvReader.EndOfData)
                   {
                       string[] fieldData = csvReader.ReadFields();
                       //Making empty value as null
                       for (int i = 0; i < fieldData.Length; i++)
                       {
                           if (fieldData[i] == "")
                           {
                               fieldData[i] = null;
                           }
                       }
                       csvData.Rows.Add(fieldData);
                   }
               }
           }
           catch (Exception ex)
           {
           }
           return csvData;
       }
       #endregion

---

Bhupinder Singh