0

In my application I recieve JSON data but never know what fields it will contain. What I'd like to do is convert the list of JSON strings into a .csv file and return it as a file result. The tricky thing is I never know what or how many fields the JSON will contain. It could be one field per object or several, with names I cannot predict. All the solutions I found are for set object structures where you can parse the JSON to a c# class that matches the JSON's structure.

Is there a way to easily parse the JSON into a dynamic object and then serializing it into a CSV? Any help is appreciated.

Thanks in advance

Edit

I found this simple export tool that could work using the PropertyInfo of the dynamic object. Suggestions?

Edit 2

Okay, so I'm not using dynamic objects anymore, it just makes stuff more complicated. I'm parsing my JSON into a Dictionary<string, string>, since I realized my JSON only consists of key value pairs. That works flawlessly. Now I need a way to serialize it into CSV, and I would like headers. The CSV export tool I mentioned before doesn't work the way I want, it doesn't support headers and for some reason it adds sep= in the first line. I haven't found a CSV serializer that works without an Object to go off of. Why is this so complicated?

3
  • I don't know why someone vote this question down. But Json can be treated as a tree-like object while the csv can be considered as a table-like file. There's no standard way to convert a tree-like object to a thing of table-like. I think it's better to tell us how you decide to map a property of a.b.c.d to a field in the table. Commented Nov 26, 2018 at 2:43
  • To make it clear, I'm asking how to convert the json string { "x":"ss", "z":{"c1":"c1","c2":"c2"}, "a":[1,2,3] } to csv ? Commented Nov 26, 2018 at 3:25
  • @itminus luckily my JSON object doesn't have arrays or more objects in it. it's just key-value pairs. So far I've figured out how to parse the JSON into a dynamic type, but I'll have to figure out if that works with PropertyInfo Commented Nov 26, 2018 at 8:20

2 Answers 2

2

Since you're assuming the property are simple properties, we can simply treat a property of json as a field in csv.

To make the code clear and clean, I define a Row as SortedDictionary<string,string> :

using Row =SortedDictionary<string,string>; 

I also write a helper class to export json to csv.

public class JsonToCsvExporter{

    public JsonToCsvExporter(string json,string sep=","){
        this._json = json;
        this.Sep = sep;
        this.Rows = new List<Row>();
        this.Headers = new List<string>();
        this.Initialize(json);
    }

    private string _json ;

    public IList<Row> Rows{get;set;}
    public IList<string> Headers { get; set; }
    public string Sep {get;set;}=",";

    private void Initialize(string json){
        var o = JArray.Parse(json);
        this.BuildRows(o, null);
        this.Headers = this.Rows.FirstOrDefault().Keys.ToList();
        this.NormailizeRows();
    }
    private void BuildRows(IEnumerable<JToken> tokens, Row row){
        if(row == null){ row = new Row(); }
        foreach( var token in tokens){
            if (token.Type == JTokenType.Property)
            {
                JProperty prop = (JProperty)token;
                if (!prop.Value.HasValues){
                    row.Add(prop.Name,prop.Value.ToString());
                }
            }
            // if it is not a `JProperty`, they shoud have children,
            //     that means it shoud be treated as a brand new line 
            else if (token.HasValues){
                var _row = new Row();
                BuildRows(token.Children(),_row);
            }
        }
        // if current row has fields, add this row
        if (row.Count>0) {
            this.Rows.Add(row);
        }
    }

    // add null for unspecified values
    private void NormailizeRows() {
        foreach (var row in Rows) {
            foreach (var header in Headers) {
                if (!row.ContainsKey(header)) {
                    row.Add(header,null);
                }
            }
        }
    }

    private async Task ForEach<T>(IEnumerable<T> items,Func<T,Task> funcForFirst,Func<T,Task> funcForEach ){
        if(funcForFirst== null ){ throw new ArgumentNullException(nameof(funcForFirst));}
        if(funcForEach== null ){ throw new ArgumentNullException(nameof(funcForEach));}

        var iter = items.GetEnumerator();
        var flag= iter?.MoveNext();
        if(flag==false){ throw new Exception("items MUST have at least one element");}

        await funcForFirst(iter.Current);

        while(iter.MoveNext()!= false){
            await funcForEach(iter.Current);
        }
    }

    public async Task ExportHeader(StreamWriter writer){
        await this.ForEach(this.Headers,
            async header=>{
                await writer.WriteAsync(header);
            },
            async header=>{
                await writer.WriteAsync(this.Sep);
                await writer.WriteAsync(header);
            }
        );
        await writer.WriteLineAsync();
    }

    public async Task ExportBody(StreamWriter writer)
    {
        foreach (var row in this.Rows) {
            await this.ForEach(row,
                async f=>{
                    await writer.WriteAsync(f.Value);
                },
                async f=>{
                    await writer.WriteAsync(this.Sep);
                    await writer.WriteAsync(f.Value);
                }
            );
            await writer.WriteLineAsync();
        }
    }

}

How to Use and Test Case

static void Main(string[] args)
{
    var json =@"[{
        'F1': 'hello1',
        'F2': 'world1',
        'F3': 'foo1',
        'F4': 'bar2',
    },{
        'F1': 'Hello2',
        'F4': 'Bar2',
    },{
        'F1': 'Hello3',
        'F2': 'World3',
        'F3': null,
        'F4': 'Bar3',
    }]";
    var fs= new FileStream("xxxx.csv",FileMode.OpenOrCreate);
    using(var writer = new StreamWriter(fs)){
        var exporter= new JsonToCsvExporter(json);
        exporter.ExportHeader(writer).Wait();
        exporter.ExportBody(writer).Wait();
        fs.Flush();
    }
}

enter image description here

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

1 Comment

wow - thanks for the extensive answer. I've already figured it out though, I guess I should have updated the question or posted my solution.. sorry for making you do unnecessary work. very nice solution though! ill accept it. :)
1

I accepted itminus's answer due to him finding a suitable solution, and putting a bunch of work into it. Though, I figured it out beforehand. Here is my own solution:

To parse I'm using good 'ol Newtonsoft.Json and to serialize to CSV I'm using jitbit's CsvHelper, as mentioned in the question. My solution takes a List<string> filled with a bunch of JSON objects, which each have the same structure, but the structure is unknown. The only given is that the JSON is filled with key-value pairs, and doesn't contain arrays or more, "deeper" objects.

[Authorize]
public class ExportController : Controller
{
    //Dependency-Injection of database context
    private readonly VoteDbContext c;
    public ExportController(VoteDbContext Context)
    {
        c = Context;
    }

    [HttpGet]
    public FileResult Feedback()
    {
        //get all feedback records
        List<string> jsonData = c.UserFeedback.Select(x => x.Data).ToList();
        //example JSON in this list:
        // {"key1":"val1", "key2":"val2", ...}

        CsvExport csvExport = new CsvExport();

        foreach (string json in jsonData)
        {
            //parse json into usable object
            Dictionary<string, string> currentData = JsonConvert.DeserializeObject<Dictionary<string, string>>(json);

            //add new row for each record
            csvExport.AddRow();
            //add values for row
            foreach (KeyValuePair<string, string> kvp in currentData)
                csvExport[kvp.Key] = kvp.Value;
        }

        //return the generated csv file
        return File(csvExport.ExportToBytes(true)/*true -> with header*/, "text/csv", "Feedback.csv");
    } 
}

I want to return it as a file from the MVC Controller, so the return type is FileResult and I'm returning the output of the File() method.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.