convert Excel to dataset

[karups]

Hi
when i convert Excel file to dataset using the following code, i find
that, some col. such as

Col1
------
404
403
NOT
222
is converted as

col1
-----
404
403
<null>
222

..I use this code to convert
Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"data source='" + SaveLocation + " '; " + "Extended Properties='Excel
8.0;IMEX=1;'")
Dim da As New OleDbDataAdapter("select * from
[Sheet1$]", cnn)
da.TableMappings.Add("Table", "TableCom")
Dim ds As New DataSet
da.Fill(ds)

Please help me in this

[Paul Clement]

On 4 Aug 2006 06:06:40 -0700, "karups" <al***********@gmail.comwrote:

¤ Hi
¤ when i convert Excel file to dataset using the following code, i find
¤ that, some col. such as
¤
¤ Col1
¤ ------
¤ 404
¤ 403
¤ NOT
¤ 222
¤
¤
¤ is converted as
¤
¤ col1
¤ -----
¤ 404
¤ 403
¤ <null>
¤ 222
¤
¤ .I use this code to convert
¤ Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
¤ "data source='" + SaveLocation + " '; " + "Extended Properties='Excel
¤ 8.0;IMEX=1;'")
¤ Dim da As New OleDbDataAdapter("select * from
¤ [Sheet1$]", cnn)
¤ da.TableMappings.Add("Table", "TableCom")
¤ Dim ds As New DataSet
¤ da.Fill(ds)
¤

The IMEX argument should have taken care of the mixed mode column issue.

You could also try changing the TypeGuessRows setting in the registry to 0. It is found at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

I'm assuming your connection string is being evaluated properly but you may want to try double
quotes instead of single quotes:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SaveLocation & ";Extended Properties=""Excel 8.0;IMEX=1"""
Paul
~~~~
Microsoft MVP (Visual Basic)

[karups]

Paul Clement wrote:

On 4 Aug 2006 06:06:40 -0700, "karups" <al***********@gmail.comwrote:

¤ Hi
¤ when i convert Excel file to dataset using the following code, i find
¤ that, some col. such as
¤
¤ Col1
¤ ------
¤ 404
¤ 403
¤ NOT
¤ 222
¤
¤
¤ is converted as
¤
¤ col1
¤ -----
¤ 404
¤ 403
¤ <null>
¤ 222
¤
¤ .I use this code to convert
¤ Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
¤ "data source='" + SaveLocation + " '; " + "Extended Properties='Excel
¤ 8.0;IMEX=1;'")
¤ Dim da As New OleDbDataAdapter("select * from
¤ [Sheet1$]", cnn)
¤ da.TableMappings.Add("Table", "TableCom")
¤ Dim ds As New DataSet
¤ da.Fill(ds)
¤

The IMEX argument should have taken care of the mixed mode column issue.

You could also try changing the TypeGuessRows setting in the registry to 0. It is found at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel

I'm assuming your connection string is being evaluated properly but you may want to try double
quotes instead of single quotes:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SaveLocation & ";Extended Properties=""Excel 8.0;IMEX=1"""
Paul
~~~~
Microsoft MVP (Visual Basic)

ya that worked for me now
Thats great
I changed in the registry and its fetching the records correctly..
Thank u paul