Loading XLS into Datagrid

[bobbyforhire]

Currently I am using a string to manually attach a csv to a datgrid. But now I would like to use a .XLS file but can't think of the connection string. Any ideas on how to change this code so it cna use XLS files?


Dim DataFile As FileInfo = New FileInfo("C:\ALL_CARDS.csv")
Dim cnCSV As OdbcConnection
Dim daCSV As OdbcDataAdapter
Dim dt As DataTable = New DataTable("ALL")
cnCSV = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & DataFile.Directory.FullName & ";")
daCSV = New OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", cnCSV)
daCSV.Fill(dt)
UltraGrid1.DataSource = dt
Me.UltraGrid1.DisplayLayout.Bands(0).Columns(0).CellActivation = Activation.Disabled

 

[jebenson]

For all your connection string needs:

http://www.connectionstrings.com/

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[bobbyforhire]

The Microsoft Jet database engine could not find the object 'xxx.xls'. Make sure the object exists and that you spell its name and the path name correctly

I'm not sure what i'm doing wrong here. I keep getting that error.

Dim DataFile As FileInfo = New FileInfo("C:\Price\xxx.xls")
Dim cnCSV As OdbcConnection
Dim daCSV As OdbcDataAdapter
Dim dt As DataTable = New DataTable("ALL")
' cnCSV = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & DataFile.Directory.FullName & ";")
cnCSV = New OdbcConnection("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & DataFile.Directory.FullName & ";")
daCSV = New OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", cnCSV)
daCSV.Fill(dt)
UltraGrid1.DataSource = dt
Me.UltraGrid1.DisplayLayout.Bands(0).Columns(0).CellActivation = Activation.Disabled

 

[bobbyforhire]

and the file actually exist in that location. I moved it thinking it was a security issue, but same results.

 

[bobbyforhire]

Anyone?

 

[jebenson]

If you want to use the Excel file, why are you still using the ODBC connection? Try using the actual Excel connection string from Connectionstrings.com:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[bobbyforhire]

I know you are pushing me in the right direction i just don't know how to use that connection string with my code to fill a datagrid....sorry

 

[bobbyforhire]

daCSV = New OdbcDataAdapter("SELECT * FROM [sheet1$]", cnCSV)


fixed my issue.

 

[jebenson]

Dim conn As OleDbConnection
Dim da As OleDbDataAdapter
Dim dt As DataTable
Dim ConnStr As String

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[red]C:\Price\xxx.xls[/red];Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

conn = New OleDbConnection(ConnStr)

da = New OleDbDataAdapter("Select * From [[red]Sheet 1[/red]]", conn)

dt = New DataTable

da.Fill(dt)

UltraGrid1.DataSource = dt

Note that in the connection string you just use the path and Excel file.

In the SQL command for the DataAdapter, use the name of the desired worksheet in the Excel file.

You'll probably have to play around with the connection a bit to get it to work.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!