外部表不是预期的格式。 [英] External table is not in the expected format.

问题描述

我创建一个小的应用程序导入Excel数据到我的数据库，当我点击它与错误崩溃按钮

  

外部表不是预期的格式。

我想谷歌搜索在这里和那里改变了codeS，但问题仍然存在。我试着将文件保存为一个.xls和当我运行code中的页面，谷歌浏览器本网页脱机时不可用（甚至不能进入调试）

下面是我的code：

 字符串strConnection = ConfigurationManager.ConnectionStrings [---]的ConnectionString。    字符串excelConnString =的String.Format（供应商= Microsoft.ACE.OLEDB.12.0;数据源= {0};扩展属性= \\的Excel 12.0 \\，文件路径）;
    //创建连接到Excel工作簿
    使用（OleDbConnection的excelConnection =新的OleDbConnection（excelConnString））
    {
        //创建的OleDbCommand来从Excel数据
        使用（OleDbCommand的CMD =新的OleDbCommand（选择* [工作表Sheet1 $]，excelConnection））
        {
            excelConnection.Open（）;
            使用（OleDbDataReader dReader = cmd.ExecuteReader（））
            {
                使用（SqlBulkCopy的sqlBulk =新SqlBulkCopy的（strConnection））
                {
                    //给你的目标表名
                    sqlBulk.DestinationTableName =表名;
                    sqlBulk.WriteToServer（dReader）;
                }
            }
        }
    }
 

解决方案

假设你已经完成了所有的preparations包括安装的的微软Access 2010中运行时 的收购相关的驱动程序试试，因为我曾在过去与它的成功以下。

如果您输入的文件是一个类型的 *。XLSX 替换试图

 扩展属性=Excel的12.0
 

您的连接字符串中的

 扩展属性=Excel的12.0 XML
 

此问题通常与Excel文件的问题或不匹配的格式会发生这样一个经验法则，你会想尝试的第一件事就是打开Excel并创建一个新的的.xlsx 与夫妇在它的样本数据其中的自己输入的（而不是复制/粘贴）。你并不需要太多的打字测试你的code片段。与你期待会做正确的信息细胞的夫妇。这种方法消除了与实际Excel文件，而不是你的C＃code的任何问题。

让我们知道你是怎么去了。

I am creating a small application to import excel data into my database, when i click the button it crashes with the error

External table is not in the expected format.

I tried googling and changing the codes here and there but the problem still occurs. I tried saving the file as a .xls and when i run the code the page went offline with google chrome's This webpage is not available (Cannot even enter debugging)

Here is my code:

    string strConnection = ConfigurationManager.ConnectionStrings["---"].ConnectionString;

    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
    //Create Connection to Excel work book 
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        //Create OleDbCommand to fetch data from Excel 
        using (OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection))
        {
            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {
                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                {
                    //Give your Destination table name 
                    sqlBulk.DestinationTableName = "TableName";
                    sqlBulk.WriteToServer(dReader);
                }
            }
        }
    }

解决方案

Assuming you have done all the preparations including installing Microsoft Access 2010 Runtime to acquire the relevant drivers try the following as I had success with it in the past.

If your input file is of a type *.xlsx trying replacing

Extended Properties="Excel 12.0"

in your connection string with

Extended Properties="Excel 12.0 Xml"

This issue normally happens with the problematic or mismatching format of Excel file so as a rule of thumb the first thing you would want to try is to open Excel and create a new .xlsx file with couple of sample data in it which you typed yourself (rather than copy/paste). You don't need much typing to test your code snippet. Couple of cells with the right info you're expecting will do. This approach eliminates having any problems with the actual Excel file rather than your C# code.

Let us know how you went.

这篇关于外部表不是预期的格式。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！