none
Import Excel data into SQL table (C#) Invalid Path RRS feed

  • Question

  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.IO;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using System.Configuration;
    
    public partial class Import: System.Web.UI.Page
    {
        protected void Upload(object sender, EventArgs e)
        {
            //Upload and save the file
            string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            //FileUpload1.SaveAs(excelPath);
            
            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                    break;
            }
    
            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["dbo.tblDSR_VisaSale"].ToString();
                DataTable dtExcelData = new DataTable();
    
                //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                //dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                //      new DataColumn("Name", typeof(string)),
                //      new DataColumn("Salary",typeof(decimal)) });
    
                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();
    
                string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.tblDSR_VisaSale";
                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("FileID", "FileID");
                        sqlBulkCopy.ColumnMappings.Add("InvoiceNo", "InvoiceNo");
                        sqlBulkCopy.ColumnMappings.Add("InvoiceDate", "InvoiceDate");
                        sqlBulkCopy.ColumnMappings.Add("DebtorName", "DebtorName");
                        sqlBulkCopy.ColumnMappings.Add("OTACode", "OTACode");
                        sqlBulkCopy.ColumnMappings.Add("DebtorLocation", "DebtorLocation");
                        sqlBulkCopy.ColumnMappings.Add("DueDate", "DueDate");
                        sqlBulkCopy.ColumnMappings.Add("PaxName", "PaxName");
                        sqlBulkCopy.ColumnMappings.Add("BookedBy", "BookedBy");
                        sqlBulkCopy.ColumnMappings.Add("BTARequisitionNo", "BTARequisitionNo");
                        sqlBulkCopy.ColumnMappings.Add("EmployeeCode", "EmployeeCode");
                        sqlBulkCopy.ColumnMappings.Add("Particular", "Particular");
                        sqlBulkCopy.ColumnMappings.Add("VisaType", "VisaType");
                        sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                        sqlBulkCopy.ColumnMappings.Add("VisaCharge", "VisaCharge");
                        sqlBulkCopy.ColumnMappings.Add("DDCharge", "DDCharge");
                        sqlBulkCopy.ColumnMappings.Add("Discount", "Discount");
                        sqlBulkCopy.ColumnMappings.Add("ServiceFee", "ServiceFee");
                        sqlBulkCopy.ColumnMappings.Add("ServiceCharge", "ServiceCharge");
                        sqlBulkCopy.ColumnMappings.Add("DeliveryCharge", "DeliveryCharge");
                        sqlBulkCopy.ColumnMappings.Add("CreditCardCharge", "CreditCardCharge");
                        sqlBulkCopy.ColumnMappings.Add("ServiceCharge1", "ServiceCharge1");
                        sqlBulkCopy.ColumnMappings.Add("ServiceCharge2", "ServiceCharge2");
                        sqlBulkCopy.ColumnMappings.Add("ServiceTax", "ServiceTax");
                        sqlBulkCopy.ColumnMappings.Add("ServiceTaxAbsorp", "ServiceTaxAbsorp");
                        sqlBulkCopy.ColumnMappings.Add("TDS", "TDS");
                        sqlBulkCopy.ColumnMappings.Add("TotalAmount", "TotalAmount");
                        sqlBulkCopy.ColumnMappings.Add("SupplierBillNo", "SupplierBillNo");
                        sqlBulkCopy.ColumnMappings.Add("SupplierBillDate", "SupplierBillDate");
                        sqlBulkCopy.ColumnMappings.Add("SupplierName", "SupplierName");
                        sqlBulkCopy.ColumnMappings.Add("OTACode2", "OTACode2");
                        sqlBulkCopy.ColumnMappings.Add("BillAmount", "BillAmount");
                        sqlBulkCopy.ColumnMappings.Add("SupplierServiceCharge", "SupplierServiceCharge");
                        sqlBulkCopy.ColumnMappings.Add("SupplierServiceTax", "SupplierServiceTax");
                        sqlBulkCopy.ColumnMappings.Add("SupplierTDS", "SupplierTDS");
                        sqlBulkCopy.ColumnMappings.Add("Commission", "Commission");
                        sqlBulkCopy.ColumnMappings.Add("Department", "Department");
                        sqlBulkCopy.ColumnMappings.Add("FileNo", "FileNo");
                        sqlBulkCopy.ColumnMappings.Add("TourFileNo", "TourFileNo");
                        sqlBulkCopy.ColumnMappings.Add("OrderNo", "OrderNo");
                        sqlBulkCopy.ColumnMappings.Add("KeyManager", "KeyManager");
                        sqlBulkCopy.ColumnMappings.Add("SettledAmount", "SettledAmount");
                        sqlBulkCopy.ColumnMappings.Add("SettlementVoucher", "SettlementVoucher");
                        sqlBulkCopy.ColumnMappings.Add("CreditNoteNo", "CreditNoteNo");
                        sqlBulkCopy.ColumnMappings.Add("ApprovalCode", "ApprovalCode");
                        sqlBulkCopy.ColumnMappings.Add("ApprovalDate", "ApprovalDate");
                        sqlBulkCopy.ColumnMappings.Add("Approver", "Approver");
                        sqlBulkCopy.ColumnMappings.Add("ArrivalDate", "ArrivalDate");
                        sqlBulkCopy.ColumnMappings.Add("BookingAgent", "BookingAgent");
                        sqlBulkCopy.ColumnMappings.Add("ClientData1", "ClientData1");
                        sqlBulkCopy.ColumnMappings.Add("ClientData2", "ClientData2");
                        sqlBulkCopy.ColumnMappings.Add("ClientData3", "ClientData3");
                        sqlBulkCopy.ColumnMappings.Add("ClientData4", "ClientData4");
                        sqlBulkCopy.ColumnMappings.Add("ClientData7", "ClientData7");
                        sqlBulkCopy.ColumnMappings.Add("ClientData8", "ClientData8");
                        sqlBulkCopy.ColumnMappings.Add("CostCentre", "CostCentre");
                        sqlBulkCopy.ColumnMappings.Add("CustSpecFullRate", "CustSpecFullRate");
                        sqlBulkCopy.ColumnMappings.Add("CustSpecFullRateCur", "CustSpecFullRateCur");
                        sqlBulkCopy.ColumnMappings.Add("CustSpecLowRate", "CustSpecLowRate");
                        sqlBulkCopy.ColumnMappings.Add("CustSpecLowRateCur", "CustSpecLowRateCur");
                        sqlBulkCopy.ColumnMappings.Add("DepartAirCity", "DepartAirCity");
                        sqlBulkCopy.ColumnMappings.Add("DepartAirCityCode", "DepartAirCityCode");
                        sqlBulkCopy.ColumnMappings.Add("DepartCountryCode", "DepartCountryCode");
                        sqlBulkCopy.ColumnMappings.Add("GroEmpNo", "GroEmpNo");
                        sqlBulkCopy.ColumnMappings.Add("GROOrderNo", "GROOrderNo");
                        sqlBulkCopy.ColumnMappings.Add("ManagerSupervisor", "ManagerSupervisor");
                        sqlBulkCopy.ColumnMappings.Add("PNR", "PNR");
                        sqlBulkCopy.ColumnMappings.Add("Project", "Project");
                        sqlBulkCopy.ColumnMappings.Add("ReasonCode", "ReasonCode");
                        sqlBulkCopy.ColumnMappings.Add("ReasonCodeDenied", "ReasonCodeDenied");
                        sqlBulkCopy.ColumnMappings.Add("ReasonTrip", "ReasonTrip");
                        sqlBulkCopy.ColumnMappings.Add("RefundableFare", "RefundableFare");
                        sqlBulkCopy.ColumnMappings.Add("RequestNo", "RequestNo");
                        sqlBulkCopy.ColumnMappings.Add("ReservationDate", "ReservationDate");
                        sqlBulkCopy.ColumnMappings.Add("SalesChannels", "SalesChannels");
                        sqlBulkCopy.ColumnMappings.Add("SuppCode", "SuppCode");
                        sqlBulkCopy.ColumnMappings.Add("TeamCode", "TeamCode");
                        sqlBulkCopy.ColumnMappings.Add("TravellerStatus", "TravellerStatus");
                        sqlBulkCopy.ColumnMappings.Add("VisaType2", "VisaType2");
                        sqlBulkCopy.ColumnMappings.Add("VoucherNo", "VoucherNo");
                        sqlBulkCopy.ColumnMappings.Add("VoucherType", "VoucherType");
                        sqlBulkCopy.ColumnMappings.Add("TicketType", "TicketType");
                        sqlBulkCopy.ColumnMappings.Add("TripType", "TripType");
                        sqlBulkCopy.ColumnMappings.Add("PanNo", "PanNo");
                        sqlBulkCopy.ColumnMappings.Add("DocketNo", "DocketNo");
                        sqlBulkCopy.ColumnMappings.Add("RequestingBranch", "RequestingBranch");
                        sqlBulkCopy.ColumnMappings.Add("PrintNarration", "PrintNarration");
                        sqlBulkCopy.ColumnMappings.Add("Market", "Market");
                        sqlBulkCopy.ColumnMappings.Add("Tag1", "Tag1");
                        sqlBulkCopy.ColumnMappings.Add("Tag2", "Tag2");
                        sqlBulkCopy.ColumnMappings.Add("CPCCardNo", "CPCCardNo");
                        sqlBulkCopy.ColumnMappings.Add("CPCVoucherNo", "CPCVoucherNo");
                        sqlBulkCopy.ColumnMappings.Add("DebtorCurrency", "DebtorCurrency");
                        sqlBulkCopy.ColumnMappings.Add("DebtorROE", "DebtorROE");
                        sqlBulkCopy.ColumnMappings.Add("ForexVisaCharge", "ForexVisaCharge");
                        sqlBulkCopy.ColumnMappings.Add("ForexDDCharge", "ForexDDCharge");
                        sqlBulkCopy.ColumnMappings.Add("ForexDiscount", "ForexDiscount");
                        sqlBulkCopy.ColumnMappings.Add("ForexServiceFee", "ForexServiceFee");
                        sqlBulkCopy.ColumnMappings.Add("ForexServiceCharge", "ForexServiceCharge");
                        sqlBulkCopy.ColumnMappings.Add("ForexDelivery", "ForexDelivery");
                        sqlBulkCopy.ColumnMappings.Add("ForexCCCharge", "ForexCCCharge");
                        sqlBulkCopy.ColumnMappings.Add("ForexServiceCharge1", "ForexServiceCharge1");
                        sqlBulkCopy.ColumnMappings.Add("ForexServiceCharge2", "ForexServiceCharge2");
                        sqlBulkCopy.ColumnMappings.Add("ForexServiceTaxTotal", "ForexServiceTaxTotal");
                        sqlBulkCopy.ColumnMappings.Add("ForexTotal", "ForexTotal");
                        sqlBulkCopy.ColumnMappings.Add("CSVFileName", "CSVFileName");
                        sqlBulkCopy.ColumnMappings.Add("AfterOffice", "AfterOffice");
                        sqlBulkCopy.ColumnMappings.Add("ApprovedOn", "ApprovedOn");
                        sqlBulkCopy.ColumnMappings.Add("ApprovedBy", "ApprovedBy");
                        sqlBulkCopy.ColumnMappings.Add("BillTo", "BillTo");
                        sqlBulkCopy.ColumnMappings.Add("Address", "Address");
                        sqlBulkCopy.ColumnMappings.Add("Address1", "Address1");
                        sqlBulkCopy.ColumnMappings.Add("Address2", "Address2");
                        sqlBulkCopy.ColumnMappings.Add("City", "City");
                        sqlBulkCopy.ColumnMappings.Add("Basic", "Basic");
                        sqlBulkCopy.ColumnMappings.Add("Cess", "Cess");
                        sqlBulkCopy.ColumnMappings.Add("Hcess", "Hcess");
                        sqlBulkCopy.ColumnMappings.Add("TypeOfTravel", "TypeOfTravel");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        con.Close();
                    }
                }
            }
        }
    }
    
    Someone please help me with this..I don't know how to fix this error. Thank you!

    Felixx Teo

    Monday, May 29, 2017 6:38 AM

Answers

  • Hi,

    I've solved my problem with another easier way.

    It Can simply import data from excel to database using Windows Form Application C#.

    Here is the code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    using Excel;
    
    namespace Test_Import
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                OpenFileDialog ope = new OpenFileDialog();
                ope.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
                if (ope.ShowDialog() == DialogResult.Cancel)
                    return;
    
                FileStream stream = new FileStream(ope.FileName, FileMode.Open);
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                DataSet result = excelReader.AsDataSet();
    
                DataClasses1DataContext conn = new DataClasses1DataContext();
                foreach (DataTable table in result.Tables)
                {
                    foreach (DataRow dr in table.Rows)
                    {
                        test addtable = new test()
                        {
                           test_id = Convert.ToString(dr[0]),
                           test_name = Convert.ToString(dr[1]),
                           test_surname = Convert.ToString(dr[2]),
                           test_age = Convert.ToInt32(dr[3])
                        };
                        conn.tests.InsertOnSubmit(addtable);
                    }
                }
                conn.SubmitChanges();
    
                excelReader.Close();
                stream.Close();
    
                MessageBox.Show("Data Imported!");
            }
        }
    }

    Here is the link to download Excel Data Reader :
    http://exceldatareader.codeplex.com/

    Thank you guys!


    Felixx Teo

    • Marked as answer by FelixxTeo Friday, June 9, 2017 4:16 AM
    Friday, June 9, 2017 4:16 AM

All replies

  • Hi FelixxTeo,

    the code not able to find the file.

    make sure that Excel file is available on that location.

    to confirm that , you can open that location manually using File Explorer.

    the path is mentioned in the error.

    you can also check that path is correct.

    and your code generate accurate path.

    try to go to that location manually take the path and compare it with the path generated by your code.

    if the path generated by your code having some mistake then try to correct it.

    other thing I want to confirm with you that what is "Path" that you are using to get file name of Fileupload1? is it any control name?

    you can note that , you are trying to append server path with file name contains by FileUpload1.

    but you did not save the file on the server.

    you had commented code to save the file.

    and then you are trying to use that path in your code to open connection but file is not crated there.

    so file is not available.

    from the above code I think that something wrong with the path of Excel file that you try to open.

    so try to correct it to work properly.

    if you are trying to open existing excel file then let me know about that.

    try to check above mentioned possibilities for the cause of this issue.

    let me know about the testing results. so that I can try to provide further suggestions if needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Monday, May 29, 2017 8:12 AM
    Moderator
  • Hi Deepak

    Thanks for your guide

    Yes I'm trying to open an existing file.
    To make it easy I've just put it on my desktop.

        protected void Upload(object sender, EventArgs e)
        {
            //Upload and save the file
            string excelPath = Server.MapPath(".xlsx") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(excelPath);

    Then it shows this error.

    After that i though it was my table's column wasn't match, so I've tried to double check my database table and the excel table. It was 100% the same column I've inserted.
    As you said it might be my path is wrong. Sorry, I‘m quite new to C# so it might be a lot of confusing for me for the information on path.
    Hopefully you could help me out of this.
    Thank you!

    Regards


    Felixx Teo


    • Edited by FelixxTeo Wednesday, May 31, 2017 5:20 AM
    Wednesday, May 31, 2017 3:03 AM
  • Hi FelixxTeo,

    to confirm the correct file path , you just need to open that location using File explorer and try to find the file.

    if you able to find the file, it means file is at correct place and you are referring to correct path.

    if we talk about the new error.

    first I want to confirm with you that what you want to do here?

    if we see the line then we can find that you are trying to save some string value in string variable named "Sheet1". so it is possible that you are trying to save the sheet name.

    if not , then try to inform us what you are trying to save in that variable.

    is your oledb datasource contains only one record?

    first try to fetch that data successfully then try to assign it to variable.

    you need to only check with the database.

    if you are 100 % sure then please try to post the picture of that column name so that we can also confirm that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 31, 2017 9:29 AM
    Moderator
  • Yes, your path is incorrect. Do not use the Desktop since it's a special folder for the interactive Windows user and an ASP.NET app would not normally have access to this folder.

    I would either create a temporary folder path from the root of C: or use Server.MapPath, which would be a path relative to the ASP.NET app. Keep in mind that you may need to grant write permissions to the folder where the files are being uploaded, especially when deploying to a web server.

    http://asp.net-tutorials.com/controls/file-upload-control/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 31, 2017 1:39 PM
  • As Deepak indicated, we need to know what you are doing here:

    An Excel Workbook can have multiple Worksheets. Do you want the name of the first Worksheet in the Workbook?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 31, 2017 1:51 PM
  • Hi Deepak,

    Sorry for the late reply!

    I've tried with a smaller table on this. Which is

    i just wanted the first sheet of the file.

    And maybe I just don't really understand about assigning value into a variable. That might make me really confused.

    Actually I took this code from ASPSnippets and change a the value inside into mine. I already hyperlinked it on that name. Since from my college project i learnt from his article. But this time is quite challenging for me. I've drag this task for few days already. Hopefully I can make it work today.
    Thanks for guide! I will update my work to you again later. Maybe need some more help from you.


    Felixx Teo

    • Marked as answer by FelixxTeo Thursday, June 1, 2017 6:32 AM
    • Unmarked as answer by FelixxTeo Thursday, June 1, 2017 6:33 AM
    Thursday, June 1, 2017 3:27 AM
  • Hi Paul,

    I've used the method u gave in the link.

    I think I already get the path from excel sheet right. But there's a problem that Deepak mentioned on it.

    I think there is the error from the start. Do you guys got any other option for me to correct this?
    And yeah I just wanna the 1st sheet in the file, the others are all blank.


    Felixx Teo


    • Edited by FelixxTeo Thursday, June 1, 2017 7:57 AM
    Thursday, June 1, 2017 5:49 AM
  • There are a number of ways to get the first sheet. See if the below link helps:

    https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of-sheet-name-with-ole


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, June 1, 2017 12:31 PM
  • Hi Paul

    May I know how to get this into mine..

    using (OleDbConnection conn = new OleDbConnection(connString)) { conn.Open(); dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME"); }

    I'm actually don't understand how it works.


    Felixx Teo

    Friday, June 2, 2017 1:41 AM
  • Hi Jamesbn,

    I've found out my error.

    That I have already get my file path. The only thing is I need to store my FileUpload1 value into DataTable then only it will store into Database.

    Do you have any idea that could help me to store my FileUpload1 file into DataTable?


    Felixx Teo

    Friday, June 2, 2017 3:00 AM
  • Hi FelixxTeo,

    you had mentioned that,"The only thing is I need to store my FileUpload1 value into DataTable then only it will store into Database."

    try to refer example below.

    My table in SQL.

    MY Excel Sheet.

    design of web page.

    full code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;   
    using System.IO;   
    using System.Data.OleDb;   
    using System.Configuration;   
    using System.Data.SqlClient;
    
    namespace WebApplication57
    {
        public partial class _Default : Page
        {
            OleDbConnection Econ;   
        SqlConnection con;
            string constr, Query, sqlconn;
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
            private void ExcelConn(string FilePath)
        {   
       
            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);   
            
        }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
                InsertExcelRecords(CurrentFilePath);
    
            }
    
            private void connection()
        {   
            sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;   
            con = new SqlConnection(sqlconn);   
           
        }
            private void InsertExcelRecords(string FilePath)
       {   
           ExcelConn(FilePath);   
       
           Query = string.Format("Select [Name],[City],[Address],[Designation] 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 Exceldt = ds.Tables[0];   
          connection();   
          //creating object of SqlBulkCopy     
          SqlBulkCopy objbulk = new SqlBulkCopy(con);   
          //assigning Destination table name     
          objbulk.DestinationTableName = "Employee";   
          //Mapping Table column     
          objbulk.ColumnMappings.Add("Name", "Name");   
          objbulk.ColumnMappings.Add("City", "City");   
          objbulk.ColumnMappings.Add("Address", "Address");   
          objbulk.ColumnMappings.Add("Designation", "Designation");   
          //inserting Datatable Records to DataBase     
          con.Open();   
          objbulk.WriteToServer(Exceldt);   
          con.Close();   
       }
    
    
    
    
    }
    }

    try to refer the example and make required changes in your code to make it work.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 2, 2017 8:19 AM
    Moderator
  • Hi Deepak,

    Thank you to share your code for me!

    But may I know what should I change for the "Econ"?

    This isn't my path

          string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
          InsertExcelRecords(CurrentFilePath);
    
    
    How can I change it to my own path?

    Felixx Teo




    • Edited by FelixxTeo Friday, June 2, 2017 9:51 AM
    Friday, June 2, 2017 9:28 AM
  • Hi FelixxTeo,

    error inform us that your access database is already open by you or any other user.

    you need to close the database first and then try to execute the code to solve this error.

    you also  need to change the query and write accurate field names.

    you need to make appropriate changes according to your code.

    this is just an example code. so it is possible that you need to change many things as per your requirement.

    to get the correct path you can use the line of code from your old code snippet.

    you had mentioned that in that old code you are getting the correct path.

    so it will solve your issue regarding path.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, June 5, 2017 7:53 AM
    Moderator
  • //Full Code

    using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.IO; using System.Data.OleDb; using System.Configuration; using System.Data.SqlClient; public partial class Import : System.Web.UI.Page { OleDbConnection Econ; SqlConnection con; string conString, Query, sqlconn; protected void Page_Load(object sender, EventArgs e) { } private void ExcelConn(string FilePath) { conString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath); Econ = new OleDbConnection(conString); } protected void Upload(object sender, EventArgs e) { string excelPath = Server.MapPath(FileUpload1.PostedFile.FileName); InsertExcelRecords(excelPath); conString = string.Empty; string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); switch (extension) { case ".xls": //Excel 97-03 conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; break; case ".xlsx": //Excel 07 or higher conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString; break; } conString = string.Format(conString, excelPath); } private void connection() { sqlconn = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; con = new SqlConnection(sqlconn); } private void InsertExcelRecords(string FilePath) { ExcelConn(FilePath); Query = string.Format("Select [EmployeeID],[CompanyName],[ContactName],[ContactTitle],[EmployeeAddress],[PostalCode] 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 Exceldt = ds.Tables[0]; connection(); //creating object of SqlBulkCopy SqlBulkCopy objbulk = new SqlBulkCopy(con); //assigning Destination table name objbulk.DestinationTableName = "dbo.EmployeeTable"; //Mapping Table column objbulk.ColumnMappings.Add("EmployeeID", "EmployeeID"); objbulk.ColumnMappings.Add("CompanyName", "CompanyName"); objbulk.ColumnMappings.Add("ContactName", "ContactName"); objbulk.ColumnMappings.Add("ContactTitle", "ContactTitle"); objbulk.ColumnMappings.Add("EmployeeAddress", "EmployeeAddress"); objbulk.ColumnMappings.Add("PostalCode", "PostalCode"); //inserting Datatable Records to DataBase con.Open(); objbulk.WriteToServer(Exceldt); con.Close(); } }

    Web.config

    <?xml version="1.0"?>
    <!--
      For more information on how to configure your ASP.NET application, please visit
      http://go.microsoft.com/fwlink/?LinkId=169433
      -->
    <configuration>
      <connectionStrings>
        <add name = "constr" connectionString = "Data Source=EQSOFTBACKUP2\SQLEXPRESS,Database=Test"/>
        <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
        <add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
      </connectionStrings>
      <system.web>
    		<compilation debug="true" targetFramework="4.0"/>
    	</system.web>
    </configuration>

    What should i put on that error part...

    Have been drag this for one week and still not yet done

    Anyone else who could help me..

    Thanks


    Felixx Teo

    Tuesday, June 6, 2017 8:37 AM
  • Hi FelixxTeo,

    if we see the error then we can find that it is not able to find 'sheet1'.

    try to check that your workbook contains 'sheet1'.

    also check that you had spelled correctly in the code.

    to avoid the error, you can copy the sheet name and paste in the code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 6, 2017 9:00 AM
    Moderator
  • Hi Deepak,

    Yes the file name is sheet1

    I've tried to change the file name but it still showing the same error.


    Felixx Teo


    • Edited by FelixxTeo Tuesday, June 6, 2017 9:35 AM
    Tuesday, June 6, 2017 9:34 AM
  • Hi FelixxTeo,

    you need to check worksheet name. not the workbook name.

    sheet1 is worksheet name inside workbook.

    you are using the sheet name in query in the code.

    it is possible that in the sheet name is 'Sheet1' and in code you are using 'sheet1'.

    you can find the difference in both.

    you need to make sure that both are same.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 6, 2017 9:40 AM
    Moderator
  • Hi Deepak,

    Sorry that I misunderstood that.

    I've change the sheet1& to Sheet1& when i found that it was capital "S"

    Then the error shows the same thing again. 


    Felixx Teo

    Tuesday, June 6, 2017 9:53 AM
  • I would double check the file path (Data Source) to the Excel Workbook. I believe that it is either incorrect or cannot be accessed by the app.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Tuesday, June 6, 2017 11:54 AM
  • Hi Paul,

    Usually the file path will show the destination of the file going or where the file located?

    I'm quite confuse with this.

    What my path shows was going to my .net , not where the file located.


    Felixx Teo

    Wednesday, June 7, 2017 3:05 AM
  • Hi FelixxTeo,

    you had mentioned that," I'm quite confuse with this. What my path shows was going to my .net , not where the file located."

    if we see your code.

    I suggest you to understand the code first.

    here, we can divide that line in to 2 parts.

    (1) Server.MapPath

    (2) FileUpload1.Postedfile.FileName

    Server.MapPath Method-> The MapPath method maps the specified relative or virtual path to the corresponding physical directory on the server.

    FileUpload.PostedFile Property -> The PostedFile property gets the underlying HttpPostedFile object for a file that is uploaded by using the FileUpload control. Use this property to access additional properties on the uploaded file.

    so in your code postedfile.filename method will only give you the name of the file.

    the server path or path to your application will be given by the server.mappath.

    so you need to understand the methods and properties that you are using in the code to get the exact idea about the code.

    so if that line not giving you the correct path where file is located then you need to correct that line.

    what we are suggesting here is just to give an example. it will not work as it is on your side.

    you need to make the changes in the code according to your requirement. so that it can work on your side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Angie Xu Thursday, June 8, 2017 1:49 AM
    Wednesday, June 7, 2017 8:00 AM
    Moderator
  • Hi Deepak,

    Thanks for the explanation. That was clear enough for me to understand.

    Will try my best to work on it until it done.

    Thank you!


    Felixx Teo

    Wednesday, June 7, 2017 10:02 AM
  • Hi guys,

    Full code

    using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using System.Data.OleDb; using System.Configuration; using System.Data.SqlClient; using System.Data; public partial class _Default : System.Web.UI.Page { OleDbConnection Econ; SqlConnection con; string constr, Query, sqlconn; protected void Page_Load(object sender, EventArgs e) { } private void ExcelConn(string FilePath) { 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 connection() { sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString; con = new SqlConnection(sqlconn); } private void InsertExcelRecords(string FilePath) { ExcelConn(FilePath); Query = string.Format("Select [EmployeeID],[CompanyName],[ContactName],[ContactTitle],[EmployeeAddress],[PostalCode] 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 Exceldt = ds.Tables[0]; connection(); //creating object of SqlBulkCopy SqlBulkCopy objbulk = new SqlBulkCopy(con); //assigning Destination table name objbulk.DestinationTableName = "dbo.EmployeeTable"; //Mapping Table column objbulk.ColumnMappings.Add("EmployeeID", "EmployeeID"); objbulk.ColumnMappings.Add("CompanyName", "CompanyName"); objbulk.ColumnMappings.Add("ContactName", "ContactName"); objbulk.ColumnMappings.Add("ContactTitle", "ContactTitle"); objbulk.ColumnMappings.Add("EmployeeAddress", "EmployeeAddress"); objbulk.ColumnMappings.Add("PostalCode", "PostalCode"); //inserting Datatable Records to DataBase con.Open(); objbulk.WriteToServer(Exceldt); con.Close(); } protected void Button1_Click(object sender, EventArgs e) { string CurrentFilePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName); InsertExcelRecords(CurrentFilePath); } }

    Why am i keep getting this error even i checked my path are spelled correctly..


    Felixx Teo


    • Edited by FelixxTeo Thursday, June 8, 2017 9:20 AM
    Thursday, June 8, 2017 9:09 AM
  • Hi FelixxTeo,

    copy that path showed in the error.
    open file explorer.

    paste the path there and press enter.

    check that file location opens and that file is there or not.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 9, 2017 2:17 AM
    Moderator
  • Hi,

    I've solved my problem with another easier way.

    It Can simply import data from excel to database using Windows Form Application C#.

    Here is the code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.IO;
    using Excel;
    
    namespace Test_Import
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                OpenFileDialog ope = new OpenFileDialog();
                ope.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
                if (ope.ShowDialog() == DialogResult.Cancel)
                    return;
    
                FileStream stream = new FileStream(ope.FileName, FileMode.Open);
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                DataSet result = excelReader.AsDataSet();
    
                DataClasses1DataContext conn = new DataClasses1DataContext();
                foreach (DataTable table in result.Tables)
                {
                    foreach (DataRow dr in table.Rows)
                    {
                        test addtable = new test()
                        {
                           test_id = Convert.ToString(dr[0]),
                           test_name = Convert.ToString(dr[1]),
                           test_surname = Convert.ToString(dr[2]),
                           test_age = Convert.ToInt32(dr[3])
                        };
                        conn.tests.InsertOnSubmit(addtable);
                    }
                }
                conn.SubmitChanges();
    
                excelReader.Close();
                stream.Close();
    
                MessageBox.Show("Data Imported!");
            }
        }
    }

    Here is the link to download Excel Data Reader :
    http://exceldatareader.codeplex.com/

    Thank you guys!


    Felixx Teo

    • Marked as answer by FelixxTeo Friday, June 9, 2017 4:16 AM
    Friday, June 9, 2017 4:16 AM