Convert csv file to json using C# with no header

Question

I would like to convert CSV file to JSON using C#. I know that there are a lot of similar questions but I couldn´t find something that could help me.

Source file looks like this:

    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

First line is not header (actually I don't know how to call it - header usually have names of each property). The result should look like this

{
        "transactionsFrom": "2019-12-01T00:00:00.000Z","transactionsTo": "2019-12-10T23:59:59.999Z",
        "transactions": [{
            "logisticCode": "005033971003",
            "siteId": "48",
            "userId":"141",
            "dateOfTransaction": "2019-12-03T15:00:12.077Z",
            "price": 50
            },
            {
            "logisticCode": "005729283002",
            "siteId": "80",
            "userId":"311",
            "dateOfTransaction": "2019-12-02T12:38:05.989Z",
            "price": 100
            }]
}

I would like to use POCO - maybe something like this:

    public class Headers
    {
        public string TransactionFrom { get; set; }
        public string TransactionTo { get; set; }
    }
    public class Results
    {
        public string logisticCode { get; set; }
        public string siteId { get; set; }
        public string userId { get; set; }
        public string dateOfTransaction { get; set; }
        public string price { get; set; }
        public string packSale { get; set; }
    }

But the problem is I don't know how to continue. Maybe some example would help. I know I can use ChoETL, CsvHelper but I don't how.

you can find how to use the [CsvHelper here](https://joshclose.github.io/CsvHelper/getting-started/). The only one problem is that you have to split the first line from the file — oleksa, Jan 10 '20 at 09:55
Does this answer your question? [CsvHelper - Read different record types in same CSV](https://stackoverflow.com/questions/38515214/csvhelper-read-different-record-types-in-same-csv) — xdtTransform, Jan 10 '20 at 14:15
You have multiple item type in the same CSV. follow the linked question to see how it's done with csv helper. basically it boil down to create Two object map , get type A line, move to part with type B . get type B lines. The [selected](https://stackoverflow.com/a/38537012/9260725) answer may look confusing as he is writing the csv on the fly instead of having a string variable to hold it. But thats just the `writer.WriteLine(` and flush part. — xdtTransform, Jan 10 '20 at 14:17

score 2 · Answer 1 · answered Jan 10 '20 at 10:56

This code might help you

Step1 - Create model class

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

public class Transaction
{
    public string logisticCode { get; set; }
    public string siteId { get; set; }
    public string userId { get; set; }
    public string dateOfTransaction { get; set; }
    public string price { get; set; }
    public string packSale { get; set; }
}

Step 2 - Split the file and read the records

         string strInput = @"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";

        var headers = new Headers();
        var transactions = new List<Transaction>();

        var csvrecords = strInput.Split(new[] { '\r', '\n' },StringSplitOptions.RemoveEmptyEntries);
        int count = 1;
        foreach(var record in csvrecords)
        {
            var values = record.Split(';');
            if (count == 1)
            {
                headers.TransactionFrom = values[0];
                headers.TransactionTo = values[1];
            }
            else
            {
                var transaction = new Transaction();
                transaction.logisticCode = values[3].Trim();
                transaction.siteId = values[4].Trim();
                transaction.userId = values[5].Trim();
                transaction.dateOfTransaction = values[2].Trim();
                transaction.price = values[0].Trim();
                transactions.Add(transaction);
            }
            count++;
        }
        headers.Transactions = transactions;
        var jsonString = JsonConvert.SerializeObject(headers);

        Console.WriteLine(jsonString);

Output -

{
    "TransactionFrom": "2019-12-01T00:00:00.000Z",
    "TransactionTo": "2019-12-10T23:59:59.999Z",
    "Transactions": [
        {
            "logisticCode": "005033971003",
            "siteId": "48",
            "userId": "141",
            "dateOfTransaction": "2019-12-03T15:00:12.077Z",
            "price": "50",
            "packSale": null
        },
        {
            "logisticCode": "005740784001",
            "siteId": "80",
            "userId": "311",
            "dateOfTransaction": "2019-12-02T12:38:05.989Z",
            "price": "100",
            "packSale": null
        }
    ]
}

Software Dev · Answer 2 · 2020-01-10T10:35:48.627

I am not sure if i can help you with any codes as your source CSV is very confusing, but i'll try to give you some ideas that might work out.

Firstly, you don't need a model class. I mean, you can use it if you want, but seems unnecessary here.

Next up is reading the CSV file. As you haven't posted any codes related to that and also didn't mention any problem with reading the file, i assume you are reading the file properly. Reading the CSV and writing a JSON from it is relatively easy. However, the CSV file itself looks very confusing. How are you reading it tho? Are you reading it as plain text? Do you have column headers or atleast columns?

If you are reading the file as plain text, then i guess you only have one way. And that is splitting the string and construct a new string with the splitted values. Splitting should be relatively easy as you have ;(semi-colon) which is separating each column/data. So the basic idea is splitting the string and storing it in an array or list, something like this :

 string[] values = myCSV.split(";");

Now all you need to do is, simply use the strings inside values to construct a new string. You can use the StringBuilder for that, or an easy way(not feasible tho) would be string concatenation. I personally would recommend you to go with the StringBuilder.

Guidelines:

StringBuilder in C#

Creating a new line in StringBuilder

Double quotes inside string

Hopefully this gives you some ideas.

Actually reading CSV file is not problem (except for first line). I am using StringBuilder. I don´t have column headers in the text file because content in the file will not change (always will be same). Only problem is how to create string with the first line. I think I will try to create StringBuilder as you mention. Do you recommend better formatting of text file? — jenik2205, Jan 10 '20 at 10:19
@jenik2205, when you use the string builder, you can create new lines for each element in the json. About formatting, i would suggest not to do it in code. Rather, save the string you got from `StringBuilder` to a regular json file and open it in any editor, like VS Code, then format it there. And you don't actually need to worry about formatting because these days, almost all code editors auto-format such files. — Software Dev, Jan 10 '20 at 10:33
actually Json data format can be very important if it is going to be used by another program that do not expect dates (or decimals etc) to be serialized as Json serializer does. The same as concatenating the json string manually can be a tricky when you will need to add one more field for example (comparing to adding new property to the model class). — oleksa, Jan 10 '20 at 13:04

Cinchoo · Accepted Answer · 2020-05-13T13:10:21.880

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"
   }
  ]
 }
]

Convert csv file to json using C# with no header

3 Answers3