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();
}

}

a big clue to whether you have asked a question or not is if you use a ? . This looks like you just need to use the debugger. — Keith Nicholas, May 14 '18 at 04:42
Keith: I know where the bug is. it is at the line "DataTable dt = JsonConvert.DeserializeObject(sJson);" it says: unexpected Json token when reading DataTable. Expected StartArray, got StartObject... — ppau2004, May 14 '18 at 05:09
I guess the line `"IMGDTL":{"CERTIMG":"CRd6z2uq3gvx7kk","ARRIMG":"ASd6z2uq3gvx7kk"}` is the problem. It doesn't contain an array but an object. You need to fix the JSON, or the data source respectively. — Markus Deibel, May 14 '18 at 05:30
Are you sure the JSON is correct? Because here it says it's incorrect: https://jsonformatter.curiousconcept.com/ — Federico Navarrete, May 14 '18 at 05:37
Federico is right, there are 2 commas missing after the first two `}`, although it might be just a copy&paste error here. — Andrew, May 14 '18 at 05:39
Hi Markus, according to https://www.w3schools.com/js/js_json_objects.asp. Json can have object as well as arrays. Besides, the file is download from a vendor website. It is beyond my control to fix the source itself. — ppau2004, May 14 '18 at 05:47
Andrew & Federico, you are correct. I accidentally missed out the comma during copy & paste. I have put them back, Now they pass through jsonformatter.curiousconcept.com as valid json format. But when running the code, still giving me same error. — ppau2004, May 14 '18 at 06:04
thats because your json isn't suited to be converted into a data table — Sujit.Warrier, May 14 '18 at 06:13
I know that JSON can contain objects (that's what it is for after all), the problem ist that the serializer is pretty stupid. I only can do the most simple transformation that fits the data type it is given. It assumes that for a `DeserializeObject` the JSON that is passed only contains arrays. In this case an object is invalid content - not for the JSON but for the Deserializer. — Markus Deibel, May 14 '18 at 13:25
You can try this, i found this as one of the best approach - https://stackoverflow.com/a/58035299/5714042 — Varun, Mar 17 '21 at 17:43

score 4 · Accepted Answer · answered May 14 '18 at 15:22

4

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.

answered May 14 '18 at 15:22

Cinchoo

6,088
2
19
34

The library worked great. Question: what does the $ followed by 2 dots mean in: "$..PKTDTL". also do you have documentation on the syntax of using your methods? – ppau2004 May 15 '18 at 07:34
It is JSON path. Refer this link for more info http://goessner.net/articles/JsonPath/ – Cinchoo May 15 '18 at 10:44
if the values contains special character, e.g. DEP%, the output replaced them into underscore, becomes DEP_. Is there anyway to fix it? – ppau2004 Jun 04 '18 at 06:50
I’m not able to reproduce it. What version of the lib are you using in your project? Pls try with latest and let me know. – Cinchoo Jun 04 '18 at 11:50

Sujit.Warrier · Answer 2 · 2018-05-16T03:35:00.367

I found myself in similar situation. this is what i did. first I created a JObject (using Newtonsoft.Json.Linq) let us consider that your json is in a string variable called "strJSON".

 JObject jsonObj= JObject.parse(strJSON);
 JObject  PKTDL=jsonObj["PKTDTL"] as JObject;
 IList<string> keys = PKTDL.Properties().Select(p => p.Name).ToList(); // this gives column names
 StringBuilder sb=new StringBuilder();
 string headers="";
 foreach(string key in keys)
 {
   headers+=","+key;
 }  
 sb.AppendLine(headers.TrimStart(','));
 foreach(JObject j in jsonObj["PKTDTL"]) //if jobject doesnt work try "JToken j"
 {
    string values="";
    foreach(string key in keys)
    {
       values+=","+jsonObj["PKTDTL"][key];
    }
    sb.AppendLine(values.TrimStart(','));
 }

   File.WriteAllText(filePath, sb.ToString());

then save sb as a csv

Error on line 2, 'JToken' does not contain a definition for 'Properties' and the best extension method overload 'Extensions.Properties(IEnumerable)' requires a receiver of type. Any idea how to solve this ? — ppau2004, May 15 '18 at 07:25
edited. casted jsonObj["PKTDTL"] to jobject before using proerties — Sujit.Warrier, May 16 '18 at 03:35

score 0 · Answer 3 · answered Sep 20 '19 at 22:08

Similar question to duplicate (see the JsonToCsv(string, string) method there). If you are already using NewtonSoft Json.NET and CsvHelper you can do similar conversion with the help of ExpandoObject.

    var jsonText = @"{
        ""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""}
        }";

    var jtoken = JObject.Parse(jsonText).SelectToken("$..PKTDTL");
    var expandos = jtoken.ToObject<ExpandoObject[]>();
    string csvText;

    using (var writer = new StringWriter())
    {
        using (var csv = new CsvWriter(writer))
        {
            csv.WriteRecords(expandos as IEnumerable<dynamic>);
        }

        csvText = writer.ToString();
    }

    Console.WriteLine(csvText);

C# Convert Json to CSV

3 Answers3