read excelfile with ADO

24 Mar 2006 3:14 PM

steven

Hello,

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

Dim cnn As NewOleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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

24 Mar 2006 3:45 PM

Kerry Moorman

Steven,

Here is a url that explains your options:

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

Kerry Moorman

Show quoteHide quote

"steven" wrote:

> Hello,
>
> I'm using ADO to read an excelfile in a datagrid as follows:
>
> Dim cnn As NewOleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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
>

24 Mar 2006 4:18 PM

steven

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

Steven

Kerry Moorman schreef:
Show quoteHide quote

> Steven,
>
> Here is a url that explains your options:
>
> http://www.sqldts.com/default.aspx?254
>
> Kerry Moorman