locked
excel data to database RRS feed

  • Question

  • User219039814 posted

    I am trying this code.. to be able to transfer the uploaded excel file data into the database.

    protected void Upload(object sender, EventArgs e)
    {


    FileUpload1.SaveAs(@"F:\G-Procurement\Files\" + "101" + ".xlsx");
    String excelPath = FileUpload1.PostedFile.FileName;

    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]["TABLE_NAME"].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.tblPersons";

    //[OPTIONAL]: Map the Excel columns with that of the database table
    sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
    sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
    con.Open();
    sqlBulkCopy.WriteToServer(dtExcelData);
    con.Close();
    }
    }
    }
    }

    This code keeps giving following error

    : The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

    After searching the net.. I gave full premission option in the security tab of the folder..

    But still this error persists.. Can some one tell me..

    <connectionStrings>
    <add name = "Excel03ConString" connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:/G-Procurement/Files/101.xls;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>

    The above is the connectionstring in the webconfig file.

    Monday, September 3, 2018 4:02 AM

Answers

  • User-893317190 posted

    Hi vijaylakshmi,

    The post below has a similar problem to yours .excel_con.Open() can only deal with file in disk.If you give it a wrong connectionstring ,it will cause the exception.Please check whether the connectionstring really connects a real file in your disk.

    https://stackoverflow.com/questions/28293911/the-microsoft-office-access-database-engine-cannot-open-or-write-to-the-file

    As KathyW  has said, your code has some issues which may cause wrong connectionstring. I have written a similar sample ,below is my code.

    string conString = string.Empty;
                string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        FileUpload1.SaveAs(@"D:\files\101.xls");
                        conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\files\101.xls;Extended Properties='Excel 8.0;HDR=YES'";
                        break;
                    case ".xlsx": //Excel 07 or higher
                        FileUpload1.SaveAs(@"D:\files\101.xlsx");
                        conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\files\101.xlsx;Extended Properties='Excel 8.0;HDR=YES'";
                        break;
                }
            
    
                using (OleDbConnection excel_con = new OleDbConnection(conString))
                {
                    excel_con.Open();
                    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].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["UserItemDb3"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            //Set the database table name
                            sqlBulkCopy.DestinationTableName = "dbo.salary";
                            //[OPTIONAL]: Map the Excel columns with that of the database table
                            sqlBulkCopy.ColumnMappings.Add("Id", "id");
                       
                            sqlBulkCopy.ColumnMappings.Add("Name", "name");
                            sqlBulkCopy.ColumnMappings.Add("Salary", "salary");
                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
                    }
                }
            }

    Best regards,

    Ackerly Xu
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 4, 2018 5:41 AM
  • User-821857111 posted

    Thank you for the sample code..

    But, Its giving an error at

    string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;

    You need to change the connection string in the sample to match the one that you are using.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 5, 2018 9:49 PM

All replies

  • User409696431 posted

    You are saving the uploaded file as F:\G-Procurement\Files\101.xlsx.

    You are calling a function with excelPath, which is FileUpload1.PostedFile.<g class="gr_ gr_21 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="21" data-gr-id="21">FileName</g>.

    The first issue is that those two are not the same.  PostedFile.FileName, in modern browsers, simply returns the name of the uploaded file.  No path.  Even if it returned a path, it would not be useful on the server, since the server has no <g class="gr_ gr_30 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="30" data-gr-id="30">access</g> the path on the web visitor's computer.

    You've saved the file to the server's F drive, and changed its name to 101.xlsx, and that is the path and name you should use for excelPath.

    The second issue is: If the folder G-Procurement is not <g class="gr_ gr_26 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="26" data-gr-id="26">inside</g> your website, your site needs permission to write and read from that folder.  Does it have those permissions?

    The third issue is that you are testing for the .xls or .xlsx extension after you've already saved the file with <g class="gr_ gr_25 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="25" data-gr-id="25">an .xlsx</g> extension.  Test for the extension before you save the file, and save it appropriately.

    There may be other <g class="gr_ gr_68 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-del replaceWithoutSep" id="68" data-gr-id="68">issues,</g> but <g class="gr_ gr_50 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="50" data-gr-id="50">clear</g> those up first before you test again.

    Monday, September 3, 2018 7:44 AM
  • User-821857111 posted

    My recommendation is not to use OleDb to work with Excel in ASP.NET applications. Use EPPlus - a free open source Office Open XML-based library - instead. Here's how to use it to do your bulk insert:

    https://www.mikesdotnetting.com/article/297/the-best-way-to-import-data-from-excel-to-sql-server-via-asp-net

    Monday, September 3, 2018 9:39 AM
  • User-893317190 posted

    Hi vijaylakshmi,

    The post below has a similar problem to yours .excel_con.Open() can only deal with file in disk.If you give it a wrong connectionstring ,it will cause the exception.Please check whether the connectionstring really connects a real file in your disk.

    https://stackoverflow.com/questions/28293911/the-microsoft-office-access-database-engine-cannot-open-or-write-to-the-file

    As KathyW  has said, your code has some issues which may cause wrong connectionstring. I have written a similar sample ,below is my code.

    string conString = string.Empty;
                string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                switch (extension)
                {
                    case ".xls": //Excel 97-03
                        FileUpload1.SaveAs(@"D:\files\101.xls");
                        conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\files\101.xls;Extended Properties='Excel 8.0;HDR=YES'";
                        break;
                    case ".xlsx": //Excel 07 or higher
                        FileUpload1.SaveAs(@"D:\files\101.xlsx");
                        conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\files\101.xlsx;Extended Properties='Excel 8.0;HDR=YES'";
                        break;
                }
            
    
                using (OleDbConnection excel_con = new OleDbConnection(conString))
                {
                    excel_con.Open();
                    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].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["UserItemDb3"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {
                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            //Set the database table name
                            sqlBulkCopy.DestinationTableName = "dbo.salary";
                            //[OPTIONAL]: Map the Excel columns with that of the database table
                            sqlBulkCopy.ColumnMappings.Add("Id", "id");
                       
                            sqlBulkCopy.ColumnMappings.Add("Name", "name");
                            sqlBulkCopy.ColumnMappings.Add("Salary", "salary");
                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }
                    }
                }
            }

    Best regards,

    Ackerly Xu
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 4, 2018 5:41 AM
  • User219039814 posted

    Thank you for the sample code..

    But, Its giving an error at

    string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;
    Wednesday, September 5, 2018 4:52 AM
  • User426857743 posted

    An easy way is to use free spire.xls library. You can get free spire.xls dll from NuGet:
    https://www.nuget.org/packages/FreeSpire.XLS/

    And then use code like below to export data from excel to a data table. Next write data table to database. 

    Workbook workbook = new Workbook();  
         
    //Load excel (supports both .xls and .xlsx)
    workbook.LoadFromFile(@”..\FandH.xlsx”);          
    
    //Get the first sheet
    Worksheet sheet = workbook.Worksheets[0];
    
    //Export data to a data table
    DataTable datatable = sheet.ExportDataTable();

    Wednesday, September 5, 2018 7:48 AM
  • User-893317190 posted

    Hi vijaylakshmi,

    You should change to your own connectionString.

    string consString=ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    Best regards,

    Ackerly Xu

    Wednesday, September 5, 2018 9:57 AM
  • Wednesday, September 5, 2018 11:10 AM
  • User-821857111 posted

    Thank you for the sample code..

    But, Its giving an error at

    string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;

    You need to change the connection string in the sample to match the one that you are using.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 5, 2018 9:49 PM