Reading Excel using OLEDB

[Rohit]

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.G. Ryan - MVP]

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]

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

 

[Paul Clement]

¤ 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)