JSON string to CSV and CSV to JSON conversion in c

Question

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

Answer 1

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 2

Had the same problem recently and I believe there is a little bit more elegant solution using the System.Dynamic.ExpandoObject and CsvHelper. It 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();
        }
    }

Answer 3

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

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

Answer 4

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;