Map CSV Headers to SQL columns using LINQ

Question

I am parsing CSV files and creating a list of objects that i will need to import them in a SQL DB. The csv file has different header names that i can map them for each column of a specific table.

LINQ fails as the names do not match.

I did try every possible tutorial and side way but cannot map the csv headers to a specific column of the db.

using (TextReader reader = File.OpenText(file))
{
    List<CSVObject> ListCSVObject = new List<CSVObject>();
    CsvReader csv = new CsvReader(reader);
    csv.Configuration.Delimiter = ",";
    csv.Configuration.MissingFieldFound = null;

    while (csv.Read())
    {
        CSVObject objectRow = csv.GetRecord<CSVObject>();
        double time = Convert.ToDouble(objectRow.Timestamp);
        DateTime dateTimeUTC = ConvertFromUnixTimestamp(time);
        objectRow.DateTimeUTC = dateTimeUTC;
        ListCSVObject.Add(objectRow);
    }

    LINQToSQLDataContext db = new LINQToSQLDataContext(connectString);
    foreach(CSVObject obj in ListCSVObject)
    {
        db.dbtoinsert.InsertOnSubmit(obj);
    }

    db.SubmitChanges();
}

Harald Coppoolse · Accepted Answer · 2019-05-03 13:50:57Z

1

My advice: don't build your own CSV-file reader; use the Nuget package CsvHelper instead

CsvHelper can take any structured CSV file, with any kind of separator, and with or without a header line.

If your CSV has a header line, you can tell CSV helper that the column that is labeled "Family Name" should be put in the property "LastName" of your class.

Even if you don't have a header line in your CSV file, you can say that column 7 should be mapped to "LastName" and column 9 should be mapped to "BirthDay"

Visit the documentation page for easy examples

answered May 3, 2019 at 13:50

Harald Coppoolse

30.6k7 gold badges78 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeric Cruz · Accepted Answer · 2019-05-02 14:27:09Z

0

You can do object initialization before inserting it to your database.

something like this.

db.dbtoinsert.InsertOnSubmit(new YourTable { Col1 = obj.CSVCol1, ... });

answered May 2, 2019 at 14:27

Jeric Cruz

1,9071 gold badge16 silver badges31 bronze badges

Collectives™ on Stack Overflow

Map CSV Headers to SQL columns using LINQ

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related