Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JSON string to CSV and CSV to JSON conversion in c#

Tags:

I'm working with JSON/CSV files in my asp.net web API project and tried with CSVHelper and ServiceStack.Text libraries but couldn't make it work.

The JSON file containing an array is dynamic and may have any number of fields

I read the file using streamreader and then need to convert it into CSV file to make it downloadable for end users.

example file text

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

JSON to CSV

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

This doesn't result me CSV data

enter image description here

Then some files are delimiter type with comma or tab and and i want to utilize CSVHelper to convert CSV string to IEnumerable dynamically

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;      } } 
like image 968
Priyanka Rathee Avatar asked Mar 29 '16 03:03

Priyanka Rathee


2 Answers

I was able to solve it by DeserializeObject to a datatable using Json.net, so want to post my own answer but will not mark it as accepted, if anyone have better way to do this.

To convert JSON string to DataTable

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

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

Final Usage in Web API

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; 
like image 164
Priyanka Rathee Avatar answered Sep 21 '22 17:09

Priyanka Rathee


I don't know if this is too late to report solution for your question. Just in case if you want to explore open source library to do the job, here is one

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

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

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

Nuget package:

.NET Framework:

      Install-Package ChoETL.JSON 

.NET Core:

      Install-Package ChoETL.JSON.NETStandard 

Sample fiddle: https://dotnetfiddle.net/T3u4W2

Full Disclosure: I'm the author of this library.

like image 29
Cinchoo Avatar answered Sep 17 '22 17:09

Cinchoo