Converting a JSON string to CSV using CSVHelper

Question

I'm working with JSON/CSV files in my ASP.NET Web API project and tried using the CSVHelper and ServiceStack.Text libraries to generate a CSV, 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 of a delimited type with comma or tab, and and I want to utilize CSVHelper to convert CSV string to an 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;
     }
}

Do you mean dynamic number of columns in your object, or dynamic number of items in the array? — drneel
– drneel, Commented Mar 31, 2016 at 19:45

TylerH · Accepted Answer · 2024-03-20 19:16:38Z

68

I was able to solve it by deserializing using DeserializeObject to a datatable.

To convert JSON string to DataTable with Newtonsoft's Json.NET:

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

To make a CSV string with CSVHelper:

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 WebAPI

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;

edited Mar 20, 2024 at 19:16

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Apr 1, 2016 at 4:25

Priyanka Rathee

1,0732 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stian Over a year ago

Does it work great? My jsonStringToTable crashed due to formatting in the newtonsoft.

Avi Meltser Over a year ago

what do you do when you have a Json object as a field? deserializing to DataTable will throw an error

TylerH · Accepted Answer · 2024-03-20 19:19:57Z

32

Just in case you want to explore an open-source library to do the job, here is one that I wrote.

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 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.

edited Mar 20, 2024 at 19:19

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jul 3, 2017 at 16:05

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

8 Comments

Cinchoo Over a year ago

understood. FYI, It is an open source library, showing how to accomplish the problem using it. Nothing more than that.

Nkosi Over a year ago

I understand. I'm just saying how it is perceived by the community. Just include your relation to the library and it should be Ok.

Arsman Ahmad Over a year ago

I installed your Install-Package ChoETL and then trying to add its namespace. But system giving me error of reference missing. I cleaned solution also but all in vain. Could you help!! @RajN

Cinchoo Over a year ago

Is your project .net framework or .net core? If .net core use ChoETL.JSON.NETStandard package. If .net framework, use ChoETL.JSON

user890332 Over a year ago

This library is superb. It unravels even nested levels of json into a csv, capturing all the data.

|

Slobodan Savkovic · Accepted Answer · 2019-09-20 21:19:35Z

7

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

answered Sep 20, 2019 at 21:19

Slobodan Savkovic

1,05711 silver badges22 bronze badges

1 Comment

Little geek Over a year ago

Can't get this to work if some entries are missing some of the values

Ángel Ibáñez · Accepted Answer · 2021-03-03 08:36:06Z

This code is OK for me:

3 functions (check, parse and aux)

    private bool IsValidJson(string strInput)
    {
        try 
        { 
            if (string.IsNullOrWhiteSpace(strInput)) { return false; }
        
            strInput = strInput.Trim();

            if ((strInput.StartsWith("{") && strInput.EndsWith("}")) || (strInput.StartsWith("[") && strInput.EndsWith("]"))) 
            {
                try
                {
                    _ = JToken.Parse(strInput);

                    return true;
                }
                catch
                {
                    return false;
                }
            }

            return false;
        }
        catch { throw; }
    }

    private string ParseJsonToCsv(string json)
    {
        try
        {
            XmlNode xml = JsonConvert.DeserializeXmlNode("{records:{record:" + json + "}}");

            XmlDocument xmldoc = new XmlDocument(); xmldoc.LoadXml(xml.InnerXml);

            DataSet dataSet = new DataSet(); dataSet.ReadXml(new XmlNodeReader(xmldoc));

            string csv = DTableToCsv(dataSet.Tables[0], ",");

            return csv;
        }
        catch { throw; }
    }

    private string DTableToCsv(DataTable table, string delimator)
    {
        try 
        { 
            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' });
        }
        catch { throw; }
    }

Bunty Thakkar · Accepted Answer · 2022-09-13 09:20:14Z

The below code successfully compiles with latest stable version of CsvHelper nuget package.

public static string JsonToCsv(string jsonContent, string delimeter)
        {
            var expandos = JsonConvert.DeserializeObject<ExpandoObject[]>(jsonContent);

            using (TextWriter writer = new StringWriter())
            {
                CsvConfiguration csvConfiguration = new CsvConfiguration(System.Globalization.CultureInfo.CurrentCulture);
                csvConfiguration.Delimiter = delimeter;
                using (var csv = new CsvWriter(writer, csvConfiguration))
                {
                    csv.WriteRecords((expandos as IEnumerable<dynamic>));
                }

                return writer.ToString();
            }
        }

Sunny Jangid · Accepted Answer · 2017-06-26 07:35:26Z

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

You're using line.Split(',') which is not a good idea. Better to use a csv reader.

Józef Podlecki · Accepted Answer · 2020-05-18 12:59:53Z

-3

using System.Globalization;

using (var csv = new CsvWriter(csvString, CultureInfo.CurrentCulture)) {
  ...
}

edited May 18, 2020 at 12:59

Józef Podlecki

11.4k5 gold badges33 silver badges55 bronze badges

answered Mar 31, 2020 at 10:39

Dev-lop-er

7412 gold badges8 silver badges19 bronze badges

Collectives™ on Stack Overflow

Converting a JSON string to CSV using CSVHelper

7 Answers 7

2 Comments

8 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

8 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related