Upload .csv file in MVC application using ajax

5.00 avg. rating (97% score) - 1 vote

Introduction

CSV(Comma Separated Values), which means the values are separated by commas. Usually in applications, there is a requirement to upload .csv files only through the HTML uploader and retrieve the values and save to the database. Lets explore how and understand each step.
Below image shows how a .csv file looks like:-

The first row in the document image depicts the column headers. The below rows are the values for the headers which we will be retrieving and saving into the database. Remember we will also need to delete the first and the last rows as they would not be saved into the database, infact they cannot be. 😀

Get started

To start with this we need to have a sample .csv file to test. I would be using the sample as mentioned in the image. First of all we need to create a view model for holding the values while looping through the file data. Lets have a look how our viewmodel 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;}
}

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 which will hold the values for each row in the .csv file which we will be retrieving running through a loop.
But before that we need to fetch the file and then send to the server for processing. We will be doing this through 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");
                }
            });
        }
    }
});

$("#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");

}

});

}

});

Lets 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. Below 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);

    });

$("#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 matches .csv then add the file path text into the text box. Then after that when the user hits/clicks Submit, then the “.click” function works out. At first we get the fileUploadId, then we check if the fileUpload value is not null. It automatically treats it as file as it includes already the input HTML element of type File. Then we declare a variable of type FormData, which will contain the entire file and the details. Then we get the files using the fileUploadId. If the files exist which 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 passed in the ajax call. We send the data as the same name as uploadFile(of type FormData()).

After this the file is sent to the server where we read though the file using the InputStream. Lets 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(newModel);// newModel needs to be an object of type ContextTables.
				context.TableContext.Add(uploadModelRecord);
	      }
			context.SaveChanges();
	  }
            return Json(null);
        }

/// <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(newModel);// newModel needs to be an object of type ContextTables.

context.TableContext.Add(uploadModelRecord);

}

context.SaveChanges();

}

return Json(null);

}

The above is the action method where the server side operation runs. Pardon me for using context object inside the controller, this is just for the demo, please “Do Not Add context in controller”. Now look at the above snippet for the attachedFile, this now contains the file which we had sent as data from ajax call and accessed through, 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 which is of type StreamReader() which takes the attachedFile’s InputStream as parameter. Then we declare a string variable which will have the 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 object. 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 which have been retrieved and saved into the vaues object. Thus we remove the 0th element and the Count – 1 value(row) and then we work on our creation of the viewModel and then save into the database table.

Conclusion

This is a very simple and handy code snippet article which may come into work at any moment of the application. There are a lot of records which can be retrieved and manipulated using this snippet and either mail or create a viewmodel and save as we did above. Thus when we hear it may seem hard and complex but it is that simple, as without saving/uploading the file into the File System we are directly readig 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.