Modify CSV file headers/column names using Cinchoo ETL

Question

I have a .Net Core application where I want to change the column names of a csv file. I'm using the Cinchoo ETL library. I have tried the following:

string csv = "../../../../data.csv";
using (var w = new ChoCSVWriter(csv).WithFirstLineHeader().Setup(s => s.FileHeaderWrite += (o, e) =>
{
    e.HeaderText = "Test,Test2";
}))
{
    w.Write(csv);
}

This is what my data.csv file looks like:

ID,Name
1, David
2, Bob

This is what my csv looks like after running my code:

Test,Test2
../../../../data.csv

The csv header names have changed but my issue is that it deleted all my data and added the path to the file for some odd reason. Any ideas on why that is?

`w.Write(csv);` <--- you are asking the library to write the string that represents the path of your file. It's literally writing the name of the file inside the file. Furthermore, it looks to me like the library is overwriting your file. You should open the file in append mode or something - any good library *should* have this functionality — derekmckinnon, Dec 14 '18 at 14:04
thank you! do you have any libraries in mind? The reason I'm using this library is because I also need to convert the csv into json — sbattou, Dec 14 '18 at 14:31
I have used [CsvHelper](https://joshclose.github.io/CsvHelper/) in the past with good results. As for the JSON piece, if you use strongly-typed models, you can easily use NewtonsoftJSON to convert back and forth between the libs. — derekmckinnon, Dec 14 '18 at 14:43
Thanks for the library! Unfortunately, it's a file I'll be getting from a server so I don't think I'll be using strongly-typed models (if that means creating a class that represents that csv file). I was really hoping to get it to work with the current library that I'm using as adding two libraries might be a little too excessive. — sbattou, Dec 14 '18 at 14:54
You could use `dynamic` or anonymous types as well, but I prefer type safety in the long run as a personal preference. If you are using .NET core, JSON support is already included. Currently they use Newtonsoft. — derekmckinnon, Dec 14 '18 at 15:00
would you be able to post a code snipet of how you would change the csv headers and then convert into json? — sbattou, Dec 14 '18 at 15:10
Here is a basic [gist](https://gist.github.com/derekmckinnon/20b4447e978d410c895e494cb6d73a57) — derekmckinnon, Dec 14 '18 at 15:44
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185257/discussion-between-derekmckinnon-and-sbattoh). — derekmckinnon, Dec 14 '18 at 15:47

Cinchoo · Accepted Answer · 2018-12-15T17:51:39.117

Couple of ways you can rename the columns with new names and produce the CSV output

Option1:

StringBuilder csvIn = new StringBuilder(@"ID,Name
1, David
2, Bob");

StringBuilder csvOut = new StringBuilder();

using (var r = new ChoCSVReader(csvIn)
    .WithFirstLineHeader()
    )
{
    using (var w = new ChoCSVWriter(csvOut)
        .WithFirstLineHeader()
        )
        w.Write(r.Select(r1 => new { Test1 = r1.ID, Test2 = r1.Name }));
}

Console.WriteLine(csvOut.ToString());

Option2:

StringBuilder csvIn = new StringBuilder(@"ID,Name
1, David
2, Bob");

StringBuilder csvOut = new StringBuilder();

using (var r = new ChoCSVReader(csvIn)
    .WithFirstLineHeader()
    )
{
    using (var w = new ChoCSVWriter(csvOut)
        .WithFirstLineHeader()
        .Setup(s => s.FileHeaderWrite += (o, e) =>
        {
            e.HeaderText = "Test,Test2";
        })
        )
        w.Write(r);
}

Console.WriteLine(csvOut.ToString());

UPDATE:

Using CSV files instead of text input

string csvInFilePath = @"C:\CSVIn.csv"
string csvOutFilePath = @"C:\CSVOut.csv"

using (var r = new ChoCSVReader(csvInFilePath)
    .WithFirstLineHeader()
    )
{
    using (var w = new ChoCSVWriter(csvOutFilePath)
        .WithFirstLineHeader()
        )
        w.Write(r.Select(r1 => new { Test1 = r1.ID, Test2 = r1.Name }));
}

UPDATE:

To get the headers, cast record to IDictionary and use Keys property on it to get the keys

string csvInFilePath = @"C:\CSVIn.csv"
string csvOutFilePath = @"C:\CSVOut.csv"

using (var r = new ChoCSVReader(csvInFilePath)
    .WithFirstLineHeader()
    )
{
    foreach (IDictionary<string, object> rec in r)
    {
         var keys = rec.Keys.ToArray();
    }
}

In order to auto discover the datatypes of CSV columns, you must set the MaxScanRows on parser. Otherwise all columns will be treated as string type.

StringBuilder csvIn = new StringBuilder(@"ID,Name,Date
1, David, 1/1/2018
2, Bob, 2/12/2019");

using (var r = new ChoCSVReader(csvIn)
    .WithFirstLineHeader()
    .WithMaxScanRows(2)
    )
{
    foreach (IDictionary<string, object> rec in r.Take(1))
    {
        foreach (var kvp in rec)
            Console.WriteLine($"{kvp.Key} - {r.Configuration[kvp.Key].FieldType}");
    }
}

Hope it helps.

Thank you for your answer! How do pass it a csv file path instead of `(@"ID,Name 1, David 2, Bob");` — sbattou, Dec 14 '18 at 17:05
pass the CSV file path (both inbound/outbound) to the constructors. — Cinchoo, Dec 14 '18 at 17:06
this works fine for me. I changed the column name then convert everything to json. Am I repeating myself too much with the StringBuilders? is there a way to optimize it? https://gist.github.com/sbattoh/6d5531c2e6c2841ac48175a37b0ebf2f — sbattou, Dec 14 '18 at 17:35
you requirement is very simple. Convert CSV to JSON with new field names. Very straightforward. see the update in gist. — Cinchoo, Dec 14 '18 at 17:42
@RajN can you please check https://gist.github.com/sbattoh/6d5531c2e6c2841ac48175a37b0ebf2f — sbattou, Dec 20 '18 at 15:11

Modify CSV file headers/column names using Cinchoo ETL

1 Answers1