C# Convert Json to CSV

Question

I have a json file in the following format:

{
"HDRDTL":["SRNO","STK_IDN","CERTIMG"],
"PKTDTL":[
{"SRNO":"2814","STK_IDN":"1001101259","CERTIMG":"6262941723"},
{"SRNO":"2815","STK_IDN":"1001101269","CERTIMG":"6262941726"},
{"SRNO":"2816","STK_IDN":"1001101279","CERTIMG":"6262941729"}
],
"IMGTTL":
["CERTIMG","ARRIMG"],
"IMGDTL":{"CERTIMG":"CRd6z2uq3gvx7kk","ARRIMG":"ASd6z2uq3gvx7kk"}
}

The "PKTDTL" array is the part I need to convert to csv. How can I achieve this in C# ?

Note, as an example, I only have 3 items in the "PKTDTL", the real json file has thousands of lines of ({"SRNO" ...}) and is huge.

I have written following code which does not work. see error message commented in the code.

Question

• Anyone knows why it failed ?

• Do you have a better/alternative method

  public static void ConvertJsonToCSV(string InFile)
  {
  
  string OutFile=InFile.Replace("json","csv");
  
  StreamReader sInFile = new StreamReader(InFile);
  StreamWriter sOutFile = new StreamWriter(OutFile);
  
  using (sInFile)
  {
      string sJson = sInFile.ReadToEnd();
  
      //*************************************************************
      // the below line failed, error message: unexpected json token
      // when reading datatable, expected startArray, got StartObject.
      //**************************************************************
  
      DataTable dt = JsonConvert.DeserializeObject<DataTable>(sJson);
      //DataTable dt = JsonConvert.DeserializeAnonymousType(sJson, new { Makes = default(DataTable) }).Makes;
  
      var sb = new StringBuilder();
  
      string[] columnNames = (from dc in dt.Columns.Cast<DataColumn>()
                              select dc.ColumnName).ToArray();
  
      sb.AppendLine(string.Join(",", columnNames));
  
      foreach (DataRow dr in dt.Rows)
      {
          foreach (object field in dr.ItemArray)
          {
              sb.Append(field.ToString().Replace(",", "") + ",");
          }
          sb.Replace(",", "\n", sb.Length - 1, 1);
      }
  
      sOutFile.Write(sb.ToString());
  
      sOutFile.Close();
      sInFile.Close();
  }
  

  }

Answer 1

With Cinchoo ETL - an open source library, you can do do the conversion easily with few lines of code

string json = @"{
    ""HDRDTL"":[""SRNO"",""STK_IDN"",""CERTIMG""],
    ""PKTDTL"":[
    {""SRNO"":""2814"",""STK_IDN"":""1001101259"",""CERTIMG"":""6262941723""},
    {""SRNO"":""2815"",""STK_IDN"":""1001101269"",""CERTIMG"":""6262941726""},
    {""SRNO"":""2816"",""STK_IDN"":""1001101279"",""CERTIMG"":""6262941729""}
    ],
    ""IMGTTL"":
    [""CERTIMG"",""ARRIMG""],
    ""IMGDTL"":{""CERTIMG"":""CRd6z2uq3gvx7kk"",""ARRIMG"":""ASd6z2uq3gvx7kk""}
    }";

StringBuilder sb = new StringBuilder();
using (var p = ChoJSONReader.LoadText(json).WithJSONPath("$..PKTDTL")
    )
{
    using (var w = new ChoCSVWriter(sb)
        .WithFirstLineHeader()
        )
        w.Write(p);
}

Console.WriteLine(sb.ToString());

Output:

SRNO,STK_IDN,CERTIMG
2814,1001101259,6262941723
2815,1001101269,6262941726
2816,1001101279,6262941729

Checkout CodeProject article for some additional help.

Disclaimer: I'm the author of this library.