Converting a CSV File to Json Using C#

How to convert CSV string to json (or to c# object's list) without loop? using .NET Framework or .NET Core

This can be done using LINQ and Json.Net (but will still do loops internally) :

using Newtonsoft.Json;
using System.Linq;

// -----------

var data = "\"Product\",\"Date\",\"Expiry\",\"Type\",\"Price\":\"ABC\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"1300\":\"XYZ\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"100\":\"AAA\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"200\":\"XXX\",\"20-Jul-2019\",\"20-Jul-2022\",\"Supplement\",\"500\"";
var datas = data.Split(':'); // string[] containing each line of the CSV
var MemberNames = datas[0].Split(','); // the first line, that contains the member names
var MYObj = datas.Skip(1) // don't take the first line (member names)
                 .Select((x) => x.Split(',') // split columns
                                 /*
                                  * create an anonymous collection
                                  * with object having 2 properties Key and Value
                                  * (and removes the unneeded ")
                                  */
                                 .Select((y, i) => new { Key = MemberNames[i].Trim('"'),
                                                         Value = y.Trim('"') })
                                 // convert it to a Dictionary
                                 .ToDictionary(d => d.Key, d => d.Value));

// MYObject is IEnumerable<Dictionary<string, string>>

// serialize (remove indented if needed)
var Json = JsonConvert.SerializeObject(MYObj, Formatting.Indented);
Debug.WriteLine(Json);

This outputs :

[
  {
    "Product": "ABC",
    "Date": "20-Jul-2019",
    "Expiry": "20-Jul-2022",
    "Type": "Supplement",
    "Price": "1300"
  },
  {
    "Product": "XYZ",
    "Date": "20-Jul-2019",
    "Expiry": "20-Jul-2022",
    "Type": "Supplement",
    "Price": "100"
  },
  {
    "Product": "AAA",
    "Date": "20-Jul-2019",
    "Expiry": "20-Jul-2022",
    "Type": "Supplement",
    "Price": "200"
  },
  {
    "Product": "XXX",
    "Date": "20-Jul-2019",
    "Expiry": "20-Jul-2022",
    "Type": "Supplement",
    "Price": "500"
  }
]

Convert csv file to json using C# with no header

With Cinchoo ETL, you can do it as follows

Define class structures as below

public class Headers
{
    public string TransactionFrom { get; set; }
    public string TransactionTo { get; set; }
    public List<Transaction1> Transactions { get; set; }
}

public class Transaction
{
    [ChoFieldPosition(4)]
    public string logisticCode { get; set; }
    [ChoFieldPosition(5)]
    public string siteId { get; set; }
    [ChoFieldPosition(6)]
    public string userId { get; set; }
    [ChoFieldPosition(2)]
    public string dateOfTransaction { get; set; }
    [ChoFieldPosition(1)]
    public string price { get; set; }
}

Parse the CSV, generate JSON as below

string csv = @"2019-12-01T00:00:00.000Z;2019-12-10T23:59:59.999Z
50;false;2019-12-03T15:00:12.077Z;005033971003;48;141;2019-12-03T00:00:00.000Z;2019-12-03T23:59:59.999Z
100;false;2019-12-02T12:38:05.989Z;005740784001;80;311;2019-12-02T00:00:00.000Z;2019-12-02T23:59:59.999Z";

string csvSeparator = ";";
using (var r = ChoCSVReader.LoadText(csv)
    .WithDelimiter(csvSeparator)
    .ThrowAndStopOnMissingField(false)
    .WithCustomRecordSelector(o =>
    {
        string line = ((Tuple<long, string>)o).Item2;

        if (line.SplitNTrim(csvSeparator).Length == 2)
            return typeof(Headers);
        else
            return typeof(Transaction);
    })
    )
{
    var json = ChoJSONWriter.ToTextAll(r.GroupWhile(r1 => r1.GetType() != typeof(Headers))
        .Select(g =>
        {
            Headers master = (Headers)g.First();
            master.Transactions = g.Skip(1).Cast<Transaction1>().ToList();
            return master;
        }));
    Console.WriteLine(json);
}

JSON Output:

[
 {
  "TransactionFrom": "2019-12-01T00:00:00.000Z",
  "TransactionTo": "2019-12-10T23:59:59.999Z",
  "Transactions": [
   {
     "logisticCode": "005033971003",
     "siteId": "48",
     "userId": "141",
     "dateOfTransaction": "false",
     "price": "50"
   }
   {
     "logisticCode": "005740784001",
     "siteId": "80",
     "userId": "311",
     "dateOfTransaction": "false",
     "price": "100"
   }
  ]
 }
]

Creating JSON from a CSV file in C#

You can add csv records to a List<T> and then serialize it with Newtonsoft.Json to get your required JSON object. See the example below:

    class Program
    {
        static void Main(string[] args)
        {
            string[] csv = new[] { "London,Dubai,4", "Dubai,Mumbai,8", "Dubai,Dhaka,4" };
            List<model> list = new List<model>();

            foreach (var item in csv)
            {

                string[] fields = item.Split(',');
                list.Add(new model
                {
                    From = fields[0],
                    To = fields[1],
                    Duration = fields[2]
                });
            }

            var json = JsonConvert.SerializeObject(list);
            Console.WriteLine(json);
            Console.ReadLine();

        }
    }

    public class model
    {
        public string From { get; set; }
        public string To { get; set; }
        public string Duration { get; set; }
    }

Reading from CSV and converting object to JSON

If possible, get the format of the CSV file right by removing column descriptions like email = and make sure to set a proper column separator like ;.
In the end your file should look like this:

email@example.com;ExampleName;127.0.0.1;login;2019-06-12 13:30:11Z
email2@example.com;ExampleName2;0.0.0.1;logout;2019-06-12 13:30:11Z

Then define a new class:

public class CSVEntry
{
    public string Email { get; set }
    public string Name { get; set; }
    public IPAddress IPAddress { get; set; }
    public string Action { get; set; } 
    public DateTime Timestamp { get; set; }       

    public CSVEntry(string email, string name, IPAddress ip, string action, DateTime timestamp)
    {
        Email = email;
        Name = name;
        IPAddress = ip;
        Action = action;
        Timestamp = timestamp;
    }

    public static CSVEntry Parse(string csvLine)
    {
        string[] args = csvLine.Split(';');

        if (args.Length >= 5)
        {
            string email = args[0];
            string name = args[1];
            IPAddress ip = IPAddress.Parse(args[2]);
            string action = args[3];
            DateTime timestamp = DateTime.Parse(args[4]);

            return new CSVEntry(email, name, ip, action, timestamp);
        }

        return null;
    }
}

If you are unable to change the format, you need to parse the csv line using a different method like regex.

Next, go through the CSV file line by line, create an object using CSVEntry.Parse(line); and add it to a List<CSVEntry> for example:

var list = new List<CSVEntry>();

while (!reader.EndOfStream)
{
    string line = reader.ReadLine();
    list.Add(CSVEntry.Parse(line));
}

Then, you can serialize it using JsonConvert.SerializeObject(list);

JSON string to CSV and CSV to JSON conversion in c#

I was able to solve it by DeserializeObject to a datatable using Json.net, so want to post my own answer but will not mark it as accepted, if anyone have better way to do this.

To convert JSON string to DataTable

public static DataTable jsonStringToTable(string jsonContent)
        {
            DataTable dt = JsonConvert.DeserializeObject<DataTable>(jsonContent);
            return dt;
        }

To make CSV string

public static string jsonToCSV(string jsonContent, string delimiter)
        {
            StringWriter csvString = new StringWriter();
            using (var csv = new CsvWriter(csvString))
            {
                csv.Configuration.SkipEmptyRecords = true;
                csv.Configuration.WillThrowOnMissingField = false;
                csv.Configuration.Delimiter = delimiter;

                using (var dt = jsonStringToTable(jsonContent))
                {
                    foreach (DataColumn column in dt.Columns)
                    {
                        csv.WriteField(column.ColumnName);
                    }
                    csv.NextRecord();

                    foreach (DataRow row in dt.Rows)
                    {
                        for (var i = 0; i < dt.Columns.Count; i++)
                        {
                            csv.WriteField(row[i]);
                        }
                        csv.NextRecord();
                    }
                }
            }
            return csvString.ToString();
        }

Final Usage in Web API

string csv = jsonToCSV(content, ",");

                HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
                result.Content = new StringContent(csv);
                result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
                result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "export.csv" };
                return result;

---

Related Topics