Simple CSV with MVC4

2/21/2014
In ASP.Net

In this article I am going to show a very easy way to use Comma Separated Value (CSV) files with Microsoft’s MVC4 web framework. I’ll show how to return CSV formatted files from both regular controllers as well as API controllers that are used with Web API. I’ll also demonstrate how to read in data from a CSV file used as a data source.

The code is this example is actually used to generate sample vehicle data. You can try it out at the Sample Vehicle Generator website.

Scenario

I needed to create some sample vehicle data for use in some training sessions. I obtained raw vehicle data at a very reasonable price from Cheapest Auto Database. Data was provided in a variety of formats, including Comma Separated Value (CSV) format, which is what I decided to use. The following is a sample of data provided.

"23297"",Kia,Soul","2012","4dr Wagon (1.6L 4cyl 6M)"
"23298","Kia,Borrego","2009","EX 4dr SUV 4WD (4.6L 8cyl 6A)"
"23299","Kia,Borrego","2009","EX 4dr SUV (4.6L 8cyl 6A)"
"23314","Kia,Rondo","2008","EX 4dr Wagon (2.4L 4cyl 4A)"

I initially wrote my own data parsing engine. However, writing a CSV parser/formatter is a problem that has been solved many times. I discovered the FileHelpers Library, written and maintained primarily by Marcos Meli. With a LPGL license, it is free for commercial and non-commercial use. There is a NuGet Package available to add it to your projects.

Reading CSV Data

In an MVC4 project you can place raw data source in the App_Data folder. Create a new MVC4 project, add the App_Data folder to the project, and place the CSV formatted source file in that folder. Then add the FileHelpers NuGet Library to the project.

To read the raw data, you need to do two things. First, create a class marked with appropriate attributes that models the structure of the data in the file. Create this class in the MVC4 project’s Models folder. You’ll need to add ‘using FileHelpers;’ to the top of your file. Here is an example of the VehicleDef class that I wrote to model my CSV data file.

[DelimitedRecord(",")]
public class VehicleDef
{
	[FieldQuoted()]
	public int ID;

	[FieldQuoted()]
	public string Make;

	[FieldQuoted()]
	public string Model;

	[FieldQuoted()]
	public int Year;

	[FieldQuoted()]
	public string TrimLevel;
}

The idea, of course, is to define fields in the class in the appropriate order that matches the data in the CSV source file. The DelimitedRecord attribute on the class definition defines the field separator in the data file. The FieldQuoted attribute tells the FileHelpers parser engine how the data is formatted in the data file (in this case, each field is surrounded by double quote characters).

The second requirement is to add a new class that has the responsibility to read the data into some kind of array or collection. I chose to create a static class called VehicleManager with a static property called AllVehicles that would allow access to the data. I created a folder called Code in my MVC4 project and put my utility classes in this location. Here is an example of the code I needed to make this work. Using statements at the top of the code referenced FileHelpers and {Project}.Models namespaces.

public static class VehicleManager
{
	private static IList<VehicleDef> _vehicles = null;
	
	public static IList<VehicleDef> AllVehicles
	{
		get { return _vehicles; }
	}
	
	static VehicleManager() 
	{
		FileHelperEngine vInputEngine = new FileHelperEngine(typeof(VehicleDef));
		_vehicles = vInputEngine.ReadFile(HostingEnvironment.MapPath("~/App_Data/makemodel.csv")) as VehicleDef[];
	}
}

In my actual project, there is quite a bit more code in this class. I added code to take a random sample of vehicles from the raw data, and generate some sample mileage and pricing data for each sample vehicle. That code is not included here.

Generating CSV Data from Regular MVC4 Controllers

When you go to the default page of the Sample Vehicle Generator website, a form is generated allowing you to specify how many vehicles you want in the generated vehicle sample. When the form is posted back to the controller, it targets an action that generates the CSV output file.

To generate CSV data, you again need two pieces of code. First, you need a class definition that contains all the fields that you want supplied in the CSV output file. I created a Vehicle class in the Models folder that looks like the following. You’ll need to reference the FileHelpers namespace via a using statement.

[DelimitedRecord(",")]
public class Vehicle
{       
	[FieldQuoted()]
	public string VIN;
   
	public int Mileage;
	
	public int Year;
	
	[FieldQuoted()]
	public string Make;

	[FieldQuoted()]
	public string Model;

	[FieldConverter(typeof(MoneyConverter))]
	public decimal SuggestedWholesale;

	[FieldConverter(typeof(MoneyConverter))]
	public decimal SuggestedRetail;

	public float PremiumFactor;

	[FieldQuoted()]
	public string TrimLevel;

	[FieldQuoted()]
	public string ProblemDescription;
}

Notable are the following attributes: DelimitedRecord on the class, and FieldQuoted and FieldConverter attributes on the individual fields. FieldConverter, which we haven’t encountered before, allows you to write a class where you can have better control over data formatting. It’s optional, but I wanted it in my case because, when I generated SuggestedRetail and SuggestedWholesale pricing, I used some randomizing elements that didn’t round the raw data to 2 places past the decimal. I wanted to clean up the monetary formatting to look like a dollar.cents value in the output data file. Here is the definition of the MoneyConverter class, which I placed in my project’s Code folder.

public class MoneyConverter: ConverterBase
{
	public override object StringToField(string from)
	{
		decimal amount;
		if (!decimal.TryParse(from, out amount))
		{
			amount = 0m;
		}
		return amount;
	}

	public override string FieldToString(object from)
	{
		decimal amount = Convert.ToDecimal(from);
		return amount.ToString("#.##");
	}
}

Note the class is derived from a ConverterBase class that is found in the FileHelpers namespace.

The second required piece of code is the code that generates and formats the file for output. I placed this code in my HomeController, in the Index action method that was the target of the form postback. Here is an example of that code.

class HomeController: Controllers
{
	//other action methods
	
	[HttpPost]
	public ActionResult Index(int numberOfVehicles) 
	{
		if (numberOfVehicles > 250) numberOfVehicles = 250;

		var vehicles = VehicleManager.GetRandomVehicles(numberOfVehicles);

		FileHelperEngine engine = new FileHelperEngine(typeof(Vehicle));
		engine.HeaderText = ""VIN",Mileage,Year,"Make","Model",SuggestedWholesale,SuggestedRetail,PremiumFactor,"TrimLevel","Problem"";

		return new FileContentResult(engine.WriteString(vehicles).GetBytes(), "application/csv")
		{
			FileDownloadName = "VehicleData.csv"
		};
	}
}

I didn’t include the definition of VehicleManager.GetRandomVehicles() method in the details of the VehicleManager class above, it’s beyond the scope of this discussion. What is notable is that FileHelperEngine is instantiated and passed the datatype of the Vehicle class definition, which is how the FileHelperEngine knows how to format the output. After the engine is created, I populated the HeaderText property with a string that contains the first line headings of the generated CSV file.

The trick to sending a formatted CSV file back to to the user is to return a new FileContentResult object. The constructor takes an array of bytes which contains the file content as the first parameter, and the mime type as the second parameter. Then, an additional property named FileDownloadName is populated for convenience. It becomes the default file name for the user if the user chooses to write the data to file on their local machine.

There is a simple technique documented here to change a string type to an array of bytes, which is the datatype required by the first parameter on the FileContentResult constructor. I created an extension method named GetBytes(), as shown below. I used this extension method to change the CSV string generated by the FileHelpers engine to a byte array required by the FileContentResult constructor. The class was placed in my project’s Code folder.

public static class Extensions
{
	public static byte[] GetBytes(this string s)
	{
		byte[] bytes = new byte[s.Length * sizeof(char)];
		Buffer.BlockCopy(s.ToCharArray(), 0, bytes, 0, bytes.Length);
		return bytes;
	}
}

In summary, it’s pretty easy to return CSV formatted data via a regular MVC4 controller. You need a class to model the format of the data being returned in the CSV file (the Vehicle class in my case), and you need to return an instance of FileContentResult in your controller’s action method. The MoneyConverter was an optional class to nicely format monetary amounts, and the GetBytes extension method was used to make it easy to convert from any string to a byte array. The FileHelpers engine does the hard work of generating the CSV formatted data.

Generating CSV Data for Web API Controllers

Web API works a bit differently. To generate a CSV file output as a result of a call to a Web API service, you need to write a formatter class. I’ll show you the code and then run through it to describe what it is doing.

public class CSVMediaTypeFormatter : BufferedMediaTypeFormatter
{
	//Constructor
	public CSVMediaTypeFormatter()
	{
		SupportedMediaTypes.Add(new MediaTypeHeaderValue("application/csv"));
		SupportedMediaTypes.Add(new MediaTypeHeaderValue("text/csv"));
	}

	public override bool CanReadType(Type type)
	{
		return false;
	}

	public override bool CanWriteType(Type type)
	{
		return typeof(IEnumerable<Vehicle>).IsAssignableFrom(type);
	}

	public override void WriteToStream(Type type, 
	                                   object value, 
	                                   System.IO.Stream writeStream, 
	                                   System.Net.Http.HttpContent content)
	{
		IEnumerable<Vehicle> vehicles = (IEnumerable<Vehicle>)value;

		FileHelperEngine engine = new FileHelperEngine(typeof(Vehicle));
		engine.HeaderText = ""VIN",Mileage,Year,"Make","Model",SuggestedWholesale,SuggestedRetail,PremiumFactor,"TrimLevel","Problem"";
		using (var writer = new StreamWriter(writeStream))
		{
			writer.Write(engine.WriteString(vehicles));
		}
	}

	public override void SetDefaultContentHeaders(Type type, 
	                                              System.Net.Http.Headers.HttpContentHeaders headers,
	                                              System.Net.Http.Headers.MediaTypeHeaderValue mediaType)
	{
		base.SetDefaultContentHeaders(type, headers, mediaType);
		headers.Add("Content-Disposition", "attachment; filename=vehicles.csv");
	}
}

You can write your formatter to derive from either MediaTypeFormatter or BufferedMediaTypeFormatter. The latter is easier because it doesn’t require any asynchronous code, so that’s the one I used. The downside is, of course, some performance limitations for heavily used websites.

In the constructor of the class, you need to tell the Web API framework what required media types will prompt the controller to format the data as CSV. I choose to target two mime types, “application/csv” and “text/csv”. Requests against my Web API service will generate CSV data automatically if that mime type is included in the request. The nice thing about this system is that the other data formats, JSON and XML, are still fully supported if the request specifies their mime types.

There are two methods you need to define in your formatter, CanReadType and CanWriteType to let the framework know if it is OK to read and/or write CSV formatted data for a particular datatype. You can see I am returning false in CanReadType because I am not accepting data posted in CSV format. However, in the CanWriteType method I am indicating that I can format CSV data if the datatype implements IEnumerable<Vehicle>.

There is also a WriteToStream method. The code in this method should look similar to the code in the HomeController’s Index method above. The only difference is that I used a StreamWriter to write the CSV formatted string from the FileHelper engine, I didn’t have to convert to an array of bytes.

Finally, if you want to supply a default filename for the user, you can do that by overriding a method called SetDefaultContentHeaders as shown. This is optional, provided as a convenience for the user.

There are two things left to do: register your formatter, then create a Controller that inherits from ApiController to serve requests.

I registered the CSVMediaTypeFormatter class in the existing WebApiConfig.cs file in the App_Start folder. I added the following line of code in the Register method of the class (before the MapHttpRoute method call).

config.Formatters.Add(new CSVMediaTypeFormatter());

Finally, to use all of the CSV goodness with Web API, create a new controller that inherits from ApiController. I created a class called VehiclesController, as shown here.

public class VehiclesController : ApiController
{
	public IEnumerable Get()
	{
		return VehicleManager.GetRandomVehicles(10);
	}

	public IEnumerable Get(int id)
	{
		if (id  250) id = 250;
		return VehicleManager.GetRandomVehicles(id);
	}
}

Again, like we saw with the regular MVC4 controllers, there isn’t too much code here. The biggest challenge is writing the formatter class, with the key methods CanReadType, CanWriteType and WriteToStream. You need to register the created formatter in your application’s startup code, and finally create a controller that will service the data via HTTP requests.

Conclusion

Microsoft’s MVC web framework is a great foundation for creating websites. This article shows how easy it is to extend and add capabilities to the base code supplied by Microsoft. If you’d like to learn more about writing MVC4 websites, we’d love to show you more in our MVC4 course. You can learn quite a bit more details about creating and deploying web services, including Web API services, in our Web Services course. Come and join us to further your career!