.NET MVC, Upload a CSV file to Database with Bulk upload

Simple yet useful tool set to have is dealing with CSV (Comma-separated values), often as not you end having to filter CSV files and then push them in to a database of some kind.

I thought I’d give a quick example of taking a CSV file, building a data table in memory and the pushing all the data to MS SQL Server in one action.

The following is by no means a finish code or ready for production, but here goes. 

The tools

• Microsoft Visual Studio Express 2010 (Download via Web platform installer)
• Microsoft SQL Express 2008
• Large cup of tea and couple of biscuits
• Example CSV file to work with. (CSV example file)
• Excel

I’m going to start with a standard vanilla MVC (Model View Controller) .NET application install, good old “File > New Project > ASP.NET MVC Web Application”

Now that’s done, lets look at our CSV file and understand what table structure we are going to have to create in the database.

The CSV file

The example CSV file contain a simple set of data, First Name (String), Surname (String) and Date of birth (DateTime).

To make life a little easier for ourselves we will delete the first line which contains the heading. It’s one less thing to deal with. Our CSV should now like this:

Arran,Maclean,01/12/1980
Rachel,Gorrod,20/12/1985
Simon,Gainham,14/05/1983
Bill,Gates,12/12/1960
Marky,Mark,15/02/1970


From this we can now look at creating a table in our database to capture the imported data. Again we will keep this simple, you can refine it later.

Now create your database. I’m keeping this example simple, just asking Visual Studio to create an empty database in the App_Data folder.

We will need 3 columns, “Firstname” nvarchar(50), “Surname” nvarchar(50) and “Dateofbirth” datetime.

CREATE TABLE BulkImportDetails(
[Firstname] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL,
[Dateofbirth] [datetime] NULL
)

Now to quickly drop in the connection string for the database in the the web.config

<connectionStrings>
<add name="DataBaseConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CSVtoBulkUpLoad.mdf;Integrated Security=True;User Instance=True"      providerName="System.Data.SqlClient" />
</connectionStrings>

OK, now that’s all out of the way, lets get to the fun stuff.

Upload your CSV file

As we are utilising MVC; We will create our form in a with a simple Html Helper. The important item to remember when uploading a file is enctype=”multipart/form-data” attribute, this deals with the bits and bytes. We will also add a ViewData holder for our feedback to the screen.

<h2>CSV Bulk Upload</h2>
<% using (Html.BeginForm("upload","",FormMethod.Post, new {enctype="multipart/form-data"})){ %>

<input type="file" name="FileUpload" />
<input type="submit" name="Submit" id="Submit" value="Upload" />

<%}    %>

<p><%= Html.Encode(ViewData["Feedback"]) %></p>

The HomeController

Now we have the inputs ready, it’s time to build our controller to handle the uploaded file and save it to a set location. Create a new folder in your App_Data folder called “uploads”

Lets add the code for to handle the upload process after the user click the upload button. Add the following code to the HomeController. (Upload load multiple files?)

[HttpPost]
public ActionResult Index(HttpPostedFileBase FileUpload)
{
if (FileUpload.ContentLength > 0)
{
           string fileName = Path.GetFileName(FileUpload.FileName);
           string path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
           try {
                FileUpload.SaveAs(path);
                ViewData["Feedback"] = "Upload Complete";
           }
           catch (Exception ex)
           {
                ViewData["Feedback"] = ex.Message;
           }
}

       return View("Index", ViewData["Feedback"]);
}

Time for a break

Great work everyone, the upload feature is working. Time for a cup of tea and a biscuit. MMMMmmm.

Lets crack on and finish.

Copying CSV to a DataTable

We are now going to convert our CSV file and return a DataTable . Think of this as Excel spread sheet in memory.

Let create our static function; One stumbling block you can come up against when working with CSV data is handling the “ , “ in a sentence.  Thanks to agreed conventions any sentence that contain a “,” should be wrapped in quotes. With this in mind we can use a regular expression to detect the comma’s and which one we need to split on.

private static DataTable ProcessCSV(string fileName)
{
//Set up our variables
string Feedback = string.Empty;
string line = string.Empty;
string[] strArray;
DataTable dt = new DataTable();
DataRow row;
// work out where we should split on comma, but not in a sentence
   Regex r = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
            //Set the filename in to our stream
            StreamReader sr = new StreamReader(fileName);

            //Read the first line and split the string at , with our regular expression in to an array
            line = sr.ReadLine();
           strArray = r.Split(line);

           //For each item in the new split array, dynamically builds our Data columns. Save us having to worry about it.
          Array.ForEach(strArray, s => dt.Columns.Add(new DataColumn()));

           //Read each line in the CVS file until it’s empty
            while ((line = sr.ReadLine()) != null)
           {
                      row = dt.NewRow();

                    //add our current value to our data row
                    row.ItemArray = r.Split(line);
                  dt.Rows.Add(row);
            }

            //Tidy Streameader up
            sr.Dispose();

            //return a the new DataTable
           return dt;

}

Lets amend our HomeController to now run our new function after upload.

[HttpPost]
        public ActionResult Index(HttpPostedFileBase FileUpload)
        {
            // Set up DataTable place holder
            DataTable dt = new DataTable();

            //check we have a file
            if (FileUpload.ContentLength > 0)
            {
                //Workout our file path
                string fileName = Path.GetFileName(FileUpload.FileName);
                string path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);

                //Try and upload
                try
                {
                    FileUpload.SaveAs(path);
                    //Process the CSV file and capture the results to our DataTable place holder
                    dt = ProcessCSV(path);

                }
                catch (Exception ex)
                {
                    //Catch errors
                    ViewData["Feedback"] = ex.Message;
                }
            }
            else
            {
                //Catch errors
                ViewData["Feedback"] = "Please select a file";
            }

            //Tidy up
            dt.Dispose();

            return View("Index", ViewData["Feedback"]);
        }

Now, if we run our web page and incept the DataTable (dt) we should now see our data in a nice little table.

Cool, feeling good so far. Lets move on and do what we really came here for! Bulk copy this table to our database table. The new function will take the DataTable generated above and send it all in one go to our database. You can of course sort, delete etc the data before sending it.

private static String ProcessBulkCopy(DataTable dt)
        {
            string Feedback = string.Empty;
            string connString = ConfigurationManager.ConnectionStrings["DataBaseConnectionString"].ConnectionString;

            //make our connection and dispose at the end
            using(  SqlConnection conn = new SqlConnection(connString))
            {
                //make our command and dispose at the end
                using (var copy = new SqlBulkCopy(conn))
                {

                        //Open our connection
                        conn.Open();

                        ///Set target table and tell the number of rows
                        copy.DestinationTableName = "BulkImportDetails";
                        copy.BatchSize = dt.Rows.Count;
                        try
                        {
                            //Send it to the server
                            copy.WriteToServer(dt);
                            Feedback = "Upload complete";
                        }
                        catch (Exception ex)
                        {
                            Feedback = ex.Message;
                        }
                }
            }

            return Feedback;
       }

Also most there, lets amend out HomeController yet again to now run our new bulk copy function.

[HttpPost]
        public ActionResult Index(HttpPostedFileBase FileUpload)
        {
            // Set up DataTable place holder
            DataTable dt = new DataTable();

            //check we have a file
            if (FileUpload.ContentLength > 0)
            {
                //Workout our file path
                string fileName = Path.GetFileName(FileUpload.FileName);
                string path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);

                //Try and upload
                try
                {
                    FileUpload.SaveAs(path);
                    //Process the CSV file and capture the results to our DataTable place holder
                    dt = ProcessCSV(path);

                    //Process the DataTable and capture the results to our SQL Bulk copy
                    ViewData["Feedback"] = ProcessBulkCopy(dt);
                }
                catch (Exception ex)
                {
                    //Catch errors
                    ViewData["Feedback"] = ex.Message;
                }
            }
            else
            {
                //Catch errors
                ViewData["Feedback"] = "Please select a file";
            }

            //Tidy up
            dt.Dispose();

            return View("Index", ViewData["Feedback"]);
        }

Finally

Well that’s it, all working. We have covered a number of topics here. File upload, Reading a CSV file and bulk copying data to a database.

Full project and database is available for download. (See right hand navigation)

If you found this helpful and would like to buy me a beer to say thanks, please

@ArranM