Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How can I use MaxScanRows in C# source code to import the excel sh

1,090 views
Skip to first unread message

Kumar Goud@discussions.microsoft.com Manoj Kumar Goud

unread,
Oct 12, 2006, 2:06:02 AM10/12/06
to
when i imported excel sheet to datagrid some of values are ignored.If i
change the TypeGuessRows registry key value in path
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines to 0 then its working
fine .But it is not the right way to change the registry key.So I followed
alternative way.I used MaxScanRows in my source code .But it has no
effect.Can anyone please help me about this problem.Is there any way how to
use MaxScanRows property, so that it will work fine.I tried in different way
like MaxScanRows=0/MaxScanRows=FFFFFFFF but no effect.Please help me out so
that I ll be glad to u.

Paul Clement

unread,
Oct 12, 2006, 12:16:07 PM10/12/06
to
On Wed, 11 Oct 2006 23:06:02 -0700, Manoj Kumar Goud <Manoj Kumar Go...@discussions.microsoft.com>
wrote:

¤ when i imported excel sheet to datagrid some of values are ignored.If i

Try specifying the IMEX argument:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=e:\\My Documents\\Book2.xls;" +
"Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;


Paul
~~~~
Microsoft MVP (Visual Basic)

Manoj Kumar Goud

unread,
Oct 12, 2006, 11:48:01 PM10/12/06
to
Hi Paul,
thanks for giving some tips.I also used IMEX=1 but no result.Please see my
string what I used in my program.

private const string EXCEL_CONNECTION_STRING_1 =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

private const string EXCEL_CONNECTION_STRING_2 = ";Extended
Properties=\"Excel 8.0;;HDR=No;MaxScanRows=0;IMEX=1\"";

Is there any other changes in this string

Regards,
Manoj

Manoj Kumar Goud

unread,
Oct 12, 2006, 11:51:01 PM10/12/06
to
Hi Paul,
Thanks for giving some tips.I also used IMEX=1 still no effect.Please see my
connection string

private const string EXCEL_CONNECTION_STRING_1 =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

private const string EXCEL_CONNECTION_STRING_2 = ";Extended
Properties=\"Excel 8.0;;HDR=No;MaxScanRows=0;IMEX=1\"";

Is there any modification or other property ,I have to add to work properly
Thanks

TWG

unread,
Aug 6, 2009, 8:50:37 PM8/6/09
to
Setting "MaxScanRows=0" is the 'correct' way to address this problem.
But this connection string property is ignored/not implemented in Jet 4.0

See the MS Knowledge base:

"http://support.microsoft.com/kb/189897/EN-US/"

There is a workaround (as noted above):

You have to change a registry key to the correct zero value, but note that you will see a performance hit as the full table will be scanned.

The Excel ODBC driver uses the TypeGuessRows DWORD value of one of the following registry key to determine how many rows to scan in your data:

* Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
* Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Set the value to Zero.


From http://www.developmentnow.com/g/22_2006_10_0_0_833712/How-can-I-use-MaxScanRows-in-C-source-code-to-import-the-excel-sh.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

0 new messages