Click here to Skip to main content
12,747,154 members (29,237 online)
Click here to Skip to main content
Add your own
alternative version


8 bookmarked
Posted 19 Feb 2013

Insert into database from CSV file

, 19 Feb 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
A simple way to import data from csv file to database


Most of the cases we get data from our client as a csv file. So we had to import data from the csv file to database. It has become a common practice that we need this functionality daily. So i decided to build a project that will do this task for me every time i need.

Using the code

As we want to insert the data from CSV to database so we will need to follow some steps to do so.  I have used Lumenworks as a third party to do this task. There is another third party tool that does the similar task as Lumenworks is GemBox. But the problem is GemBox does not support inserting data more than 150 rows at a time. So I decided to use Lumenwork.

Here they are:

Step 1: First we need to keep in mind that security issue comes first. So we need to validate the input file that we are putting a valid csv file. We will not perform any operations if the file format is not valid.

I want to solve this security issue at client side. So i attached a required field validator which has the "ControlToValidate" property pointing the file uploaded. To validate the file format i added a RegularExpressionValidator with ValidationExpression="^.*\.(csv|CSV)$". So now i am able to validate the file format of the uploaded file from client side. So we have completed our first criteria about security issue of file format.

Here is the sample code for client side validation for checking valid file format we want to accept.

<asp:FileUpload ID="flucsv" runat="server" />
<asp:RequiredFieldValidator ID="rfvCSV" runat="server" ErrorMessage="Please Select a file first"
     ValidationGroup="validate" Display="Dynamic" ControlToValidate="flucsv">

Step 2: We have validated the input file. Now we need to save it in the server. In my project i have separated the inserting task in to two.  

  1. First I will save the file in the database and if I am able to save the file successfully then my task is half done. 
  2. Secondly I will insert the data from the saved file and modify or add an extra column field if i need to do so. 

Here is code segment that checks if there is a file with the same name already exists on the server if so then delete the file and save the new one.  

private string DeleteAndSaveCsvFile(string physicalTempFilePath, FileUpload inputFile, string contentType)
    if (contentType == "application/"
        || contentType == "application/ms-excel"
        || contentType == "application/x-zip-compressed"
        || contentType == "application/octet-stream"
        || contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        || contentType == "text/csv")
        physicalTempFilePath = "~/CSV/" + Path.GetFileName(inputFile.FileName);
        physicalTempFilePath = Server.MapPath(physicalTempFilePath);
        if (File.Exists(physicalTempFilePath))
      return physicalTempFilePath;

There is a folder called "CSV" in the project folder to hold the uploaded the csv files temporally.  

Step 3: Our next task is to make a employeeList of type User. Here is the code segment to do this:

private void AddEmployeeToList(CsvReader csv, List<User> employeeList)
    User _user = new User();
    string value = string.Empty;
    value = csv[0];
    if (!string.IsNullOrEmpty(value))
         _user.EmployeeId = Convert.ToInt32(value);
         value = string.Empty;
    value = csv[1];
    if (!string.IsNullOrEmpty(value))
         _user.LastName = value;
         value = string.Empty;
    value = csv[2];
    if (!string.IsNullOrEmpty(value))
         _user.FirstName = value;
         value = string.Empty;
    value = csv[3];
    if (!string.IsNullOrEmpty(value))
         _user.Email = value;
         value = string.Empty;
     _user.Password = GeneratePassword();

I have generated a random password from GUID and I have add an extra column with the list object. Here is the code segment to do this task.  

private string GeneratePassword()
   string guidResult = string.Empty;
   while (guidResult.Length <= 6)
         // Get the GUID.
         guidResult += Guid.NewGuid().ToString().GetHashCode().ToString("x");
   return guidResult;

Step 4: And finally we will assign the list object to the UserBLL and insert into database one by one. Here is the code segment to do this:

private void InsertNewEmployee(List<User> employeeList)
   UserBLL _userBLL = new UserBLL();
   foreach (User item in employeeList)

How to Use the project:

Step 1: First download the project and after extracting open the project file in visual studio.

Step 2: Make a new database and named it “CSVTODATABASE” then create a table like the image provided below and save it as “User”. 


Step 3: Then clean the project and build it. Then run the project and you will be able to work with it now. Change the codes as with your requirement.  

Here is a sample input for CSV file. Follow the image to create one for yourself.


  • As far as I know there is no limitation of Lumenworks of handling data  like GemBox. Gembox does not support inserting data more that 150 rows at a time. Lumenworks does not have such limitation.  
  • Easy to handle and modify data before inserting.  
  • Like I have added an extra field as password that is not in the csv file but I needed to insert a random password for every user in the database with the corresponding csv field value. I found it easier to do with this. 

Happy coding.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer Cefalo
Bangladesh Bangladesh

I am Palash Debnath. I have been working on windows technologies since 2008. I started with ASP.NET. Then I moved to Windows Form and from the last year I have been working with Windows 8 app development. My future plan is to work with Windows 10 app development as well. I have completed my Undergraduate from Khulna University of Engineering in Computer Science & Engineering. Now I am working as a Software Engineer at Cefalo on Windows 8 app development.

You may also be interested in...

Comments and Discussions

QuestionI Vote 5 for this Excellent Article Pin
Member 1106137911-Sep-14 20:07
memberMember 1106137911-Sep-14 20:07 
GeneralMy vote of 5 Pin
Sk. Tajbir20-Feb-13 11:57
memberSk. Tajbir20-Feb-13 11:57 
GeneralRe: My vote of 5 Pin
dpalash20-Feb-13 19:33
memberdpalash20-Feb-13 19:33 
GeneralMy vote of 5 Pin
Guilherme Morais20-Feb-13 2:03
memberGuilherme Morais20-Feb-13 2:03 
GeneralRe: My vote of 5 Pin
dpalash20-Feb-13 4:24
memberdpalash20-Feb-13 4:24 
GeneralMy vote of 5 Pin
Md. Humayun Rashed19-Feb-13 20:42
memberMd. Humayun Rashed19-Feb-13 20:42 
GeneralRe: My vote of 5 Pin
dpalash19-Feb-13 23:19
memberdpalash19-Feb-13 23:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170215.1 | Last Updated 19 Feb 2013
Article Copyright 2013 by dpalash
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid