无法使用Microsoft.ACE.OLEDB.12.0在c#中从Excel中读取所有行 [英] Not able to read all row from Excel in c# with Microsoft.ACE.OLEDB.12.0

问题描述

我有一个excel文件，其中有10多个缺少记录，
我正在使用Ado点网来阅读此excel(Microsoft.ACE.OLEDB.12.0)

当我通过C#读取此文件时，我仅获得(或读取)2.4万行(用于批量插入)


当我将其托管在我的本地系统(IIS 6)(通过使用"Localhost")上时，我能够读取所有行(10个缺少行)

如果我在我的主服务器(IIS 7)上托管相同的文件，则我只能读取24000行

当我尝试上传后将EXCEL转换为CSV然后又将CSV转换为EXCEL时，我可以读取31000行(行数没有增加)



谁能帮我解决这个问题


1我用于读取excel的连接字符串是

I have one excel file with more than 10 lacks records,
I am using Ado dot net for read this excel (Microsoft.ACE.OLEDB.12.0)

when i am reading this file through C# then i am getting(or reading) only 24 thousand rows (for Bulk Insert)


when I am hosting this on my local system (IIS 6)(by using "Localhost") ,I am able to read all rows(10 lacks rows)

if i am hosting the same on my main server (IIS 7), then i am able to read only 24 thousand rows only

When I CONVERTED EXCEL TO CSV THEN AGAIN CSV to EXCEL after i tryied to upload then I can read 31 thousand rows(no of row increased)



Could any one please help me to solve this problem


1 My connection string for reading excel is

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0;\"";

2

2

OleDbCommand xlCmd = new OleDbCommand("SELECT * FROM [" + sheetName + "]", xlCon);
            xlCmd.CommandType = CommandType.Text;
            OleDbDataReader xlReader = xlCmd.ExecuteReader();

3我的超时时间超过6000

已添加CODE标签-LOSMAC [/EDIT]

3 my time out more than 6000

CODE tags added - LOSMAC[/EDIT]

推荐答案

已从评论中移出
这非常有趣...我正在处理多个文件，文件大小在40-125 MB之间.您使用IMEX，因此，请参阅 connectionstrings.com 网站上的注释:
将数据作为文本处理(...)当您要将文件中的所有数据都视为文本时，请使用此选项，以覆盖Excels列类型常规"以猜测专栏.
如果您想将列标题读入结果集中(即使有标题也要使用HDR = NO)并且列数据是数字，请使用IMEX = 1以避免崩溃.

始终使用IMEX = 1是检索混合数据列数据的一种更安全的方法.考虑以下情况:一个Excel文件可能会正常工作，原因是该文件的数据导致驱动程序猜测一种数据类型，而另一个包含其他数据的文件导致驱动程序猜测另一种数据类型.这可能会导致您的应用崩溃."

Moved from comment
It''s very interesting... I''m working with multiple files which size is between 40 - 125 MB. You use IMEX, so please, see a note from connectionstrings.com site:
"Treating data as text(...) Use this one when you want to treat all data in the file as text, overriding Excels column type "General" to guess what type of data is in the column.
If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file''s data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash."

这篇关于无法使用Microsoft.ACE.OLEDB.12.0在c#中从Excel中读取所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！