Changing upload controller from supporting .xlsx to .csv

I have an MVC application which allows an admin user to upload 2 different excel file onto the system; the controller then creates a dataset with the excel data and then populates either a “Schools” or a “School2” database with the dataset using an SqlBulkCopy.

These uploads work perfect when I test them locally using IIS Express, although the same version deployed to AWS elastic beanstalk throws an error when I press the import button. As far as I am aware, this is due to my AWS RDS needing access to the OleDB provider jet drivers; something which I can not do because these drivers can not just be installed on an AWS RDS like they can be on an EC2 instance.

So my plan is to change my upload controller around to accept .csv files instead of excel files. This should solve my problem and allow my upload buttons to work after being deployed on AWS. Could someone help me/point me in the right direction to change my controller to support .csv instead of excel please?

Upload Controller:

namespace CampBookingSys.Controllers
{
    public class UploadController : Controller
    {
        SqlConnection con = new SqlConnection(@"Data Source=bookingdb.cwln7mwjvxdd.eu-west-1.rds.amazonaws.com,1433;Initial Catalog=modeldb;User ID=craig1990;Password=27Oct90!;Database=modeldb;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

        OleDbConnection Econ;

        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertExceldata(filepath, filename);
            return View();
        }

        [HttpPost]
        public ActionResult Index2(HttpPostedFileBase file)
        {
            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertExceldata2(filepath, filename);
            return RedirectToAction("Index");
        }

        private void ExcelConn(string filepath)
        {
            string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);
            Econ = new OleDbConnection(constr);
        }

        private void InsertExceldata(string filepath, string filename)
        {
            string fullpath = Server.MapPath("/excelfolder/") + filename;
            ExcelConn(fullpath);
            string query = string.Format("Select * from [{0}]", "Sheet1$");
            OleDbCommand Ecom = new OleDbCommand(query, Econ);
            Econ.Open();

            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);
            Econ.Close();
            oda.Fill(ds);

            DataTable dt = ds.Tables[0];

            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            objbulk.DestinationTableName = "dbo.Schools";
            objbulk.ColumnMappings.Add("AcademicYear", "AcademicYear");
            objbulk.ColumnMappings.Add("RollNumber", "RollNumber");
            objbulk.ColumnMappings.Add("OfficialSchoolName", "OfficialSchoolName");
            objbulk.ColumnMappings.Add("Address1", "Address1");
            objbulk.ColumnMappings.Add("Address2", "Address2");
            objbulk.ColumnMappings.Add("Address3", "Address3");
            objbulk.ColumnMappings.Add("Address4", "Address4");
            objbulk.ColumnMappings.Add("County", "County");
            objbulk.ColumnMappings.Add("Eircode", "Eircode");
            objbulk.ColumnMappings.Add("LocalAuthority", "LocalAuthority");
            objbulk.ColumnMappings.Add("X", "X");
            objbulk.ColumnMappings.Add("Y", "Y");
            objbulk.ColumnMappings.Add("ITMEast", "ITMEast");
            objbulk.ColumnMappings.Add("ITMNorth", "ITMNorth");
            objbulk.ColumnMappings.Add("Latitude", "Latitude");
            objbulk.ColumnMappings.Add("Longitude", "Longitude");

            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();
        }

        private void InsertExceldata2(string filepath, string filename)
        {
            string fullpath = Server.MapPath("/excelfolder/") + filename;
            ExcelConn(fullpath);
            string query = string.Format("Select * from [{0}]", "Sheet1$");
            OleDbCommand Ecom = new OleDbCommand(query, Econ);
            Econ.Open();

            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);
            Econ.Close();
            oda.Fill(ds);

            DataTable dt = ds.Tables[0];

            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            objbulk.DestinationTableName = "dbo.School2";
            objbulk.ColumnMappings.Add("RollNumber", "RollNumber");
            objbulk.ColumnMappings.Add("OfficialSchoolName", "OfficialSchoolName");
            objbulk.ColumnMappings.Add("Address1", "Address1");
            objbulk.ColumnMappings.Add("Address2", "Address2");
            objbulk.ColumnMappings.Add("Address3", "Address3");
            objbulk.ColumnMappings.Add("Address4", "Address4");
            objbulk.ColumnMappings.Add("County", "County");
            objbulk.ColumnMappings.Add("Eircode", "Eircode");
            objbulk.ColumnMappings.Add("PhoneNumber", "PhoneNumber");
            objbulk.ColumnMappings.Add("Email", "Email");
            objbulk.ColumnMappings.Add("PrincipalName", "PrincipalName");
            objbulk.ColumnMappings.Add("DeisSchool", "DeisSchool");
            objbulk.ColumnMappings.Add("SchoolGender", "SchoolGender");
            objbulk.ColumnMappings.Add("PupilAttendanceType", "PupilAttendanceType");
            objbulk.ColumnMappings.Add("IrishClassification", "IrishClassification");
            objbulk.ColumnMappings.Add("GaeltachtArea", "GaeltachtArea");
            objbulk.ColumnMappings.Add("FeePayingSchool", "FeePayingSchool");
            objbulk.ColumnMappings.Add("Religion", "Religion");
            objbulk.ColumnMappings.Add("OpenClosedStatus", "OpenClosedStatus");
            objbulk.ColumnMappings.Add("TotalGirls", "TotalGirls");
            objbulk.ColumnMappings.Add("TotalBoys", "TotalBoys");
            objbulk.ColumnMappings.Add("TotalPupils", "TotalPupils");

            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();
        }
    }
}

Answer

My first advice is to do bulk inserts in the DBMS, not in code. Doing them via code is only prone to add addtional issues.

As far as parsing the .xlsx files go, the OleDB driver is propably unessesary. There are a few basic rules for working with office formats:

if you can limit it to the new ones (.xlsx), you can use the OpenXML SDK. Or any of the Wrappers people made around it. Or even just the .ZipArchive and XMLReader classes.
if you need to support the old formats (.xls) too, you got to use the (t)rusty Office COM Interop. This has all the usual issue of COM Interop, and aditionally needs office installed and a Interactive session
for any given Display Technology and Problem, there might be a 3rd option. But those are few and far in between. As we always got the Interop to fall back on, we never developed a complete Office processing class like so many other languages have.

I would put OlebDB in that last category – a rare and very specific solution.

It always advise for using the first option.

And Officer COM Interop should be silently burried, with the old formats being removed from the file format options. Considering this is a WebApplication that will likely run as services, you will not get the nesseary interactive session anyway.

Of course accepting .csv is also an option. And indeed Excel has full .CSV support.

Advertisement

Answer