How to read csv file and import its data with Linq

Introduction

I was recently tasked with the project of reading a CSV file and importing its data to SQL Server database. This may sound very common and I believe there are lot of traditional methods to achieve this.As always the time was a big constraint , I wanted some quick solution . I could not recommend SSIS packages as customer was using SQL Server Express Edition.

Scenario

You are given a CSV file with the "," as delimiter of the file. There are three fields in the file

  1. Customer Id
  2. Customer Name
  3. City

The first line of the file has the column headings, this line should not be imported in the table. You have the table called Customer in the database which has the same columns. You are required to import the csv file data into this table. Sounds pretty easy, you must be thinking of reading the file loop through its contents and add the each line record in the table using DML statements.C’mon guys I will show you a better and improved way of doing the same without any loops…, So your eyes are open wide, without loop how is that possible. Well Linq will help us to achieve this fete.

Solution

I knew that File class function ReadAllLines method returns array of string.And there is the catch whatever that implements IEnumerable can be queried with Linq. As I needed to store the csv data into the table. I had already added Linq to SQL class in my solution. So let us see some code.

Dim dataImport = From line As String In File.ReadAllLines("YourFileNamewithFullPath") _
                         Skip 1 _
                         Let CR = line.Split(",") _
                         Select New TblCustomer With {.CustomerId = CR(0), _
                                                 .CustomerName = CR(1), _
                                                 .City = CR(2)}

The above code reads the array of lines using Linq syntax and store each line in CR variable by splitting the line contents with delimiter using Let statement. This record is then stored in constructor of the TblCustomer, which is the Linq to SQL Class for Customer table in the database.Notice that I am using Skip clause for not to read the file header, which are actual the column names in the csv file.So now your dataImport variable contains all the data of the csv file excluding the header. Now this data needs to be imported into the Customer table in the database. Let us have look at the code for the same.

Ctx.TblCustomer.InsertAllOnSubmit(dataImport)

Ctx.SubmitChanges()

Here Ctx is the object variable of Linq Data Context class. The InsertAllOnSubmit method will do bulk insert of the data provided to it as a collection object in its parameter. So that’s it with just two lines of the code I have imported data into my table. Kudos to Linq

Closure

I always like to do the old things in newer way and this was the classic example of it. I hope you must be agreeing with my thoughts, use Technology to best of its advantage otherwise don’t use. Happy coding…

Advertisements

2 thoughts on “How to read csv file and import its data with Linq

  1. Hello,

    Can we make it more generic, e.g. I have five CSV files and each have different number of columns and I dont know which column is at what place in file. Is there any way by which it will run for all the five files.

    • Hi,
      As per my understanding the concept of CSV files , the developer is already aware of the delimiter and the index of each columns in the file. If you don’t have this is preliminary info how would you map the csv columns with your table columns. If you have this info before hand than you can program it with multiple files.

      Thanks
      Goldy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s