Write json string into one .csv column

Question

I want to write a string into one Column in an .csv (Excel) file. My Problem is that the string is written into multiple Columns.

In this screenshot for example I have 20 Columns.

        GetMetadataCompleteResponse resultValue = null;
        string jsonData = null;

        await Task.Run(() =>
        {
            byte[] rawData = Convert.FromBase64String(responseContent);
            jsonData = CompressUtil.Unzip(rawData);
        });

        resultValue = JsonConvert.DeserializeObject<GetMetadataCompleteResponse>(jsonData);

       foreach(string a in resultValue.Value.Values)
            {
                foreal += a;
            }
       await Log.Info("callWebservice for " + strUrl + ", Result: " + objErrorDetails.Code + ", " + foreal);

edit

I've noticed that the new Column starts after every ';'(semicolon). I probably can just replace it with something else.

@TheIncorrigible1 hey there. I'm quite new to json. What is mean with flat json? — axbeit
– axbeit, Commented May 14, 2019 at 13:46

Community · Accepted Answer · 2021-10-07 11:07:30Z

I think you have 2 issues. The first one is how you write your CSV with simple string concatenation. With no escaping or double quote. The Json will have commas , that will be separator in your CSV.

In order to produc e a valid CSV you should read the RFC 4180 and use a proper library to handle the Serialisation.

Here is an Minimal, Complete, and Verifiable example of writing a Json in a CSV column.

using CsvHelper;
using CsvHelper.Configuration;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
                    
public class Program
{
    public static void Main()
    {
        var input = new Foo
        {
            Label = "My Foo",
            Bars = new List<Bar> {
                new Bar{Label="Bar2"},
                new Bar{Label="Bar1"},
                new Bar{Label="Bar3"},
            }
        };

        var json = JsonConvert.SerializeObject(input);

        var myObject = new CsvObject
        {
            Label = "My CSV object",
            FooString = json,
        };

        var result = "";

        // Writing into a string instead of a file for debug purpuse. 
        using (var stream = new MemoryStream())
        using (var reader = new StreamReader(stream))
        using (var writer = new StreamWriter(stream))
        using (var csv = new CsvWriter(writer))
        {

            csv.Configuration.RegisterClassMap<CsvObjectMap>();

            csv.WriteHeader<CsvObject>();                
            csv.NextRecord();

            csv.WriteRecord(myObject);                
            csv.NextRecord();

            writer.Flush();
            stream.Position = 0;
            result = reader.ReadToEnd();
        }
        Console.WriteLine(result);
    }
    
    private sealed class CsvObjectMap : ClassMap<CsvObject>
    {
        public CsvObjectMap()
        {
            Map( m => m.FooString );
            Map( m => m.Label );
        }
    }
    public class CsvObject
    {
        public string Label { get; set; }
        public string FooString { get; set; }
    }
    public class Foo
    {
        public string Label { get; set; }
        public List<Bar> Bars { get; set; }
    }
    public class Bar
    {
        public string Label { get; set; }
    }
}

Live demo : https://dotnetfiddle.net/SNqZX1

In this exemple I have used CsvHelper for CSV serialisation, and Json.NET for the Json serialisation. Note that Writing a CSV to a file is a more simlpe task that to a string like in this example

Collectives™ on Stack Overflow

Write json string into one .csv column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related