CsvHelper - write to SQL Database with mapping

Question

The CSVHelper .NET library seems fantastic so far, but the documentation is a little lacking for a pseudo-beginner like myself.

I need to read a csv file and write the results to our SQL Server database. For the table I'm writing to, I need to map to its columns from the CSV columns, including some concatenation of multiple fields to one.

This is what I have for reading the csv file:

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");
        var records = csv.GetRecords<EntityCsv>().ToList();
    }
}

My EntityCsv class contains property names for all columns of the csv file.

Then, I also have a class called TaskEntity which contains the property names and types for the destination database table (although I'm unclear as to whether I need this).

Finally, per advice from a colleague, I have a method set up to make use of SQLBulkCopy as thus:

public void AddBulk(List<TaskEntity> entities)
{
    using (var con = GetConnection())
    {
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.BatchSize = 2000;
        bulk.BulkCopyTimeout = 0;
        bulk.DestinationTableName = "dbo.CsvExports";
        bulk.WriteToServer(entities.AsDataTable());
        bulk.Close();
    }
}

I borrowed that code block from him and would theoretically run that method as the final step.

But I know I'm missing a step in between, and that is mapping the fields from the csv to the SQL server field. I'm scratching my head at how to implement this step.

So let's say for simplicity's sake I have 3 columns in the csv file, and I want to map them to 2 columns of the SQL table as follows:

CsvColumn1 -> SQLtableColumn1
CsvColumn2 + CsvColumn3 -> SQLtableColumn2

How would I go about accomplishing this with CsvReader and C#? I have explored the Mapping section of the CSVReader documentation but everything I'm seeing in there seems to refer to mapping column names from an input file to names in an output file. I don't see anything there (nor anywhere on the Google) that speaks specifically to taking the input file and exporting its rows to a SQL database.

David Specht · Accepted Answer · 2019-09-30 13:18:48Z

6

+50

You can use a ClassMap to map the csv columns to the sql table columns and skip the CsvEntity class.

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");

        csv.Configuration.RegisterClassMap<TaskEntityMap>();

        var records = csv.GetRecords<TaskEntity>().ToList();
    }
}

public class TaskEntity
{
    public int Id { get; set; }
    public string SqlTableColumn1 { get; set; }
    public string SqlTableColumn2 { get; set; }
}

public sealed class TaskEntityMap : ClassMap<TaskEntity>
{
    public TaskEntityMap()
    {
        Map(m => m.SqlTableColumn1).Name("CsvColumn1");
        Map(m => m.SqlTableColumn2).ConvertUsing(row => row.GetField<string>("CsvColumn2") + " " + row.GetField<string>("CsvColumn3"));
    }
}

answered Sep 30, 2019 at 13:18

David Specht

9,2691 gold badge26 silver badges37 bronze badges

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

5 Comments

Stpete111 Over a year ago

Good to see you again, David. This looks like the answer I've been in search of. Let me ensure it works and if so, I'll mark this as the answer so you get awarded the bounty. Thanks!

Stpete111 Over a year ago

Hi David, I just ran into one thing I didn't consider - I'm pretty sure your solution is going to work and I'll be awarding you the bounty no matter what - quick question though: let's say I create two different entity maps, TaskEntityMap1 and TaskEntityMap2; then say, in the csv file, if the first field of a given row is 1, use TaskEntityMap1 to map that row to SQL, if it's 2, use TaskEntityMap2. Both maps would map to the same SQL table, just going to different fields. Where would I handle that logic in the code when I'm using CSVHelper, since there is no for each loop involved?

David Specht Over a year ago

You would likely need to loop through each record so that you can check the first record for your mapping flag. Check out the documentation on Reading By Hand I would do a Read(), ReadHeader(), another Read(), then check your flag field by name or index. Register the particular ClassMap. Call GetRecord<TaskEntity> to add that record (instead of adding each individual property), then start your while(csv.Read()) loop to get the rest of the records. If you need more just start a new StackOverflow question.

Stpete111 Over a year ago

Hi David, thanks, that's a good point, I should have started a new question to begin with. I'll do that. It appears your answer above does in fact satisfy the original question so shortly I'll be marking it as answer now to award you the bounty. Thanks again!

Stpete111 Over a year ago

David, in case you're interested: stackoverflow.com/questions/58190364/…

Sreeram Nair · Accepted Answer · 2019-09-30 08:27:30Z

I have used the SqlBulkCopy along with the csvhelper to dump data in to the sql server.

SqlBulkCopy is an awesome utility that writes data to the sql server from nearly any data source that can be loaded into a DataTable instance.

var lines = File.ReadAllLines(file);
if (lines.Count() == 0) 
    return;

var tableName = GetTableName(file);
var columns = lines[0].Split(',').ToList();
var table = new DataTable();
sqlBulk.ColumnMappings.Clear();

foreach (var c in columns)
{
    table.Columns.Add(c);
    sqlBulk.ColumnMappings.Add(c, c); 
}

for (int i = 1; i < lines.Count() - 1; i++)
{
    var line = lines[i];
    // Explicitly mark empty values as null for SQL import to work
    var row = line.Split(',')
        .Select(a => string.IsNullOrEmpty(a) ? null : a).ToArray();
    table.Rows.Add(row);
}

sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(table);

Collectives™ on Stack Overflow

CsvHelper - write to SQL Database with mapping

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related