Reading Excel using OLEDB

When I read excel file sheet as table through ADO.NET using OLEDB
it does not return all
data. I know that it scans 8 rows for deciding about datatype and it can be
changed to 16 rows. I changed it to 16 rows but still does not work. I know
what is happening, it finds the column name which obviously string type on
8th column in my file and probably it guess the columns as string type..
After that I have all numeric data for that column. It does not return all
these data Is there
any work around ?

W
Wed Oct 26 11:30:30 CDT 2005

Rohit - just to be clear, it is returning some of the column correct? If
you wouldn't mind, post the code if you can and hopefully we can nail it
from there.

HTH,

Bill
"Rohit" <rohits@nagarro.com> wrote in message
news:efuCpPk2FHA.3136@TK2MSFTNGP09.phx.gbl...
> When I read excel file sheet as table through ADO.NET using OLEDB
> it does not return all
> data. I know that it scans 8 rows for deciding about datatype and it can
> be
> changed to 16 rows. I changed it to 16 rows but still does not work. I
> know
> what is happening, it finds the column name which obviously string type on
> 8th column in my file and probably it guess the columns as string type..
> After that I have all numeric data for that column. It does not return all
> these data Is there
> any work around ?
>
>

Rohit
Wed Oct 26 11:41:54 CDT 2005

Hello Ryan,

Thanks for your reply.

Actually what I am doing is , I am reading an Excel file using OLEDB.
Now the first row in the excel file is column names and then like 17 rows
are blank values.
After the 18th row, I Have the data upto 1000 rows.

Now , the problem I am facing is that Excel identifies only the first 8 rows
.So Now I am getting all the row values as NULLin the table since the first
17 rows are NULL values.

I hope I made myself clear.Please Let me Know

Regards,
Rohit
"W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> wrote in message
news:eTkWQqk2FHA.3244@tk2msftngp13.phx.gbl...
> Rohit - just to be clear, it is returning some of the column correct? If
> you wouldn't mind, post the code if you can and hopefully we can nail it
> from there.
>
> HTH,
>
> Bill
> "Rohit" <rohits@nagarro.com> wrote in message
> news:efuCpPk2FHA.3136@TK2MSFTNGP09.phx.gbl...
>> When I read excel file sheet as table through ADO.NET using OLEDB
>> it does not return all
>> data. I know that it scans 8 rows for deciding about datatype and it can
>> be
>> changed to 16 rows. I changed it to 16 rows but still does not work. I
>> know
>> what is happening, it finds the column name which obviously string type
>> on
>> 8th column in my file and probably it guess the columns as string type..
>> After that I have all numeric data for that column. It does not return
>> all
>> these data Is there
>> any work around ?
>>
>>
>
>

Paul
Wed Oct 26 13:09:18 CDT 2005

On Wed, 26 Oct 2005 22:11:54 +0530, "Rohit" <rohits@nagarro.com> wrote:

¤ Hello Ryan,
¤
¤ Thanks for your reply.
¤
¤ Actually what I am doing is , I am reading an Excel file using OLEDB.
¤ Now the first row in the excel file is column names and then like 17 rows
¤ are blank values.
¤ After the 18th row, I Have the data upto 1000 rows.
¤
¤ Now , the problem I am facing is that Excel identifies only the first 8 rows
¤ .So Now I am getting all the row values as NULLin the table since the first
¤ 17 rows are NULL values.
¤

Are you using the IMEX argument in your connection string?

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""


Paul
~~~~
Microsoft MVP (Visual Basic)