Convert CSV to JSON, How to address columns containing comma(,) using c#

Question

I tried to convert CSV data to JSON. It quiet worked fine but few columns have comma, while converting to json, comma contained data is getting split. This is the code I tried,

var path = @"C:xyz\\abc.csv";
            var csv = new List<string[]>();
            var lines = File.ReadAllLines(path);

            foreach (string line in lines)
                csv.Add(line.Split(','));

            var properties = lines[0].Split(',');

            var listObjResult = new List<Dictionary<string, string>>();

            for (int i = 1; i < lines.Length; i++)
            {
                var objResult = new Dictionary<string, string>();
                for (int j = 0; j < properties.Length; j++)
                    objResult.Add(properties[j], csv[i][j]);

                listObjResult.Add(objResult);
            }
            var json = JsonConvert.SerializeObject(listObjResult, Formatting.Indented);
            List<ABCModel> desrilize = JsonConvert.DeserializeObject<List<ABCModel>>(json);
            return desrilize;

CSV data

employee,emp city,state,emp address
"abc","efg","lkj","building name"
"wer","sdf","qwe","afj Building, near cross"

In above third line contains comma, which should not get split while converting to json. Where as using above code, its getting split. Kindly help.

Also there is a space in "emp city", how to define jsonProperty for the same while creating model.

Expected json

[
  {
    "employee": "abc",
    "emp city": "efg",
    "state": "lkj",
    "emp address": "building name"
  },
  {
    "employee": "wer",
    "emp city": "sdf",
    "state": "qwe",
    "emp address": "afj Building, near cross"
  }
]

It's more complex then you thought. Use a library like [CsvHelper](https://joshclose.github.io/CsvHelper/). It takes care for all these nifty wrinkles. — Oliver, Feb 17 '22 at 07:09
You are ignoring the `"` which enclose a field's content. You must only use commas that aren't enclosed by double quotes a field separator. Furthermore, why are you creating a JSON string that you then immediately parse back into an object? You can create the object directly. And finally, you are not removing the double quotes from your values, ie even if everything else works, your json will look as follows `[{"employee": "\"abc\"", ...},..]` — derpirscher, Feb 17 '22 at 07:20
use an existing CSV-parser, asl oliver suggested. done. no need to reinvent the wheel. — Franz Gleichmann, Feb 17 '22 at 07:23
Thank you @Oliver. It helped. I have one more query. There is space "emp city". Unable to define it in the model as json property. Any solution? — Aishwarya Gujjar, Feb 17 '22 at 08:50
`[JsonPropertyName("emp city")] public string EmpCity { get; set; }` tried this. Didn't work. Is it wrong? — Aishwarya Gujjar, Feb 17 '22 at 09:11

score 0 · Answer 1 · answered Feb 17 '22 at 07:10

You can try with csvHelper or csv converter. using csv:

var options = new CsvOptions // Defaults
            {
                RowsToSkip = 0, // Allows skipping of initial rows without csv data
                SkipRow = (row, idx) => string.IsNullOrEmpty(row) || row[0] == '#',
                Separator = '\0', // Autodetects based on first row
                TrimData = false, // Can be used to trim each cell
                Comparer = null, // Can be used for case-insensitive comparison for names
                HeaderMode = HeaderMode.HeaderPresent, // Assumes first row is a header row
                ValidateColumnCount = true, // Checks each row immediately for column count
                ReturnEmptyForMissingColumn = false, // Allows for accessing invalid column names
                Aliases = null, // A collection of alternative column names
                AllowNewLineInEnclosedFieldValues = false, // Respects new line (either \r\n or \n) characters inside field values enclosed in double quotes.
                AllowBackSlashToEscapeQuote = false, // Allows the sequence "\"" to be a valid quoted value (in addition to the standard """")
                AllowSingleQuoteToEncloseFieldValues = false, // Allows the single-quote character to be used to enclose field values
                NewLine = Environment.NewLine // The new line string to use when multiline field values are read (Requires "AllowNewLineInEnclosedFieldValues" to be set to "true" for this to have any effect.)
            };

            var csv = File.ReadAllText(fileName or filePath);
            foreach (var line in CsvReader.ReadFromText(csv, options))
            {
                yourModel.Add(new yourModel()
                {
                    StoneCode = line["Field1"],
                    StoneTypeName = line["Field2"],
                });
            }

@AishwaryaGujjar If this answer helped or solved your problem, you should upvote and/or accept it. — Chandra Shakar, Feb 18 '22 at 08:12

score 0 · Answer 2 · answered Feb 17 '22 at 10:22

To read your desired CSV input by using CsvHelper, this example should help you:

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using CsvHelper;
using CsvHelper.Configuration;

public class Program
{
    public static void Main()
    {
        var csvContent = @"employee,emp city,state,emp address
""abc"",""efg"",""lkj"",""building name""
""wer"",""sdf"",""qwe"",""afj Building, near cross""";

        List<Employee> employees;
        using (var reader = new StringReader(csvContent))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            csv.Context.RegisterClassMap<EmployeeMap>();
            var records = csv.GetRecords<Employee>();
            employees = records.ToList();
        }

        foreach (var employee in employees)
        {
            Console.WriteLine(System.Text.Json.JsonSerializer.Serialize(employee));
        }
    }
}

public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        Map(e => e.Name).Name("employee");
        Map(e => e.City).Name("emp city");
        Map(e => e.State).Name("state");
        Map(e => e.Address).Name("emp address");
    }
}

public class Employee
{
    public string Name { get; set; }

    public string City { get; set; }

    public string State { get; set; }

    public string Address { get; set; }
}

Thank you. It works like gem. Just a small change StringReader to StreamReader. — Aishwarya Gujjar, Feb 17 '22 at 17:36
If this answer helped or solved your problem, you should upvote and / or accept it. — Oliver, Feb 18 '22 at 07:15
I have accepted the answer but its invisible. Its saying, I do not have expected reputation to accept the answer, since I am new to Stack overflow. — Aishwarya Gujjar, Feb 18 '22 at 18:31

Convert CSV to JSON, How to address columns containing comma(,) using c#

2 Answers2