CODEWITHSUNDEEP
c#sqlentity-frameworklinq
Christos Karapapas
Christos Karapapas

Reputation: 1086

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

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;
}

Upvotes: 0

Views: 1133

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

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();
}

Upvotes: 2

Related Questions