OleDbDataReader对象无法读取该文件

本文关键字：文件 读取 对象 OleDbDataReader | 更新日期: 2023-09-27 18:26:46

我开发了一个功能，可以将Excel工作表导入到表中。但是当我上传文件并点击按钮导入时。该代码不起作用，并给了我下面提到的错误。我尝试过使用DataAdapter，但也不起作用。请参阅以下错误：-

Microsoft Office Access数据库引擎找不到对象"TableName"。请确保该对象存在，并拼写其名称和路径名正确。

此外，请参阅代码以供参考：-

 private void ImporttoSQL(string sPath)
{  
    string sSourceConstr1 = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:'AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""", sPath);
    string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);
    OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
    using (sSourceConnection)
    {
        string sql = string.Format("Select [Merchant_Name],[Store_Name],[Store_Address],[City] FROM [MerchantTempDetail]", "Sheet1$");
        OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
        sSourceConnection.Open();
        conn.Open();
        using (OleDbDataReader dr = command.ExecuteReader())
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.DestinationTableName = "MerchantTempDetail";
                //You can mannualy set the column mapping by the following way.
                //  bulkCopy.ColumnMappings.Add("Mini_Category_Id", "Mini_Category_Id");
                //bulkCopy.ColumnMappings.Add("CategoryId", "CategoryId");
                bulkCopy.ColumnMappings.Add("Merchant_Name", "Merchant_Name");
                bulkCopy.ColumnMappings.Add("Store_Name", "Store_Name");
                bulkCopy.ColumnMappings.Add("Store_Address", "Store_Address");
                bulkCopy.ColumnMappings.Add("City", "City");
                bulkCopy.WriteToServer(dr);
            }
        }
    }
}

编辑代码：-

 public static void ExcelToSqlServerBulkCopy()
{
    // Connection String to Excel Workbook
    // Jet4
    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
    // Ace Ole db 12
    string excelAceOleDb12ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AgentList.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
    // Create Connection to Excel Workbook
    using (OleDbConnection connection = new OleDbConnection(excelAceOleDb12ConnectionString))
    {
        OleDbCommand command = new OleDbCommand("Select [Merchant_Name],[Store_Name],[Store_Address],[City] FROM [Sheet1$]", connection);
        // open excel
        connection.Open();
        // Create DbDataReader to Data Worksheet
        using (DbDataReader dr = command.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString;
            // Bulk Copy to SQL Server
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "MerchantTempDetail";
                bulkCopy.WriteToServer(dr);
            }
        }
    }
}
protected void btnImport_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string sPath = Server.MapPath(FileUpload1.FileName);
        FileUpload1.SaveAs(sPath);
        ExcelToSqlServerBulkCopy();
    }
}

OleDbDataReader对象无法读取该文件

您需要定义2个连接，一个用于Excel源；另一个用于要批量复制到的sql数据库。（此代码已经过测试）如果您需要复制到内存中的数据表，请在MSDN上使用此示例。

public static void ExcelToSqlServerBulkCopy ()
{
  // Connection String to Excel Workbook
  // Jet4
  string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
  // Ace Ole db 12
  string excelAceOleDb12ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AgentList.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
  // Create Connection to Excel Workbook
  using (OleDbConnection connection = new OleDbConnection(excelAceOleDb12ConnectionString))
  {
    OleDbCommand command = new OleDbCommand("Select [Merchant_Name],[Store_Name],[Store_Address],[City] FROM [AgentList$]", connection);
    // open excel
    connection.Open ();        
    // Create DbDataReader to Data Worksheet
    using (DbDataReader dr = command.ExecuteReader ())
    {
      // SQL Server Connection String
      string sqlConnectionString = @"Data Source=.'SQLEXPRESS;Initial Catalog=StackOverflow;Integrated Security=True";
      // Bulk Copy to SQL Server
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy (sqlConnectionString))
      {
        bulkCopy.DestinationTableName = "Q26382169";
        bulkCopy.WriteToServer (dr);
      }
    }
  }
}

使用以上变量名称的非常重要的细节：

  1. 必须事先创建要复制到的数据库（StackOverflow）
  2. 目标表（Q26382169）必须存在于该数据库中，DDL琐碎，与Excel文件的列相同
  3. 由于您使用HDR=YES选项，Excel工作表的第一行必须包含指定的列名：Merchant_Name、Store_Name、Store_Address、City（不带[]）
  4. OleDbCommand中工作表的名称（AgentList$）

最终通过调试解决。

 private void ImporttoSQL(string sPath)
{
    string sSourceConstr1 = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:'AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""", sPath);
    string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);
    //   string sSource = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + ";Extended Properties=Excel 8.0", sPath);
    // string sDestConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
    using (sSourceConnection)
    {
        SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultSQLConnectionString"].ConnectionString);
        string sql = "Select [Merchant_Name],[Store_Name],[Store_Address],[City] FROM [Sheet1$]";
        OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
        sSourceConnection.Open();
        conn.Open();
        using (OleDbDataReader dr = command.ExecuteReader())
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.DestinationTableName = "MerchantTempDetail";
                bulkCopy.ColumnMappings.Add("Merchant_Name", "Merchant_Name");
                bulkCopy.ColumnMappings.Add("Store_Name", "Store_Name");
                bulkCopy.ColumnMappings.Add("Store_Address", "Store_Address");
                bulkCopy.ColumnMappings.Add("City", "City");
                 bulkCopy.WriteToServer(dr);
            }
        }
    }
}
相关文章：