Excel Worksheet as Datasource..

[JaedenRuiner]

In Access (through ODBC I think) there was a way to add an "external link" to an excel file, temporarily treating it like a database datasource, which allowed on the privilege of using SQL statements to transfer data, such as an INSERT INTO () SELECT () method, granted the excel file either had a header row or you could directly reference the columns numerically.

Is there a way to do this within the VB.Net environment?

Basically I have my DB and an Excel file (user selected at run time, but will always be formatted the same). I want to take 6 columns from teh excel file and import them into a db table for processing via DB commands instead of having to manipulate them through the COM Interop assemblies. Granted I will probably still need the PIAs for the initial access to the excel file, but I'd rather not have to do:

  dim x as Excel.Worksheet
  for i = 0 to x.rowcount
    MyTableAdapter.Insert(x.rows(i).cells(4).Value, x.rows(i).cells(7).value)
  next

That seems very slow and clunky. I'd much rather do:

  dim sql as new SqlCeCommand("", MyConn)
  
  sql.commandText = " Insert Into MyTable (col1, col2, col3) select Col4, col7, col11 from MyExcelTable;"
  sql.ExecuteNonQuery()

Or something of the like. I know that is not precise syntax so please don't correct my pseudo code, i'm just looking for a way to attach the excel worksheet temporarily as a db (whether via ODBC or some other linkage)

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[MattP]

Here's an example using JET.

http://www.vbdotnetheaven.com