Converting a csv file to json using C#

Question

I was wondering if someone's written a utility to convert a CSV file to Json using C#. From a previous question on stackoverflow, I'm aware of this nice utility - https://github.com/cparker15/csv-to-json and at the moment I plan to refer to it but an existing C# implementation would be very helpful! Thanks!

Answer 1

If you can use System.Web.Extensions, something like this could work:

var csv = new List<string[]>(); // or, List<YourClass>
var lines = System.IO.File.ReadAllLines(@"C:\file.txt");
foreach (string line in lines)
    csv.Add(line.Split(',')); // or, populate YourClass          
string json = new 
    System.Web.Script.Serialization.JavaScriptSerializer().Serialize(csv);

You might have more complex parsing requirements for the csv file and you might have a class that encapsulates the data from one line, but the point is that you can serialize to JSON with one line of code once you have a Collection of lines.

Answer 2

Cinchoo ETL - an open source library available to do the conversion of CSV to JSON easily with few lines of code

For a sample CSV:

Id, Name, City
1, Tom, NY
2, Mark, NJ
3, Lou, FL
4, Smith, PA
5, Raj, DC

Sample code,

string csv = @"Id, Name, City
1, Tom, NY
2, Mark, NJ
3, Lou, FL
4, Smith, PA
5, Raj, DC
";

StringBuilder sb = new StringBuilder();
using (var p = ChoCSVReader.LoadText(csv)
    .WithFirstLineHeader()
    )
{
    using (var w = new ChoJSONWriter(sb))
        w.Write(p);
}

Console.WriteLine(sb.ToString());

Output JSON:

[
 {
  "Id": "1",
  "Name": "Tom",
  "City": "NY"
 },
 {
  "Id": "2",
  "Name": "Mark",
  "City": "NJ"
 },
 {
  "Id": "3",
  "Name": "Lou",
  "City": "FL"
 },
 {
  "Id": "4",
  "Name": "Smith",
  "City": "PA"
 },
 {
  "Id": "5",
  "Name": "Raj",
  "City": "DC"
 }
]

Sample fiddle: https://dotnetfiddle.net/pclnsT

Checkout CodeProject article for some additional help.

UPDATE: If your CSV file has duplicate column names or no names, please use the below steps to produce the JSON file

string csv = @"Id, Name, 
1, Tom, NY
2, Mark, NJ
3, Lou, FL
4, Smith, PA
5, Raj, DC
";

StringBuilder sb = new StringBuilder();
using (var p = ChoCSVReader.LoadText(csv)
    .WithField("Id", position: 1)
    .WithField("Name", position: 2)
    .WithField("City", position: 3)
    .WithFirstLineHeader(true)
    )
{
    using (var w = new ChoJSONWriter(sb))
        w.Write(p);
}

Console.WriteLine(sb.ToString());

Sample fiddle: https://dotnetfiddle.net/pP5Du6

Disclaimer: I'm the author of this library.

Answer 3

I used Dictionary and returned json using newtonsoft

public string ConvertCsvFileToJsonObject(string path) 
{
    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);
    }

    return JsonConvert.SerializeObject(listObjResult); 
}

Answer 4

Install Nuget package NewtonSoft.Json
Add reference dll Microsoft.VisualBasic

using System.Linq;
using Newtonsoft.Json;
using Microsoft.VisualBasic.FileIO;
using System.IO;
using System;
using System.Collections.Generic;
using System.Globalization;

namespace Project
{
    public static class Program
    {
        public static void Main(string[] args)
        {
            string CSVpath = @"D:\New Folder\information.csv";
            string analyticsData = ReadFile(CSVpath);
        }

        private static string ReadFile(string filePath)
        {
            string payload = "";
            try
            {
                if (!string.IsNullOrWhiteSpace(filePath) && File.Exists(filePath) && Path.GetExtension(filePath).Equals(".csv", StringComparison.InvariantCultureIgnoreCase))
                {
                    string[] lines = File.ReadAllLines(filePath);

                    if (lines != null && lines.Length > 1)
                    {
                        var headers = GetHeaders(lines.First());
                        payload = GetPayload(headers, lines.Skip(1));
                    }
                }
            }
            catch (Exception exp)
            {
            }
            return payload;
        }

        private static IEnumerable<string> GetHeaders(string data)
        {
            IEnumerable<string> headers = null;

            if (!string.IsNullOrWhiteSpace(data) && data.Contains(','))
            {
                headers = GetFields(data).Select(x => x.Replace(" ", ""));
            }
            return headers;
        }

        private static string GetPayload(IEnumerable<string> headers, IEnumerable<string> fields)
        {
            string jsonObject = "";
            try
            {
                var dictionaryList = fields.Select(x => GetField(headers, x));
                jsonObject = JsonConvert.SerializeObject(dictionaryList);
            }
            catch (Exception ex)
            {
            }
            return jsonObject;
        }

        private static Dictionary<string, string> GetField(IEnumerable<string> headers, string fields)
        {
            Dictionary<string, string> dictionary = null;

            if (!string.IsNullOrWhiteSpace(fields))
            {
                var columns = GetFields(fields);

                if (columns != null && headers != null && columns.Count() == headers.Count())
                {
                    dictionary = headers.Zip(columns, (x, y) => new { x, y }).ToDictionary(item => item.x, item => item.y);
                }
            }
            return dictionary;
        }

        public static IEnumerable<string> GetFields(string line)
        {
            IEnumerable<string> fields = null;
            using (TextReader reader = new StringReader(line))
            {
                using (TextFieldParser parser = new TextFieldParser(reader))
                {
                    parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); fields = parser.ReadFields();
                }
            }
            return fields;
        }
    }
}

Answer 5

Taking only a dependency on Newtonsoft.Json, here's a helper method given an array of CSV lines, the first one being the header.

    public static IEnumerable<JObject> CsvToJson(IEnumerable<string> csvLines)
    {
        var csvLinesList = csvLines.ToList();

        var header = csvLinesList[0].Split(',');
        for (int i = 1; i < csvLinesList.Count; i++)
        {
            var thisLineSplit = csvLinesList[i].Split(',');
            var pairedWithHeader = header.Zip(thisLineSplit, (h, v) => new KeyValuePair<string, string>(h, v));

            yield return new JObject(pairedWithHeader.Select(j => new JProperty(j.Key, j.Value)));
        }
    }

Answer 6

I use ChoETL:

using ChoETL;
using System.IO;

public class FromCSVtoJSON
{
    public FromCSVtoJSON()
    {

    }

    public void convertFile(string inputFile, string outputFile)
    {
        using (var writer = new ChoJSONWriter(outputFile))
        {
            using (var reader = new ChoCSVReader(inputFile).WithFirstLineHeader())
            {
                writer.Write(reader);
            }
        }
    }
}

Answer 7

From that same SO answer, there is a link to this post.

CsvToJson extention method

/// <summary>
/// Converts a CSV string to a Json array format.
/// </summary>
/// <remarks>First line in CSV must be a header with field name columns.</remarks>
/// <param name="value"></param>
/// <returns></returns>
public static string CsvToJson(this string value)
{
    // Get lines.
    if (value == null) return null;
    string[] lines = value.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
    if (lines.Length < 2) throw new InvalidDataException("Must have header line.");

    // Get headers.
    string[] headers = lines.First().SplitQuotedLine(new char[] { ',' }, false);

    // Build JSON array.
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("[");
    for (int i = 1; i < lines.Length; i++)
    {
        string[] fields = lines[i].SplitQuotedLine(new char[] { ',', ' ' }, true, '"', false);
        if (fields.Length != headers.Length) throw new InvalidDataException("Field count must match header count.");
        var jsonElements = headers.Zip(fields, (header, field) => string.Format("{0}: {1}", header, field)).ToArray();
        string jsonObject = "{" + string.Format("{0}", string.Join(",", jsonElements)) + "}";
        if (i < lines.Length - 1)
            jsonObject += ",";
        sb.AppendLine(jsonObject);
    }
    sb.AppendLine("]");
    return sb.ToString();
}

There appears to be an issue with where some methods called within the above extension live (see the comments of the original blog post), but it should get you most of the way there.

EDIT Here is another SO answer about splitting a CSV line. You could use one of the suggested regex solutions to create your own SplitQuotedLine method:

public static string SplitQuotedLine(this string value, char separator, bool quotes) {
    // Use the "quotes" bool if you need to keep/strip the quotes or something...
    var s = new StringBuilder();
    var regex = new Regex("(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)");
    foreach (Match m in regex.Matches(value)) {
        s.Append(m.Value);
    }
    return s.ToString();
}

I did not test the above, so forgive me if I made any errors.

Also, it would appear that Zip is a LINQ extension method, so that takes care of that problem.

Answer 8

Here's mine.. It can parse 9k CSV records in centuries. LOL

class CSVTOJSON
{
    public string ConvertToJSON()
    {
        string json = string.Empty;
        string csv = string.Empty;

        using (StreamReader reader = new StreamReader("data.csv"))
        {
            csv = reader.ReadToEnd();
        }

        string[] lines = csv.Split(new string[] { "\n" }, System.StringSplitOptions.None);

        if (lines.Length > 1)
        {
            // parse headers
            string[] headers = lines[0].Split(',');

            StringBuilder sbjson = new StringBuilder();
            sbjson.Clear();
            sbjson.Append("[");

            // parse data
            for (int i = 1; i < lines.Length; i++)
            {
                if (string.IsNullOrWhiteSpace(lines[i])) continue;
                if (string.IsNullOrEmpty(lines[i])) continue;

                sbjson.Append("{");

                string[] data = lines[i].Split(',');

                for (int h = 0; h < headers.Length; h++)
                {
                    sbjson.Append(
                        $"\"{headers[h]}\": \"{data[h]}\"" + (h < headers.Length - 1 ? "," : null)
                    );
                }

                sbjson.Append("}" + (i < lines.Length - 1 ? "," : null));
            }

            sbjson.Append("]");

            json = sbjson.ToString();
        }

        return json;
    }
}

But it works.

console log:

Converting CSV to JSON
CSV has 9486 data
Total duration converting CSV to JSON: 00:00:00.0775373

Answer 9

Small variation to the solution by bc3tech i.e. avoiding external dependencies (on Newtonsoft.Json), and instead using System.Text.Json (dotnet core 3+)

public static IEnumerable<string> CsvToJson(string fileName, char delim = '|')
{
    var lines = File.ReadLines(fileName); 
    var hdr = new List<string>(lines.First().Trim().Split(delim));
    foreach (var l in lines.Skip(1).Where(l => (l.Trim() != String.Empty))) 
    {
        var val = l.Trim().Split(delim);
        var ds = hdr.Zip(val, (k, v) => new { k, v }).ToDictionary(x => x.k, x => x.v);
        yield return JsonSerializer.Serialize(ds);
    }
}

Answer 10

I can see most people simply assume parsing CSV file is to simply split comma delimiter between each column, but the following format is still a valid CSV

"aaa","bbb","ccc"
"z, z",yyy,xxx

There is a nice class hidden within Microsoft.VisualBasic.FileIO to handle CSV file format correctly. I combine this with JSON.NET came up with the solution.

   public static string? CsvToJson(string input, string delimiter)
   {
        using (TextFieldParser parser = new TextFieldParser(
                 new MemoryStream(Encoding.UTF8.GetBytes(input))))
        {
            parser.Delimiters = new string[] { delimiter };
            string[]? headers = parser.ReadFields();
            if (headers == null) return null;
            string[]? row;
            string comma = "";
            var sb = new StringBuilder((int)(input.Length * 1.1));
            sb.Append("[");
            while ((row = parser.ReadFields()) != null)
            {
                var dict = new Dictionary<string, object>();
                for (int i = 0; row != null && i < row.Length; i++)
                    dict[headers[i]] = row[i];

                var obj = JsonConvert.SerializeObject(dict);
                sb.Append(comma + obj);
                comma = ",";
            }
            return sb.Append("]").ToString();
        }
    }

Usage

var str = @"Header1,""Header,,2 "",Data3
1,444.00, ""Liang, Jerry""
0,""5,550"",Jerry
";

var json = CsvToJson(str, ",");

Result

[
  {
    "Header1": "1",
    "Header,,2": "444.00",
    "Data3": "Liang, Jerry"
  },
  {
    "Header1": "0441",
    "Header,,2": "5,550",
    "Data3": "Jerry"
  }
]

Answer 11

I looked for the answer for this question finally i solved it by using Dictionary

public static void CreateJsonFromCSV()
{
    string path = "C:\\Users\\xx\\xx\\xx\\xx\\lang.csv";
    string textFilePath = path;
    const Int32 BufferSize = 128;

    using (var fileStream = File.OpenRead(textFilePath))
    using (var streamReader = new StreamReader(fileStream, Encoding.UTF8, true, BufferSize))
    {
        String line;
        Dictionary<string, string> jsonRow = new Dictionary<string, string>();

        while ((line = streamReader.ReadLine()) != null)
        {

            string[] parts = line.Split(',');

            string key_ = parts[0];
            string value = parts[1];


            if (!jsonRow.Keys.Contains(key_))
            {
                jsonRow.Add(key_, value );
            }

        }
        var json = new JavaScriptSerializer().Serialize(jsonRow);
        string path_ = "C:\\XX\\XX\\XX\\XX\\XX.csv";
        File.WriteAllText(path_, json);
    }

} 

Answer 12

Make sure you add the below in web.config before you do parse large csv files.

 <system.web.extensions>
       <scripting>
           <webServices>
               <jsonSerialization maxJsonLength="50000000"/>
           </webServices>
       </scripting>
   </system.web.extensions>

Answer 13

Try this:

 StreamReader sr = new StreamReader(filePath);
 while ((line = sr.ReadLine()) != null)
 {
      //Console.WriteLine(line);
      string[] csv = line.Split(',');
      var dictionary = new Dictionary<string, string>();
      dictionary.Add("dispatching_base_number",csv[0]);
      dictionary.Add("available_vehicles", csv[1]);
      dictionary.Add("vehicles_in_trips", csv[2]);
      dictionary.Add("Cancellations", csv[3]);
      string jsonN = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(dictionary);
      Console.WriteLine("Sending message: {0}",jsonN);
 }

Answer 14

Try this and convert CSV to JSON object:

public static List<object> CsvToJson( string body, string[] column ) {
    if ( string.IsNullOrEmpty( body ) ) return null;
    string[] rowSeparators = new string[] { "\r\n" };
    string[] rows = body.Split( rowSeparators, StringSplitOptions.None );
    body = null;
    if ( rows == null || ( rows != null && rows.Length == 0 ) ) return null;
    string[] cellSeparator = new string[] { "," };
    List<object> data = new List<object>( );
    int clen = column.Length;
    rows.Select( row => {
        if ( string.IsNullOrEmpty( row ) ) return row;
        string[] cells = row.Trim( ).Split( cellSeparator, StringSplitOptions.None );
        if ( cells == null ) return row;
        if ( cells.Length < clen ) return row;
        Dictionary<object, object> jrows = new Dictionary<object, object>( );
        for ( int i = 0; i < clen; i++ ) {
            jrows.Add( column[i], cells[i]?.Trim( ) );
        }
        data.Add( jrows );
        return row;
    } ).ToList( );
    rowSeparators = null; rows = null;
    cellSeparator = null;
    return data;
}

var data = CsvToJson("csv_input_str", new string[]{ "column_map" })
string jsonStr = new JavaScriptSerializer { MaxJsonLength = int.MaxValue }.Serialize( data );

Answer 15

First, load the csv file into datatable and serialize it to Json document. It uses OLEDB Provider that can parse the csv wisely,

Courtesy to Jim Scott, https://stackoverflow.com/a/1050278/6928056

Courtesy to K_B, https://stackoverflow.com/a/2979938/6928056

using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using Newtonsoft.Json;

static string ConvertCsvToJson(string path, bool isFirstRowHeader)
{
    string header = isFirstRowHeader ? "Yes" : "No";

    string pathOnly = Path.GetDirectoryName(path);
    string fileName = Path.GetFileName(path);

    string sql = @"SELECT * FROM [" + fileName + "]";

    using(OleDbConnection connection = new OleDbConnection(
              @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + 
              ";Extended Properties=\"Text;HDR=" + header + "\""))
    using(OleDbCommand command = new OleDbCommand(sql, connection))
    using(OleDbDataAdapter adapter = new OleDbDataAdapter(command))
    {
        var dataTable = new DataTable();
        dataTable.Locale = CultureInfo.CurrentCulture;
        adapter.Fill(dataTable);
        return JsonConvert.SerializeObject(dataTable, Formatting.Indented);
    }
}

Answer 16

Simple method to convert flat csv file to a collection of simple json formatted objects. Works with files with header row on the first line. Part of this method was found somewhere else on SO. Add reference to Microsoft.VisualBasic.

using Microsoft.VisualBasic.FileIO;
public static StringBuilder ReadCsv()
    {
        var path = @"X:\...\input.csv";
        using (TextFieldParser csvParser = new TextFieldParser(path))
        {
            csvParser.CommentTokens = new string[] { "#" };
            //Remember to use your own separator
            csvParser.SetDelimiters(new string[] { ";" });
            csvParser.HasFieldsEnclosedInQuotes = false;

            StringBuilder json = new StringBuilder();
            string[] colNames = new string[0];
            string[] fields = new string[0];

            json.Append("[");

            int counter = 0;
            while (!csvParser.EndOfData)
            {
                if (counter == 0)
                {
                    //Read properies' names
                    colNames = csvParser.ReadFields();
                    counter++;
                    Console.WriteLine($"{colNames.Length} columns detected!");
                }
                else
                {
                    // Read current line fields, pointer moves to the next line.
                    // Read the properties' values
                    fields = csvParser.ReadFields();

                    json.Append("{");

                    for (int i = 0; i < colNames.Length; i++)
                    {
                        json.Append($"\"{colNames[i]}\":{TryParse(fields[i])}");
                        if (i != colNames.Length - 1)
                        {
                            json.Append(",");
                        }
                    }
                    json.Append("},");
                    Console.WriteLine($"Writing record nr.: {counter}");
                    counter++;
                }
            }
            json.Length--; //Remove trailing comma
            json.Append("]");
            return json;
        }
    }

string TryParse(string s)
    {
        if (string.IsNullOrEmpty(s)) return "null";
        //Remember to set your decimal character here!
        if (s.Contains('.'))
        {
            double dResult;
            //This works on my computer, could be different on your machine
            if (double.TryParse(s, NumberStyles.AllowDecimalPoint,
                                CultureInfo.InvariantCulture, out dResult))
                return dResult.ToString(CultureInfo.InvariantCulture);
        }
        else
        {
            int intResult;
            if (int.TryParse(s, out intResult))
                return intResult.ToString(CultureInfo.InvariantCulture);
        }
        return "\"" + s + "\"";
    }

---

This should give you a simple list of json objects.

Answer 17

If you are looking for a C# only solution this might work for you. I recently face the same issue and created this method to overcome.

public static string ConvertToJsonStructure(string csvDataAsString, char delimiter = ',', bool hasHeaders = true)
    {
        var output = string.Empty;
        if (string.IsNullOrEmpty(csvDataAsString))
            return "{}";

        var rows = csvDataAsString.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
        var headers = hasHeaders ? rows[0].Split(delimiter) : null;
        var colCount = headers?.Length;
        var jsonStart = "{";
        var jsonEnd = "}";

        if (rows.Length > (1 + (hasHeaders ? 1 : 0)))
        {
            jsonStart = "[";
            jsonEnd = "]";
        }

        output = output + jsonStart;

        int startIndex = hasHeaders ? 1 : 0;

        for (int i = startIndex; i < rows.Length; i++)
        {
            var cols = rows[i].Split(delimiter);

            if (colCount == null)
                colCount = cols.Length;

            var tempJson = "{";

            for (int j = 0; j < colCount.Value; j++)
            {
                if (hasHeaders)
                    tempJson = tempJson + $"\"{headers[j]}\":";

                var isNumber = Regex.IsMatch(cols[j], @"^\d + $");
                var val = isNumber ? cols[j] : $"\"{cols[j]}\"";
                tempJson = tempJson + val;

                if (j < colCount.Value - 1)
                    tempJson = tempJson + ",";

            }

            tempJson = tempJson + "}";

            if (i < rows.Length - 1)
                tempJson = tempJson + ",";

            output = output + tempJson;
        }

        output = output + jsonEnd;

        return output;
    }
}

You need to pass your content as string to this method and you can do something like this to read your csv

var csvAsString = File.ReadAllText("TestCsv.csv");

        var result = CsvToJson.ConvertToJsonStructure(csvAsString);

Answer 18

For the myriad reasons you're seeing in all these answers, stand on the shoulders of giants and use a Csv Parsing nuget package to pull out the data, then serialize it to JSON using built-in serializers. There is no reason at all to hand-roll this.