Loading an Excel file into a DataSet

[Guest]

Hello eveyone,

I have the below code which is trying to load an Excel file into a DataSet,
however it errors at the oAdapter.Fill(ds); line with An unhandled exception
of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll. Can
anyone spot the a reason why. or help with things that I could look at?

Thamks all,

OleDbConnection objConn=new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand oCmd=new OleDbCommand("SELECT * FROM [ZREPSLET$]", objConn);
OleDbDataAdapter oAdapter=new OleDbDataAdapter();
oAdapter.SelectCommand = oCmd;

DataSet ds=new DataSet();
oAdapter.Fill(ds);

 

[Grzegorz Danowski]

Hello eveyone,

I have the below code which is trying to load an Excel file into a
DataSet,
however it errors at the oAdapter.Fill(ds); (...)

oAdapter.SelectCommand = oCmd;

DataSet ds=new DataSet();
oAdapter.Fill(ds);

Check details of the exception, for example add try and catch:

try
{
oAdapter.Fill(ds);
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Message);
}

Regards,
Grzegorz

 

[Bernie Yaeger]

Hi Jon,

First of all, what does sconnectionstring look like?

You might want to try something like this, or modify your connection string
to replicate it:
Dim oconnex As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=c:\my documents\test_.xls;" & _

"Extended Properties=""Excel 8.0;HDR=Yes""")

Try

oconnex.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New OleDbCommand("select * from [sheet1$]", oconnex)

Dim oda As New OleDbDataAdapter(ocmd)

Dim ods As New DataSet("test file")

oda.Fill(ods, "test file")

Dim irow As DataRow

For Each irow In ods.Tables(0).Rows

If Not IsDBNull(irow(0)) Then

MessageBox.Show(irow(0))

Else

MessageBox.Show("null value")

End If

If Not IsDBNull(irow(1)) Then

MessageBox.Show(irow(1))

Else

MessageBox.Show("null value")

End If

Next

oconnex.Close()

HTH,

Bernie Yaeger

 

[Val Mazur]

Hi,

Like for me code looks fine unless your code did not instantiate DS
variable. Did you do this using New?

 

[Guest]

All,

Thanks for your replies. I now have it working. The excel file had 2 empty
columns in, I remove these and it works great.

Thanks again,

Jon