How to export JSON string to CSV file?

Question

I have ASP.Net site. I have JSON string that need to exported to physical CSV file.

 private String JsonToCsv(string jsonData, string delimiter)
    {
        try
        {
            StringWriter swObj = new StringWriter();
            using (var csv = new CsvWriter(swObj))
            {
                csv.Configuration.SkipEmptyRecords = true;
                csv.Configuration.WillThrowOnMissingField = false;
                csv.Configuration.Delimiter = delimiter;

                using (var dt = jsonStringToTable(jsonData))
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        csv.WriteField(col.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 swObj.ToString();

        }
        catch (Exception ex)
        {
            //handle exception 
            return null;
        }
    }

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

    public HttpResponseMessage ExportToCSV(string jsonData)
    {
        string csv = JsonToCsv(jsonData, ",");

        HttpResponseMessage res = new HttpResponseMessage(HttpStatusCode.OK);
        res.Content = new StringContent(csv);
        res.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
        res.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "export.csv" };
        return res;
    }

But neither I am getting any exception nor CSV file is getting any data.

The Export.csv is located in the root folder.

How do I export the JSON & auto download the file??

Answer 1

It seems you do have an error, that you are suppressing in your catch.

Your first error is in that your jsonContent is not actually json. You have a variable assignment jsonContent = [...] in your sample. the section [...] is your actual json.

to handle that, you need only compose it better, by not having it assign to a variable (recommended approach), or handle instances here with jsonContent.Split(new [] {'='}).Last(). (a declarative vs imperative approach/strategy).

Also, you are attempting to deserialize into an incorrect type, for it does not reflect your json data structure.

---

although there are other manners to convert and process one string to another. I do agree the proper thing to do here is to deserialize your object (or not serialize beforehand - recommended).

---

I'm providing a sample Console Application for you to review two implementations of handle a JsonToCsv operation.

• dynamic (imperative)
• and providing a Type and using System.Reflection on that type. (declarative to the Json.Convert.DeserializeObject<T>() method, imperative afterward)

there is a dependency on NewtonSoft.Json Assembly (Install it via NuGet package) in this implementation; it reflects your provided code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Dynamic;
using System.Reflection;
using Newtonsoft;


namespace JsonToCsvTests
{
    using Newtonsoft.Json;
    using System.IO;
    using System.Threading.Tasks;

    class Program
    {

        static void Main(string[] args)
        {
            TestJsonToCsv();
            Console.ReadLine();
        }

        static void TestJsonToCsv()
        {
            string jsonData = @"jsonData = [
                        {
                            ""DocumentName"": ""Test Document"",
                            ""ActionDate"": ""2015-09-25T16:06:25.083"",
                            ""ActionType"": ""View"",
                            ""ActionPerformedBy"": ""Sreeja SJ""
                        },
                        {
                            ""DocumentName"": ""Test Document"",
                            ""ActionDate"": ""2015-09-25T16:12:02.497"",
                            ""ActionType"": ""View"",
                            ""ActionPerformedBy"": ""Sreeja SJ""
                        },
                        {
                            ""DocumentName"": ""Test Document"",
                            ""ActionDate"": ""2015-09-25T16:13:48.013"",
                            ""ActionType"": ""View"",
                            ""ActionPerformedBy"": ""Sreeja SJ""
                        }]";

            Console.WriteLine("...using System.Dynamic and casts");
            Console.WriteLine();
            Console.WriteLine(JsonToCsv(jsonData, ","));
            Console.WriteLine();
            Console.WriteLine();
            Console.WriteLine("...using a provided StrongType with System.Reflection.");
            Console.WriteLine();
            Console.WriteLine(JsonToCsv<JsonData>(jsonData, ","));
        }

        static private string JsonToCsv(string jsonContent, string delimiter)
        {
            var data = jsonStringToTable(jsonContent);
            var headers = ((IEnumerable<dynamic>)((IEnumerable<dynamic>)data).First()).Select((prop) => prop.Name).ToArray();
            var csvList = new List<string> 
            {
                string.Join(delimiter, headers.Select((prop) => string.Format(@"""{0}""", prop)).ToArray())
            };

            var lines = ((IEnumerable<dynamic>)data)
                .Select(row => row)
                .Cast<IEnumerable<dynamic>>()
                .Select((instance) => string.Join(delimiter, instance.Select((v) => string.Format(@"""{0}""", v.Value))))
                .ToArray();

            csvList.AddRange(lines);
            return string.Join(Environment.NewLine, csvList );
        }

        static private string JsonToCsv<T>(string jsonContent, string delimiter) where T : class
        {
            var data = jsonStringToTable<T>(jsonContent);

            var properties = data.First().GetType().GetProperties();
            
            var lines = string.Join(Environment.NewLine,
                string.Join(delimiter, properties.Select((propInfo) => string.Format(@"""{0}""", propInfo.Name))),
                string.Join(Environment.NewLine, data.Select((row) => string.Join(delimiter, properties.Select((propInfo) => string.Format(@"""{0}""", propInfo.GetValue(row)))))));

            return lines;
        }

        static private dynamic jsonStringToTable(string jsonContent)
        {
            var json = jsonContent.Split(new[] { '=' }).Last();
            return JsonConvert.DeserializeObject<dynamic>(json);
        }

        static private IEnumerable<T> jsonStringToTable<T>(string jsonContent) where T : class
        {
            var json = jsonContent.Split(new[] { '=' }).Last();
            return JsonConvert.DeserializeObject<IEnumerable<T>>(json);
        }

        public class JsonData
        {
            public string DocumentName { get; set; }
            public DateTime ActionDate { get; set; }
            public string ActionType { get; set; }
            public string ActionPerformedBy { get; set; }
        }
    }
}

---

Console.Output

...using System.Dynamic and casts

"DocumentName","ActionDate","ActionType","ActionPerformedBy"
"Test Document","9/25/2015 4:06:25 PM","View","Sreeja SJ"
"Test Document","9/25/2015 4:12:02 PM","View","Sreeja SJ"
"Test Document","9/25/2015 4:13:48 PM","View","Sreeja SJ"


...using a provided StrongType with System.Reflection.

"DocumentName","ActionDate","ActionType","ActionPerformedBy"
"Test Document","9/25/2015 4:06:25 PM","View","Sreeja SJ"
"Test Document","9/25/2015 4:12:02 PM","View","Sreeja SJ"
"Test Document","9/25/2015 4:13:48 PM","View","Sreeja SJ"

Answer 2

This is what i use to generate CSV file on my ASP.NET Website

    public static class CSVUtils
{
    public static void AddCsvLine(bool isFrenchSeparator, StringBuilder csv, params object[] values)
    {
        foreach (var value in values)
        {
            csv.Append('"').Append(value).Append('"');
            if (isFrenchSeparator)
            {
                csv.Append(';');
            }
            else
            {
                csv.Append(',');
            }
        }
        csv.Append('\r'); // AppendLine() adds a double line break with UTF32Encoding
    }
}

    public FileContentResult ExportCSV()
    {
        StringBuilder csv = new StringBuilder();
        CSVUtils.AddCsvLine(false, csv, "Field1", "Field2", "Field3");
        CSVUtils.AddCsvLine(false, csv, "value1", "value2", "value3");

        return this.File(new UTF32Encoding().GetBytes(csv.ToString()), "text/csv", "myfile.csv");
    }

I basically call the ExportCSV action from my website, on a button click for example and it downloads the file. Make sure to clean your JSON beforehand from all coma otherwise it would mess your CSV file.

EDIT: Specifically for JSON, you'd also have to anti-slash every " otherwise it would mess the StringBuilder i guess