Import multiple Excel Files into SQL Server in ASP.Net MVC

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Hi,

I have about 3 excel files. I need to import multiple excel files data from each excel into tables (as the same columns as the table). My code only imports one excel. How to import multiple excel files?  I have read many questions but none works. Please someone help me!

How to upload multiple excel files To SQL Server Database Using MVC ? 

    public class HomeController : Controller
    {
        // GET: /Home/
        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        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();
        } 

        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 fileepath,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 = "tbl_registration";
            objbulk.ColumnMappings.Add("Email", "Email");
            objbulk.ColumnMappings.Add("Password", "Password");
            objbulk.ColumnMappings.Add("Name", "Name");
            objbulk.ColumnMappings.Add("Address", "Address");
            objbulk.ColumnMappings.Add("City", "City");
            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();
        }
    }

 

Posted 5 months ago

Hi Ayush,

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

You can download the database table SQL by clicking the download link below.

Download SQL file

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View(new DataTable());
    }

    [HttpPost]
    public ActionResult Index(HttpPostedFileBase[] postedFile)
    {
        DataTable dt = GetDataTableFromExcel(postedFile);
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Constring"].ConnectionString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                sqlBulkCopy.DestinationTableName = "dbo.Customers";
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                con.Open();
                sqlBulkCopy.WriteToServer(dt);
                con.Close();
            }
        }

        return View(dt);
    }

    private DataTable GetDataTableFromExcel(HttpPostedFileBase[] postedFiles)
    {
        DataTable dt = new DataTable();
        for (int j = 0; j < postedFiles.Length; j++)
        {
            HttpPostedFileBase postedFile = postedFiles[j];
            string filePath = string.Empty;
            string path = Server.MapPath("~/Uploads/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            filePath = path + Path.GetFileName(postedFile.FileName);
            string extension = Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(filePath);

            string conString = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03.
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 and above.
                    conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }
            conString = string.Format(conString, filePath);
            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;
                        connExcel.Open();
                        DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        connExcel.Close();
                        string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        connExcel.Open();
                        cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                        odaExcel.SelectCommand = cmdExcel;
                        odaExcel.Fill(dt);
                        connExcel.Close();
                    }
                }
            }

            Directory.Delete(path, true);
        }

        return dt;
    }
}

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<DataTable>" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>
</head>
<body>
    <div>
        <% using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
           {%>
        <input type="file" name="postedFile" multiple required />
        <input type="submit" value="Import" />
        <hr />
        <table>
            <tr>
                <% foreach (DataColumn column in Model.Columns) {%>
                <th><%=column.ColumnName%></th>
                <% }%>
            </tr>
            <% foreach (DataRow row in Model.Rows) {%>
            <tr>
                <%foreach (DataColumn column in Model.Columns) {%>
                <td><%=row[column.ColumnName]%></td>
                <%} %>
            </tr>
            <% }%>
        </table>
        <% } %>
    </div>
</body>
</html>

Screenshots

The Excel files

The Form

Database after Insert