How to read csv file and import its data with Linq

Goldytech
Posted by in .NET Framework category on for Beginner level | Views : 20848 red flag

In this post I teach you how to read csv file using linq and imports its data in table
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...
Page copy protected against web site content infringement by Copyscape

About the Author

Goldytech
Full Name: Muhammad Afzal Qureshi
Member Level: Bronze
Member Status: Member
Member Since: 8/4/2009 10:58:17 PM
Country: India

http://goldytech.wordpress.com
Hello Everyone Myself Muhammad Afzal , aka GoldyTech. Thats my pen name. I reside in India and work as a solution Architect on .NET platform. I hope you must have enjoyed reading my blog. Please leave your comments or suggestions good or bad and help me to improve

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)