JSON for CSV and CSV for converting JSON to C #

Question

I work with JSON / CSV files in my asp.net web API project and tried with CSVHelper and ServiceStack.Text , but could not get it working.

The JSON file containing the array is dynamic and can have any number of fields

I read the file using streamreader and then have to convert it to a CSV file to make it downloadable to end users.

sample file text

[{"COLUMN1":"a","COLUMN2":"b","COLUMN3":"c","COLUMN4":"d","COLUMN5":"e"}, {"COLUMN1":"a","COLUMN2":"b","COLUMN3":"c","COLUMN4":"d","COLUMN5":"e"}] 

JSON for CSV

 public static string jsonStringToCSV(string content) { var jsonContent = (JArray)JsonConvert.DeserializeObject(content); var csv = ServiceStack.Text.CsvSerializer.SerializeToCsv(jsonContent); return csv; } 

It does not give me CSV data

Then some files are a comma or tab delimiter type, and I want to use CSVHelper to convert a CSV string to dynamically IEnumerable

 public static IEnumerable StringToList(string data, string delimiter, bool HasHeader) { using (var csv = new CsvReader(new StringReader(data))) { csv.Configuration.SkipEmptyRecords = true; csv.Configuration.HasHeaderRecord = HasHeader; csv.Configuration.Delimiter = delimiter; var records = csv.GetRecords(); return records; } } 

Answer 1

I was able to solve it using DeserializeObject for data using Json.net, so I want to post my own answer, but I will not mark it as accepted if someone has a better way to do this.

To convert a JSON string to a DataTable

 public static DataTable jsonStringToTable(string jsonContent) { DataTable dt = JsonConvert.DeserializeObject<DataTable>(jsonContent); return dt; } 

To create a CSV string

 public static string jsonToCSV(string jsonContent, string delimiter) { StringWriter csvString = new StringWriter(); using (var csv = new CsvWriter(csvString)) { csv.Configuration.SkipEmptyRecords = true; csv.Configuration.WillThrowOnMissingField = false; csv.Configuration.Delimiter = delimiter; using (var dt = jsonStringToTable(jsonContent)) { foreach (DataColumn column in dt.Columns) { csv.WriteField(column.ColumnName); } csv.NextRecord(); foreach (DataRow row in dt.Rows) { for (var i = 0; i < dt.Columns.Count; i++) { csv.WriteField(row[i]); } csv.NextRecord(); } } } return csvString.ToString(); } 

End use in the web interface

 string csv = jsonToCSV(content, ","); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StringContent(csv); result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "export.csv" }; return result; 

Answer 2

I do not know if it is too late to communicate a solution for your question. Just in case, if you want to research the open source library to do the job, here is one

Cinchoo ETL makes it easy to convert JSON to CSV with a few lines of code

 using (var r = new ChoJSONReader("sample.json")) { using (var w = new ChoCSVWriter("sample.csv").WithFirstLineHeader()) { w.Write(r); } } 

For more info / source go to https://github.com/Cinchoo/ChoETL

Nuget Package:

.NET Framework:

  Install-Package ChoETL.JSON 

.NET Core:

  Install-Package ChoETL.JSON.NETStandard 

Full disclosure: I am the author of this library.

Answer 3

 public void Convert2Json() { try { if (FileUpload1.PostedFile.FileName != string.Empty) { string[] FileExt = FileUpload1.FileName.Split('.'); string FileEx = FileExt[FileExt.Length - 1]; if (FileEx.ToLower() == "csv") { string SourcePath = Server.MapPath("Resources//" + FileUpload1.FileName); FileUpload1.SaveAs(SourcePath); string Destpath = (Server.MapPath("Resources//" + FileExt[0] + ".json")); StreamWriter sw = new StreamWriter(Destpath); var csv = new List<string[]>(); var lines = System.IO.File.ReadAllLines(SourcePath); foreach (string line in lines) csv.Add(line.Split(',')); string json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(csv); sw.Write(json); sw.Close(); TextBox1.Text = Destpath; MessageBox.Show("File is converted to json."); } else { MessageBox.Show("Invalid File"); } } else { MessageBox.Show("File Not Found."); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } 

Answer 4

I had the same issue recently, and I believe there is a more elegant solution using System.Dynamic.ExpandoObject and CsvHelper . This is less code, and hopefully the performance is similar or better compared to the DataTable.

  public static string JsonToCsv(string jsonContent, string delimiter) { var expandos = JsonConvert.DeserializeObject<ExpandoObject[]>(jsonContent); using (var writer = new StringWriter()) { using (var csv = new CsvWriter(writer)) { csv.Configuration.Delimiter = delimiter; csv.WriteRecords(expandos as IEnumerable<dynamic>); } return writer.ToString(); } }