Excel “External table is not in the expected format.”

原创 2011年04月14日 10:01:00

Question:

I'm trying to read an Excel (xlsx) file using the code shown below. I get an "External table is not in the expected format." error unless I have the file already open in Excel. In other words, I have to open the file in Excel first before I can read if from my C# program. The xlsx file is on a share on our network. How can I read the file without having to open it first? Thanks

 

 

string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
DataSet ds = new DataSet();
adaptor.Fill(ds);
}

 

 

Answers:

"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

 

 

public static string path = @"C:/src/RedirectApplication/RedirectApplication/301s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

 

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ldl22847/article/details/6322339

OleDbConnection打开xls文件发生“External table is not in the expected format.”异常

网上大量能搜索到的是: 打开xls用"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Prop...
  • zhiyazw
  • zhiyazw
  • 2016-01-22 20:42:32
  • 906

C# 导入EXCEL 报错外部表不是预期的格式错误

错误经过:在读取Excel时,出现外部表不是预期的格式 错误原因1: 由于Excel 97-2003的连接格式与Excel 2010 的 不同造成。 以下是从网上摘抄原文 Excel...
  • jiajiayouba
  • jiajiayouba
  • 2012-05-03 15:27:50
  • 37838

Log4j使用方式详解

Logging-log4j Wiki Login Log4jXmlFormat FrontPage RecentChanges FindPage HelpContents Log4jXmlFor...
  • u010662488
  • u010662488
  • 2015-10-13 22:11:57
  • 10809

external table + alert_sid.log

创建 directory– create alert directory select * from dba_directories dd where 1=1 and dd.director...
  • ctypyb2002
  • ctypyb2002
  • 2017-08-29 12:34:25
  • 138

Excel “External table is not in the expected format.”

Question:I'm trying to read an Excel (xlsx) file using the code shown below. I get an "External tabl...
  • ldl22847
  • ldl22847
  • 2011-04-14 10:01:00
  • 7660

导入EXCEL 报错外部表不是预期的格式错误

错误经过:在读取Excel时,出现外部表不是预期的格式 错误原因1: 由于Excel 97-2003的连接格式与Excel 2010 的 不同造成。 以下是从网上摘抄原文 Excel...
  • TianGaojie123abc
  • TianGaojie123abc
  • 2013-10-15 15:25:27
  • 696

导入EXCEL2007报错外部表不是预期的格式错误

错误经过:在读取Excel时,出现外部表不是预期的格式 错误原因1: 由于Excel 97-2003的连接格式与Excel 2007 的 不同造成。 以下是从网上摘抄原文 Excel...
  • y_124882511
  • y_124882511
  • 2012-03-09 20:56:58
  • 4902

导入EXCEL2007报错"外部表不是预期的格式错误"的解决方法

错误经过:在读取Excel时,出现外部表不是预期的格式 错误原因1: 由于Excel 97-2003的连接格式与Excel 2007 的 不同造成。 以下是从网上摘抄原文 Excel...
  • bingtingabc
  • bingtingabc
  • 2012-06-19 14:55:11
  • 701

eclipse + MinGW搭建C/C++环境

Eclipse+CDT+MinGW 配置 C/C++ 开发环境     开场白:谨以此文献给所有喜欢探索和热爱开源软件的朋友们。     1:首先你得有 JAVA ...
  • Yan456jie
  • Yan456jie
  • 2015-03-06 15:39:40
  • 404

『ORACLE』外部表external table创建(11g)

[oracle@enmo1 ~]$ mkdir ext SQL> create directory dir_ext as '/home/oracle/ext'; Directory creat...
  • qq_38815172
  • qq_38815172
  • 2017-05-31 20:44:36
  • 177
收藏助手
不良信息举报
您举报文章:Excel “External table is not in the expected format.”
举报原因:
原因补充:

(最多只允许输入30个字)