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

Solution 1

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;

Solution 2

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.

Solution 3

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

Solution 4

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

View more solutions

Comments

[Eminem]

Can you please provide us with the error that is given or the output

[Priyanka Rathee]

@Eminem Please see the excel screenshot.

[Eminem]

Im asking for the output that it DOES give. Not what you expect it to give

[Priyanka Rathee]

Yes same, the screenshot is of what it gives not as i expects.

[Eminem]

Mybad. I thought those were the actual values

[Eminem]

It would appear as if it is outputting the properties of a type? Can you confirm that your content coming through correctly

[Priyanka Rathee]

Yes, i do confirm. I checked in debug mode and on printed the json on console as a string looking good

[Saleem]

See my answer to another similar question. Only difference is OP was converting DataTable to JSON instead of CSV. stackoverflow.com/questions/36272054/…

[Priyanka Rathee]

I'm looking to utilize the csvhelper or ServiceStack.Text library instead write a lots of code because the CSV is tricky and a single misplaced comma etc may break the code

[drneel]

Do you mean dynamic number of columns in your object, or dynamic number of items in the array?

[Matt]

Since you seem to use Excel, have you considered the built-in data transformation functionality? Have a look into this youtube tutorial for details. You can import & transform JSON data in Excel and then save it as CSV.

[marcus]

Just what I was trying to accomplish. Works great!

[Cinchoo]

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

[Nkosi]

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.

[stian]

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

[Arsman Ahmad]

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]

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

[Victor_Tlepshev]

When I am searching in NuGet library for the Json.NET I get back the Newtonsoft.Json in the result window. I guess it is the same. It is already installed in my project but CsvWriter seems to be missing. The project is using .NET v4.6.2 Any ideas how to get it to work?

[Priyanka Rathee]

CsvWriter is available in another package called CsvHelper - github.com/JoshClose/CsvHelper you may install > Install-Package CsvHelper

[pso]

@RajN getting "Method not found: 'Void ChoETL.ChoRecordReader..ctor(System.Type, Boolean)'." on your package usage.

[Cinchoo]

Found out the incompatible dependencies in the package. Corrected, pushed new version 1.0.9.

[user890332]

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

[user890332]

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

[Varun]

I found this solution the best of all. :-)

[Avi Meltser]

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

[Little geek]

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

[KWallace]

@Victor_Tlepshev - What Victor said.