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.
- 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.
- 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)
{
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.