Excel into oledb dataset

[hog]

Is it possible to import an Excel worksheet into a oledb dataset?

 

I do not need to manipulate Excel at all I just need to get the data out of the .xls file into my app:confused:

[hog]

Err yep yep you can:)

 

simply enter 'reading excel into dataset' in the VS 2003 help and an example appears :):)

[hog]

Mmm

 

If I use this per the VS example:

 

MyConnection = New System.Data.OleDb.OleDbConnection( _
             "provider=Microsoft.Jet.OLEDB.4.0; " & _
             "data source=C:\returns.XLS; " & _
             "Extended Properties=Excel 8.0;")

 

I get an error saying the table is in an unexpected format.

 

Any ideas??

[dynamic_sysop]

not sure if the oledb will easily allow excel or not , but maybe you could try referencing the Microsoft Excel object library ( 10.0 or what version you have ) then you can do the following to open a xls file and read from it , easily :

Imports Excel
'/// as usual at the top of your form's code window.
'////
   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
       Dim objExcel As New Excel.Application()
       Dim objBook As Excel.Workbook
       Try
           objBook = objExcel.Workbooks.Open("C:\Book1.XLS")
           Dim x As Integer
           For x = 1 To objBook.Sheets.Count
               MessageBox.Show(objBook.Sheets("Sheet1").Cells(1, x).Value)
           Next
           objBook.Save()
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       Finally
           objBook.Application.Quit() '/// just to make sure excel dont hang in the memory
           objExcel.Application.Quit() '/// we make these get closed last ( after any possible errors )
           objBook = Nothing
           objExcel = Nothing
       End Try

   End Sub

hope it helps a bit :)

[hog]

Thnx DS, I'll keep your method as a backup but have discovered this:

 

We have a corporate system that exports data to Excel. Excel opena automatically with the data displayed.

 

If you close Excel no propmts are given.

 

If I run my ealier code I get this error:

 

'yadda.xls may contain features that are not compatible with text (tab delimited). Do you want to keep the workbook in this format.'

 

If I say yes I get the above error.

 

If I say no I am prompted for a file name which I supply then the code above works a treat.

 

What I can't suss is why?

[dynamic_sysop]

i've just connected to an xls file without trouble using oledb connection , here's how i connected , hope it helps:)

   Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
       Dim DBSet As System.Data.DataSet
       Dim DBCommand As System.Data.OleDb.OleDbDataAdapter
       Dim DBCon As System.Data.OleDb.OleDbConnection
       Try
           DBCon = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=C:\Book1.XLS;Extended Properties=Excel 8.0;")
           DBCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", DBCon)

           DBSet = New System.Data.DataSet()
           DBCommand.Fill(DBSet)
           DataGrid1.DataSource = DBSet.Tables.Item(0)'///put the excel info in to a datagrid.
       Catch ex As Exception
           MessageBox.Show(ex.Message)
       Finally
           DBCon.Close() '/// close the connection to excel.
           DBSet = Nothing
           DBCommand = Nothing
       End Try

   End Sub

[hog]

Yep, very similar to this:

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

       Dim DS As System.Data.DataSet
       Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
       Dim MyConnection As System.Data.OleDb.OleDbConnection

       MyConnection = New System.Data.OleDb.OleDbConnection( _
             "provider=Microsoft.Jet.OLEDB.4.0; " & _
             "data source=C:\returns.XLS; " & _
             "Extended Properties=Excel 8.0;")

       ' Select the data from Sheet1 of the workbook.
       MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
             "select * from [Returns$]", MyConnection)

       DS = New System.Data.DataSet

       Try

           MyCommand.Fill(DS)

       Catch ex As Exception

           MessageBox.Show(ex.Message)

       End Try

       MyConnection.Close()

   End Sub

 

if I create a brand new xls file from Excel 2002 the code works ok. If I get the export xls file from the corporate system that opens ok in excel 2002 the code fails. If I save the corporate xls file as stated above it works.

 

Question is what is so different about the corp xls file that when I open it make no changes and click save I get

 

'yadda.xls may contain features that are not compatible with text (tab delimited). Do you want to keep the workbook in this format.'

Edited July 4, 2003 by hog

[hog]

How do I attach a file on this posting? If I send your the xls file you will see what I mean:)

[dynamic_sysop]

np , there's an attatch file box down below where you type to post a message ( if you use the Post Reply normal way and not the box at the bottom of the topic ) , click browse and find yer file and it'll send with the message :)

[hog]

OKey dokey:)

 

here's the file.

 

If you run the code on it first you will get the error "external table not in expected format"

 

Then open the file in Excel, click Save and say No to keep format and save the file back as itself.

 

Run the code again and it works :(

 

Thnx

returns.zip

[dynamic_sysop]

the excel file was corupt ( invalid chars ) i've fixed it and got it to load fine using oledb :)

here ya go

returns.zip

[hog]

Aha, question is do you know what the corruption was?

 

My problem is this:

 

The user needs to use the corp system to export the data to Excel, I think the corp system was written in VB?

 

As the export autmatically opens Excel with the data showing they will say it's OK. Also if the user just closes the spreadsheet there is no problem.

 

If I know what the corruption is I may get them to sort it...BIG MAY.

 

I also think the corp system is pre Excel 2002, may Excel 97 or 95?

 

I have tried using Extended Properies of 5.0 and 3.0 with no joy:(

 

Thnx

Edited July 4, 2003 by hog

[dynamic_sysop]

a few of the cells where showing as ##### , when i double clicked them they reverted to dates, i then clicked save and chose "no" on the options "do you want to keep the format the same" ( the "no" option said something about making it the correct format for excel 97 / 2000 )

[hog]

OK don't that was corruption as dates are displayed as # when the cell is not wide enough.

 

There I need to find out what version they are exporting it in:)

 

Thnx

[farshad]

imports

 

Having added a reference to the excel object version 9.0 in the com tab, then is there still a requirement to have imports Excel on the top of the code?

Thanks

[Sonde]

do you know how to read all Data from a Workbook

 

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _

"select * from [../Test.xls$]", MyConnection)

[hog]

farshad & sonde place your questions in a new thread :)

[CyberHawke]

Hey guys, I just found this thread in a search for something else. I work with Excel spreadsheets all the time using ADO.NET, and previously using ADO COM. I regularly read and compare data from sheets, and also submit updates as well as execute DDL statements to manipulate the structure of the sheet. If you have any questions, on data manipulation in Excel using ADO, feel free to ask any time.

 

John

[AlexCode]

I know this is an old thread but I'm having this problem that perfectly maches this thread...

 

I can successfully read an EXCEL file into a DataSet, every thing is fine... but... When I'm importing data from several xls files I don't know the name of the sheet or sheets...

 

So, is there a way I can do a similar SELECT statement so it retrieves the name of the sheets? Or I have to use interop just to do this simple task...

 

Or else... is there a way of referencing the sheet by its order number or something like that??

 

I just need a way to reference a sheet without knowing its name... a way to make it general...

 

 

Thanks,

 

Alex :D

Edited November 14, 2003 by AlexCode