StreamWriter Exportation to csv with DATATABLE

[PIPSSOU]

Good morning !

I'm a beginner in Csharp.

I would like to write data from a datatable to a Csv file.

My problem is that I also want to transform certain data which is originally in columns into row headers.

Exemple:
Nom   Prenoms     Rubrique Paie        Montant
DOE    John            Salairebase           1000000
DOE    John            Transport               20000

Expected result
Nom   Prenoms    salairebase    Transport
DOE   John            1000000        20000

Obtained result
Nom  Prenoms      salairebase    Transport
DOE   John             20000             1000000

My Code

if (str3 == "RUBRIQUES PAIE") {
   // Ecriture des entetes Agent
   for (int index = 0; index < count2; ++index) {
      streamWriter.Write(data.Columns[index].Caption + ";");
   }
   // Ecriture des entetes Rubriques

   for (int index = 0; index < rubCount; ++index) {
      if (index == rubCount - 1)
         streamWriter.WriteLine(stringList[index]);
      else
         streamWriter.Write(stringList[index] + ";");
   }

   int indexRub = count2 - 1;
   string lastAgent = "000";
   int controle = 0;
   // Parcours des Agents par Matricule
   foreach(DataRow row in (InternalDataCollectionBase) data.Rows) {
      string headAgent = "";
      for (int index = 0; index < count2; ++index) {
         string str0 = data.Columns[index].DataType.Name == "DateTime" ? row[index].ToString().Substring(0, 10) : row[index].ToString();
         headAgent += str0 + ";";
      }
      // Infos Agent
      if (headAgent != lastAgent) {
         if (lastAgent != "000") {
            streamWriter.WriteLine("");
            controle = 0;
         }

         streamWriter.Write(headAgent);
      }
      lastAgent = headAgent;

      controle++;

      // Infos Rubriques
      streamWriter.Write(row[indexMt].ToString() + (indexRub == controle ? "" : ","));

   }
   streamWriter.Flush();
   streamWriter.Dispose();

 

[Skydiver]

Can you go into a little more detail about how you determine which row values to transform into column headers, and which row values become the row values below those new column headers? What happens if there are more rows with matching first name/last name than there are column headers and values? What happens if there are duplicate values meant to become column headers, but their corresponding values are different?

 

[cjard]

Life is so complex when we toss OO notions out of the window

Let's have a class for holding information:

public class Person{
  public string Nom {get;set;}
  public string Prenoms {get;set;}
  public string SalaireBase {get;set;}
  public string Transport {get;set;}
}

Let's have an indexer so that we can retrieve a person if we already created them. We'll retrieve them by name as that appears to be the same on each row:

var index = new Dictionary<string, Person>();

Now let's loop the datatable retrieving or making new as we go:

foreach(DataRow ro in ddataTable.Rows){

  var nom = ro["Nom"].ToString();
  var prenoms = ro["Prenoms"].ToString();
  var indexKey = nom+"\t"+prenoms;

  //make sure the index contains a person with that name - if they dont exist, create them
  if(!index.ContainsKey(indexKey)) index[indexKey] = new Person(){ Nom = nom, Prenoms = prenoms };

  //get the person by that name
  var p = index[indexKey];

  //supplement the data on them
  var rubP = ro["Rubrique Paie"].ToString();
  if(rubP == "salairebase") p.SalaireBase = ro["Montant"].ToString();
  else if(rubP == "Transport") p.Transport= ro["Montant"].ToString();

}

Now you have an index full of Persons, you can just write them to a CSV. Install the CSVHelper Nuget library:

using (var writer = new StreamWriter("path\\to\\file.csv"))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
    csv.WriteRecords(index.Values);
}

I'm sure, as you process the file, you'll find things you have to work around, like upper/lowercase names being the same person, repeated lines and you want the first not the last, etc.. Exercise for the reader