Very often, we will have requirements to develop a feature that allows users to export data to various text file formats like csv, tab-separated, pipe-separated, etc. In Asp.Net WebForms, we used to render the html generated by the grid control or render the data in specific format into the response and set ContentType property accordingly. This will enable the users to download the data into a specific file formats from the browser window. Since LINQ and Entity Framework are commonly used now, it will be better if we can generate these files from the model objects directly. In this article, let’s use LINQtoCSV package to create various Delimiter-Seperated files like csv, tab, pipe, etc. in a simpler way.
For this sample, we will create an Asp.Net MVC project from Visual Studio and use EntityFramework Code First for data-access. We will use a simple model with an Employee and Department class. On HomeController, let’s add action methods to download employee data in csv, tab-separated and pipe-delimited formats. 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
{
[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; }
}
Save CSV file to Disk
We will use LINQtoCSV, to save the entire data into csv file. The below action method will do that.
[HttpPost]
public ActionResult SaveAsCSV()
{
List<Employee> list = db.Employees.ToList();
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = ',',
FirstLineHasColumnNames = true
};
CsvContext csvContext = new CsvContext();
csvContext.Write<Employee>(list, Path.Combine(Server.MapPath("~"), "CSV", "Employees.csv"), csvFileDescription);
return View("GetAllEmployeeData", list);
}
Include LINQtoCSV namespace for this code to work.
The CsvFileDescription object takes configuration settings like separator character (comma for csv file) and other properties of the csv files. The CsvContext object will use this configuration settings to create the csv files form the passed in model. The above action method will create the csv file and save it to a folder called “CSV” in the application root folder. The generated CSV file will look like below,
Controlling Columns in CSV Files
Most often, we will not provide entire data from the model in the CSV files. For example, we may need to exclude the primary key values, combine 2 columns into one(FirstName and LastName as Name), get DepartmentName instead of DepartmentId. To do this, we can decorate the required columns with CsvColumn attribute, create read-only columns for combining column data and getting foreign key columns description in the Employee model.
public class Employee
{
//Required only for CSV
[CsvColumn(Name = "Name", FieldIndex = 1)]
public string Name { get { return FirstName + " " + LastName; } }
[CsvColumn(Name = "DepartmentName", FieldIndex = 2)]
public string DeptName { get { return Department.DepartmentName; } }
[CsvColumn(Name = "Address", FieldIndex = 3)]
public string Address { get { return Address1 + "," + Address2; } }
//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; }
[CsvColumn(Name = "City", FieldIndex = 4)]
public string City { get; set; }
[CsvColumn(Name = "State", FieldIndex = 5)]
public string State { get; set; }
[CsvColumn(Name = "Country", FieldIndex = 6)]
public string Country { get; set; }
[CsvColumn(Name = "ZipCode", FieldIndex = 7)]
public string PostalCode { get; set; }
public virtual Department Department { get; set; }
}
The FieldIndex in CsvColumn attribute will render the column at the specified index. We also need to set EnforceCsvColumnAttribute property to true to make the file generator to only consider the columns with CsvColumn attribute. The action method looks like,
[HttpPost]
public ActionResult SaveAsCSV()
{
List<Employee> list = db.Employees.ToList();
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = ',',
FirstLineHasColumnNames = true,
EnforceCsvColumnAttribute = true
};
CsvContext csvContext = new CsvContext();
csvContext.Write<Employee>(list, Path.Combine(Server.MapPath("~"), "CSV", "Employees.csv"), csvFileDescription);
return View("GetAllEmployeeData", list);
}
Once executed, the generated file will look like,
CSV file Download
Instead of saving the file to disk, we can also write the file into the response stream so the user can download the file. To do this,
[HttpPost]
public ActionResult DownloadAsCSV()
{
List<Employee> list = db.Employees.ToList();
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = ',',
FirstLineHasColumnNames = true,
EnforceCsvColumnAttribute = true
};
CsvContext csvContext = new CsvContext();
byte[] file = null;
using (MemoryStream memoryStream = new MemoryStream())
{
using (StreamWriter streamWriter = new StreamWriter(memoryStream))
{
csvContext.Write<Employee>(list, streamWriter, csvFileDescription);
streamWriter.Flush();
file = memoryStream.ToArray();
}
}
return File(file, "text/csv", "Employees.csv");
}
Include System.IO namespace for the above to code work.
To create other delimited files like tab, pipe we need to set the SeparatorChar property to the required character.
For tab-sepearated,
[HttpPost]
public ActionResult DownloadAsTab()
{
List<Employee> list = db.Employees.ToList();
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = '\t',
FirstLineHasColumnNames = true,
EnforceCsvColumnAttribute = true
};
CsvContext csvContext = new CsvContext();
byte[] file = null;
using (MemoryStream memoryStream = new MemoryStream())
{
using (StreamWriter streamWriter = new StreamWriter(memoryStream))
{
csvContext.Write<Employee>(list, streamWriter, csvFileDescription);
streamWriter.Flush();
file = memoryStream.ToArray();
}
}
return File(file, "text/csv", "Employees.txt");
}
For pipe-separated file,
[HttpPost]
public ActionResult DownloadAsPipe()
{
List<Employee> list = db.Employees.ToList();
CsvFileDescription csvFileDescription = new CsvFileDescription
{
SeparatorChar = '|',
FirstLineHasColumnNames = true,
EnforceCsvColumnAttribute = true
};
CsvContext csvContext = new CsvContext();
byte[] file = null;
using (MemoryStream memoryStream = new MemoryStream())
{
using (StreamWriter streamWriter = new StreamWriter(memoryStream))
{
csvContext.Write<Employee>(list, streamWriter, csvFileDescription);
streamWriter.Flush();
file = memoryStream.ToArray();
}
}
return File(file, "text/csv", "Employees.txt");
}
Download the source and see it in action!