65.9K
CodeProject is changing. Read more.
Home

Create CSV from JSON in C#

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.84/5 (15 votes)

Mar 22, 2013

CPOL
viewsIcon

145727

Using Extension Method and JSON Serializer

Introduction

I faced a problem of creating CSV from a JSON object in ASP.NET. Here I am showing how to convert JSON to CSV with XML and DataSet.

Using the Code

I am using an Extension method of DataTable to create CSV, XmlNodeReader to create XML from an XML node, JSON.DeserializeXmlNode. Let's start work:

var json={
"employees": [
{ "firstName":"John" , "lastName":"Doe" }, 
{ "firstName":"Anna" , "lastName":"Smith" }, 
{ "firstName":"Peter" , "lastName":"Jones" }
]
}

I need it deserialized first:

XmlNode xml = JSON.DeserializeXmlNode("{records:{record:" + json + "}}"); 
XmlDocument xmldoc = new XmlDocument();
//Create XmlDoc Object
xmldoc.LoadXml(xml.InnerXml);
//Create XML Steam 
var xmlReader = new XmlNodeReader(xmldoc);
DataSet dataSet = new DataSet();
//Load Dataset with Xml
dataSet.ReadXml(xmlReader);
//return single table inside of dataset
var csv = dataSet.Tables[0].ToCSV(",");

You have found that here is the extension method .ToCSV. Let's see how to create this.

public static string ToCSV(this DataTable table,string delimator)
{
    var result = new StringBuilder();
    for (int i = 0; i < table.Columns.Count; i++)
    {
        result.Append(table.Columns[i].ColumnName);
        result.Append(i == table.Columns.Count - 1 ? "\n" : delimator);
    }
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            result.Append(row[i].ToString());
            result.Append(i == table.Columns.Count - 1 ? "\n" : delimator);
        }
    }
    return result.ToString().TrimEnd(new char[] { '\r', '\n' });
    //return result.ToString();
}

This is it. Thanks!

Here, you will see how to convert string to ObjectList, Object to Json string.

Using C# JavascriptSerializer Class

public static class Helper
{
    public static string AsJsonList<T>(List<T> tt)
    {
        return new JavaScriptSerializer().Serialize(tt);
    }
    public static string AsJson<T>(T t)
    {
        return new JavaScriptSerializer().Serialize(t);
    }
    public static List<T> AsObjectList<T>(string tt)
    {
        return new JavaScriptSerializer().Deserialize<List<T>>(tt);
    }
    public static T AsObject<T>(string t)
    {
        return new JavaScriptSerializer().Deserialize<T>(t);
    }
}

It will help you create Json from datatable.

public string DataTableToJsonObj(DataTable dt)
{
    DataSet ds = new DataSet();
    ds.Merge(dt);
    StringBuilder JsonString = new StringBuilder();
    if (ds != null && ds.Tables[0].Rows.Count > 0)
    {
        JsonString.Append("[");
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            JsonString.Append("{");
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                if (j < ds.Tables[0].Columns.Count - 1)
                {
                    JsonString.Append("\"" + 
                    ds.Tables[0].Columns[j].ColumnName.ToString() + 
                    "\":" + "\"" + 
                    ds.Tables[0].Rows[i][j].ToString() + "\",");
                }
                else if (j == ds.Tables[0].Columns.Count - 1)
                {
                    JsonString.Append("\"" + 
                    ds.Tables[0].Columns[j].ColumnName.ToString() + 
                    "\":" + "\"" + 
                    ds.Tables[0].Rows[i][j].ToString() + "\"");
                }
            }
            if (i == ds.Tables[0].Rows.Count - 1)
            {
                JsonString.Append("}");
            }
            else
            {
                JsonString.Append("},");
            }
        }
        JsonString.Append("]");
        return JsonString.ToString();
    }
    else
    {
        return null;
    }

Using any approach, let's just use it... you are fine.

For creating CSV, I would suggest you to use Nuget Package.

Install-Package ServiceStack.Text

Usage

JSON

string JsonSerializer.SerializeToString<T>(T value)
void JsonSerializer.SerializeToWriter<T>(T value, TextWriter writer)

T JsonSerializer.DeserializeFromString<T>(string value)
T JsonSerializer.DeserializeFromReader<T>(TextReader reader)

JSV

string TypeSerializer.SerializeToString<T>(T value)
void TypeSerializer.SerializeToWriter<T>(T value, TextWriter writer)

T TypeSerializer.DeserializeFromString<T>(string value)
T TypeSerializer.DeserializeFromReader<T>(TextReader reader)

CSV

string CsvSerializer.SerializeToString<T>(T value)
void CsvSerializer.SerializeToWriter<T>(T value, TextWriter writer)

T CsvSerializer.DeserializeFromString<T>(string value)
T CsvSerializer.DeserializeFromReader<T>(TextReader reader)

Reference

Happy coding, folks!

Hope all of you comments on issues that you are having. It's up to you to choose which approach you will use.