Creating a dataset from an Excel File

[Guest]

I am having trouble with getting all data back in a dataset from the excel
spread sheet.

Each column is named, however I am finding if the first value under the
named column is not populated the rest of the values are ignored within that
column. I would like to know if this is a bug or a problem with my approach.

The code that is generating the dataset is as follows:

Dim ds As DataSet
Dim myCommand As OleDbDataAdapter
Dim myConnection As OleDbConnection

myConnection = New OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & FileLocationTextBox.Text & _
"; Extended Properties=Excel 8.0;")


Dim row As DataRow

' Select the data from header sheet of the workbook.
myCommand = New OleDbDataAdapter( _
"select * from [" & "Sheet1" & "$]", myConnection)
ds = New DataSet


myCommand.Fill(ds)
DataGrid1.DataSource = ds

 

[Val Mazur]

Hi Stewart,

Since Excel could keep different types of data in a same column, then
sometimes provider does not recognize proper datatype of the entire column
and just sets all the values to NULL. What you could do is to force Jet
OLEDB provider to treat each column as a string datatype and it should load
everything, but you would need to have a deal with the strings only. To
force provider to do this you need to add IMEX=1 to your extended properties
of the connection

myConnection = New OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & FileLocationTextBox.Text & _
"; Extended Properties=""Excel 8.0;IMEX=1""")