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