Import txt data into existing SQL Server table in .net using LINQ

Question

The txt file is of a specific form, it uses ';' as delimiter and has a specific number of columns. I also have a table that I created code-first with Entity Framework, which has the same number of columns.

So far I was able to import that kind of txt files to tables using "raw" SQL queries like BULK INSERT. But I am trying to learn how to do this from a web app using C# (or LINQ if needed).

I came across this solution from another question, but it seems that it creates a table named tbl, what I would like to do instead is to insert the data into an existing one.

public DataTable ConvertToDataTable (string filePath, int numberOfColumns)
{
    DataTable tbl = new DataTable();

    for(int col =0; col < numberOfColumns; col++)
        tbl.Columns.Add(new DataColumn("Column" + (col+1).ToString()));

    string[] lines = System.IO.File.ReadAllLines(filePath);

    foreach(string line in lines)
    {
        var cols = line.Split(':');

        DataRow dr = tbl.NewRow();

        for(int cIndex=0; cIndex < 3; cIndex++)
        {
           dr[cIndex] = cols[cIndex];
        }

        tbl.Rows.Add(dr);
    }

    return tbl;
}

This code just creates a `DataTable` object, it doesn't write anything to a database. You mention LINQ and Entity Framework, so presumably you already have some data context object and model? Can you just read that file into a list of that model and then add that list to the data context? — David, Oct 29 '17 at 11:33
Yes I already have a data context and the model. Should I first declare a list inside the model of my table? — Christos Karapapas, Oct 29 '17 at 11:52
I'm not sure what you mean. I would just read the file into a list of the object, then pass the list to `.AddRange()` on the corresponding data context DBSet. — David, Oct 29 '17 at 12:04

score 2 · Answer 1 · answered Oct 30 '17 at 08:17

First of all, my advise would be not to read the CSV file yourself. Use a NUGET CSV file serializer like CSVHelper

With CSVHelper you directly convert the lines into your destination type:

using (TextReader txtReader = new StreamReader(sourceFileName)
{
    csvReader = new CsvReader(txtReader)
    IEnumerable<MyClass> result = csvReader.GetRecords<MyClass>()
    // TODO: put result into database
}

One of the constructors of CsvReader takes a configuration object in which you can define your delimiter (":"); header rows; Comment lines; what to do with empty lines etc.

If you decide not to use CsvHelper you will need to convert your lines into MyClass objects:

IEnumerable<MyClass> ConvertTxtFile(string fileName)
{
    // TODO: checks to see if fileName is proper file
    IEnumerable<string> lines = System.IO.File.ReadAllLines(fileName);
    foreach(string line in lines)
    {
       yield return StringToMyClass(line);
    }
}
MyClass StringToMyClass(string line)
{
    // TODO: code to convert your line into a MyClass.
}

As you don't ask how to convert a line into a MyClass, I leave this to you.

After a while, you have a sequence of MyClass objects. Your question is how to add them to your database using Entity Framework and Linq

Well, that will be the easy part (once you've learned how to use entity framework).

Supposing your DbContext has a DbSet<MyClass>, representing a table of MyClass objects

IEnumerable<MyClass> readItems = ConvertTxtFile(fileName);
using (var dbContext = new MyDbContext())
{
    dbContext.MyClasses.AddRange(readItems.ToList());
    dbContext.SaveChanges();
}

Ok that is a lot of info, thanks for sharing! Let me study your answer and I will tell you if I managed to implement it in my case. — Christos Karapapas, Oct 30 '17 at 08:30

Import txt data into existing SQL Server table in .net using LINQ

1 Answers1