Comma Separated Value (CSV) in MVC Using Ajax

Introduction

Comma Separated Value (CSV) is a format for storing data with values separated by commas. Usually, in applications, there is a requirement to upload CSV files only using the HTML uploader to retrieve the values and save them to the database. Let's explore how and understand each step. The following image shows what a CSV file looks like.

The first row in the document image depicts the column headers. The following rows are the values for the headers that we will be retrieving and saving into the database. Remember, we will also need to delete the first and the last rows since they would not be saved into the database, and in fact, they cannot be.

Get started

To start with this we need to have a sample CSV file to test. I will be using the sample as specified in the image. First of all, we need to create a view model for holding the values while looping through the file data. Let's have a look at what our view model would look like.

public class CsvRecordsViewModel
{
    public string Name { get; set; }
    public string School { get; set; }
    public int Age { get; set; }
    public string DOB { get; set; }
    public string ParentName { get; set; }
}

This is the view model that will hold the values for each row in the CSV file that we will be retrieving by running through a loop. But before that, we need to fetch the file and then send it to the server for processing. We will be doing this using Ajax.

$("#submitBox").click(function() {
    var fileUpload = document.getElementById("IDofTheUploader");
    if (fileUpload.value != null) {
        var uploadFile = new FormData();
        var files = $("#IDofTheUploader").get(0).files;
        
        // Add the uploaded file content to the form data collection
        if (files.length > 0) {
            uploadFile.append("CsvDoc", files[0]);
            $.ajax({
                url: "/Controller/Action",
                contentType: false,
                processData: false,
                data: uploadFile,
                type: 'POST',
                success: function() {
                    alert("Successfully Added & processed");
                }
            });
        }
    }
});

Let's understand what happens inside the snippet mentioned above. When the user selects a file from the browse window, he needs to select a CSV file, nothing other than that. For that, we need to give a check both at the client level and the server level. The following snippet shows how to restrict the user from uploading some other extension file.

$("#IDofTheUploader").change(function () {
    var selectedText = $("#IDofTheUploader").val();
    var extension = selectedText.split('.');
    if (extension[1] !== "csv") {
        $("#IdofTheTextBoxUpload").focus();
        alert("Please choose a .csv file");
        return;
    }
    $("#IdofTheTextBoxUpload").val(selectedText);
});

Thus, if a user tries to upload some other extension file, he gets an alert saying Please choose a CSV file. As you can see we have checked based on the upload ID input change function and the last line, if the file extension is .csv then add the file path text into the text box. Then after that when the user hits/clicks Submit, the ".click" function executes. At first, we get the fileUploadId, then we check if the fileUpload value is not null. It automatically treats it as a file since it includes already the input HTML element of type File. Then we declare a variable of type FormData that will contain the entire file and the details. Then we get the files using the fileUploadId. If the files exist that we check from the length, then the file is appended into the FormData type variable declared earlier, then we make the Ajax call to the server. Just keep in mind to add the ProcessData and the contentType to be ed in the Ajax call. We send the data with the same name as uploadFile (of type FormData()). Then the file is sent to the server where we read through the file using the InputStream. Let's first have a look at the snippet.

/// <summary>
/// Controller method to validate the CSV document before upload
/// </summary>
/// <returns></returns>
public ActionResult UploadCsvFile()
{
    var attachedFile = System.Web.HttpContext.Current.Request.Files["CsvDoc"];
    if (attachedFile == null || attachedFile.ContentLength <= 0)
        return Json(null);

    var csvReader = new StreamReader(attachedFile.InputStream);
    var uploadModelList = new List<CsvRecordsViewModel>();
    string inputDataRead;
    var values = new List<string>();

    while ((inputDataRead = csvReader.ReadLine()) != null)
    {
        values.Add(inputDataRead.Trim().Replace(" ", "").Replace(",", " "));
    }

    values.Remove(values[0]);
    values.Remove(values[values.Count - 1]);

    using (var context = new Entities())
    {
        foreach (var value in values)
        {
            var uploadModelRecord = new CsvRecordsViewModel();
            var eachValue = value.Split(' ');
            uploadModelRecord.Name = eachValue[0] != "" ? eachValue[0] : string.Empty;
            uploadModelRecord.School = eachValue[1] != "" ? eachValue[1] : string.Empty;
            uploadModelRecord.Age = eachValue[2] != "" ? eachValue[2] : string.Empty;
            uploadModelRecord.DOB = eachValue[3] != "" ? eachValue[3] : string.Empty;
            uploadModelRecord.ParentName = eachValue[4] != "" ? eachValue[4] : string.Empty;
            uploadModelList.Add(uploadModelRecord); // newModel needs to be an object of type ContextTables.
            context.TableContext.Add(uploadModelRecord);
        }
        context.SaveChanges();
    }

    return Json(null);
}

The preceding is the action method where the server-side operation runs. Pardon me for using a context object inside the controller, this is just for the demo, please "Do Not Add context to the controller". Now look at the preceding snippet for the attached file, this now contains the file that we had sent as data from the Ajax call and accessed using the current request in the context using the same name as the FormData() variable append Name. Then we create a new object for the CSV Reader that is of type StreamReader() that takes the attached file's InputStream as a parameter. Then we declare a string variable that will have each row or line read from the CSV file using the csvReader object ReadLine() property. If that is not null then we manipulate the string row and then add each row now to a list of string type objects. We replace the spaces with empty and the comma with spaces so that it will be easy to split and start extracting the values. Then the most important task is to remove the first and last row in the CSV file that has been retrieved and saved into the values object. Thus we remove the 0th element and the Count - 1 value (row) and then we work on our creation of the viewModel and save that into the database table.

Conclusion

This is a very simple and handy code snippet article that might be useful at any moment of the application. There are many records that can be retrieved and manipulated using this snippet and either mail or create a model and save as we did above. Thus when we hear it may seem hard and complex but it is that simple, since without saving/uploading the file into the File System we are directly reading the file at runtime. I hope this becomes handy for all. I am not an expert, so suggestions and corrections are humbly welcome. Thanks for your patience.