CODEWITHSUNDEEP
c#jsoncsv
axbeit
axbeit

Reputation: 883

Write json string into one .csv column

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

Upvotes: 0

Views: 3980

Answers (1)

xdtTransform
xdtTransform

Reputation: 2057

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

Upvotes: 1

Related Questions