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();
xmldoc.LoadXml(xml.InnerXml);
var xmlReader = new XmlNodeReader(xmldoc);
DataSet dataSet = new DataSet();
dataSet.ReadXml(xmlReader);
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' });
}
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.
• Experience in analysis, design and development of client/server, web based and n- tier application. Expert in developing windows applications, web applications, windows services and web services in Microsoft Visual Studio .Net IDE using C#.Net 1.1, C#.Net 2.0, VB.NET, ASP.NET, ADO.NET and SQL Server.
• Proficient in Web Services, SOAP, XML, XSL, XSLT, Xpath, XSL-FO, XPointer, DTD, XML Schema, CSS, DOM, HTML, AJAX and JavaScript, JQuery.
• Created deployment packages for Applications using Visual studio.Net Startup Project, which involves creating native image of an assembly, installing an assembly in Global assembly cache (GAC)
• Worked extensively with Data Adapter, Dataset, Data reader as a part of ADO.NET to access and Update database.
• Extensive experience in SQL Server Database design, Database maintenance, developing T-SQL queries, stored procedures, and triggers using SQL Server 2000/2005/2008.
• Good working knowledge with designer tools such as Microsoft Visio.
• Good working knowledge in designing Use Case, Class, Sequence, Collaboration, State,
Component, Deployment, Activity diagrams using UML
• Expertise in designing UML design patterns such as Adapter, Proxy, Bridge, and Facade and so on
• Expert in dealing with Private, Shared satellite and resource assemblies, configuring them with strong names, Deploying in GAC
• Expert in debugging an application using the debugging tools provided by the Visual Studio.Net IDE.
• Functional domain experience involves Financial, Telecom and Hospital management.
• Effective in working independently and collaboratively in teams.
Specialties:C#.NET,ASP.NET,ASP.NET MVC3,EnityFramework,JQuery,Linq to SQL,Oracle11g,TSQL,HTML,CSS,DevExpress,Crystal Reports.
Linkedin Profile