My previous article Create CSV, Tab-Separated and Other Delimiter-Separated Files in Asp.Net MVC discussed about exporting data into CSV and other text file formats from an Asp.Net MVC application. At times, we also need to import data from a Demiliter-Separated text files into a Sql Server table from our application. The LINQtoCSV package is capable of doing both creating and importing data into a database table. Moving forward, let’s see how to do upload data from a delimiter-separated text files into a table using Entity Framework Code First.
For this sample, we will create an Asp.Net MVC project using Visual Studio. We will also create a simple data model with an Employee and Department class. On HomeController, let’s add action methods to upload employee data from csv, tab-separated and pipe-delimited files into Sql Server table. Source code is attached in this article.
First, let’s include the LINQToCSV Nuget package into our project. To do this, go to Nuget package manager(Right click project and select “Manage Nuget Packages..”) and search “LINQtoCSV”. Click Install button to download and install package.
Below are the Employee and Department objects for reference,
public class Employee
{
//Table properties
[Key]
public int EmployeeId { get; set; }
public int DepartmentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address1 { get; set; }
public string Address2 { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string PostalCode { get; set; }
public virtual Department Department { get; set; }
}
public class Department
{
[Key]
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
public virtual ICollection<Employee> Employees { get; set; }
}
Import CSV File
Let’s first create a sample csv file with all the column values for our upload similar to below. I have not included EmployeeId since it is an auto generated column.
To upload the file, we add a POST action method under HomeController and get the uploaded file stream. Similar to creating csv file, there is a method in CsvContext class to read (CsvContext.Read<T>( StreamReader, CsvFileDescription)) the data from a file. The csv file description should be passed in this method with appropriate file properties for the LINQToCsv component to read and deserialize into List<Employee> object. Code below,
Controller:
[HttpPost]
public ActionResult UploadCsv(HttpPostedFileBase attachmentcsv)
{
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = ',',
FirstLineHasColumnNames = true
};
CsvContext csvContext = new CsvContext();
StreamReader streamReader = new StreamReader(attachmentcsv.InputStream);
IEnumerable<Employee> list = csvContext.Read<Employee>(streamReader, csvFileDescription);
db.Employees.AddRange(list);
db.SaveChanges();
return Redirect("GetAllEmployeeData");
}
View:
<div class="col-md-12">
@using (Html.BeginForm("UploadCsv", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="form-group">
<label for="attachment">Select a Csv File</label>
<label class="btn btn-default btn-file">
<input type="file" name="attachmentcsv" id="attachmentcsv" hidden>
</label>
</div>
<button type="submit" class="btn btn-primary">Upload</button>
}
</div>
The above code will deserialize the uploaded file data into IEnumerable<Employee> object and saves it to database once db.SaveChanges() is called. It finally redirects the user to action method GetAllEmployeeData that list all the employees in the table.
Note – I have used Grid.Mvc package to generate the above grid. Refer here to know more about Grid.Mvc package.
As you would have guessed, to upload other de-limited files we just need the set the de-limiter character to SeparatorChar property of CsvFileDescription in the above code.
The below action method will upload a pipe-separated file when called.
[HttpPost]
public ActionResult UploadPipe(HttpPostedFileBase attachmentpipe)
{
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = '|',
FirstLineHasColumnNames = true
};
CsvContext csvContext = new CsvContext();
StreamReader streamReader = new StreamReader(attachmentpipe.InputStream);
IEnumerable<Employee> list = csvContext.Read<Employee>(streamReader, csvFileDescription);
db.Employees.AddRange(list);
db.SaveChanges();
return Redirect("GetAllEmployeeData");
}
Download the source and see it in action! The source includes sample text files for uploading.