read excelfile with ADO

[steven]

Hello,

I'm using ADO to read an excelfile in a datagrid as follows:

Dim cnn As NewOleDb.OleDbConnection("Provider=Microsoft.Jet.O LEDB.4.0;
Data Source=" & filename & ";Extended Properties=""Excel
8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim dt As New DataTable("excel")
da.Fill(dt)
Datagrid1.datasource = dt
This works fine. But when a certain column on the second row (the first
row are the headers) contains an integer, then ADO assumes that this
whole column contains integers (except for the header offcourse). And
for each value that is not an integer, ADO replaces the string with the
DBNull value.

What am I doing wrong? Or is this a known bug? Are there any workarounds?

Thanks for the help,

Steven

[Kerry Moorman]

Steven,

Here is a url that explains your options:

http://www.sqldts.com/default.aspx?254

Kerry Moorman
"steven" wrote:

Hello,

I'm using ADO to read an excelfile in a datagrid as follows:

Dim cnn As NewOleDb.OleDbConnection("Provider=Microsoft.Jet.O LEDB.4.0;
Data Source=" & filename & ";Extended Properties=""Excel
8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim dt As New DataTable("excel")
da.Fill(dt)
Datagrid1.datasource = dt
This works fine. But when a certain column on the second row (the first
row are the headers) contains an integer, then ADO assumes that this
whole column contains integers (except for the header offcourse). And
for each value that is not an integer, ADO replaces the string with the
DBNull value.

What am I doing wrong? Or is this a known bug? Are there any workarounds?

Thanks for the help,

Steven

[steven]

Thank you very much !! Just adding "IMEX=1" to my connectionstring did
the job!!

Steven

Kerry Moorman schreef:

Steven,

Here is a url that explains your options:

http://www.sqldts.com/default.aspx?254

Kerry Moorman