Insert into database from CSV file

• Download source code - 1.2 MB

Introduction

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 asp.net 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">
</asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="revCSV" runat="server" ErrorMessage="Upload .CSV File only"
     ValidationGroup="validate" Display="Dynamic" ValidationExpression="^.*\.(csv|CSV)$"
     ControlToValidate="flucsv">
</asp:RegularExpressionValidator>
<asp:Button ID="btnUpload" Text="Upload" runat="server" OnClick="UploadCsvDataToDatabase"
            CausesValidation="true" ValidationGroup="validate" />

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/vnd.ms-excel"
        || 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))
           {
             File.Delete(physicalTempFilePath);
           }
        inputFile.SaveAs(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();
     employeeList.Add(_user);
}

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)
   {
      _userBLL.CreateNewUser(item);
   }
}

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.

Advantages:

• 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.