Cannot import Excel data

Printable View

Nov 24th, 2009, 08:52 AM
LuxCoder

Cannot import Excel data

I am getting this error :

Quote:

Could not find installable ISAM.

While trying to import my excel data into sql express database using following:

Code:

Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=d:/test/Places.xls;Extended Properties=Excel 8.0;") excelConnection.Open() Dim excelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Locations [OBDC; Driver={SQLServer};Server=(local);Database=D:\PROJECTMW\MACHINEWALE\MACHINEWALE\DATABASEMW.MDF;Trusted_Connection=yes].[Places]SELECT * FROM [Place$];", excelConnection) excelCommand.ExecuteNonQuery() excelConnection.Close()

Any suggestions?

Lux.
Nov 24th, 2009, 09:07 AM
stanav

Re: Cannot import Excel data

Code:

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=d:/test/Places.xls

I'm not sure that this is the problem, but shouldn't the path separator be "\" instead?
Nov 24th, 2009, 09:12 AM
LuxCoder

Re: Cannot import Excel data

Done that! Still getting same error :(
Nov 24th, 2009, 11:57 AM
stanav

Re: Cannot import Excel data

This looks very suspicious to me:

Code:

Dim excelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Locations [OBDC; Driver={SQLServer};Server=(local);Database=D:\PROJECTMW\MACHINEWALE\MACHINEWALE\DATABASEMW.MDF;Trusted_Connection=yes].[Places]SELECT * FROM [Place$];", excelConnection)

I don't know if it's OK to insert new records that way since I've never done it like that (and never seen it's being done like that either). What I normally do is to pull the data from excel into a dataset/datatable, and then insert the records from the dataset/datatable to the database in a separate step.
Nov 24th, 2009, 11:43 PM
LuxCoder

Re: Cannot import Excel data

ok.. can u give me an example?
Nov 25th, 2009, 02:41 AM
LuxCoder

Re: Cannot import Excel data
After some modifications i used the following command to import:
vb Code:
```
SELECT  Slno, State, District, City, Village, pincode
INTO       [D:\PROJECTMW\MACHINEWALE\MACHINEWALE\databasemw.mdf].locations
FROM     OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Test\Places.xls;IMEX=1', 'SELECT * FROM [Place$]') AS derivedtbl_1
```
But i got the following error:

Quote:

"The specified schema name "D:\PROJECTMW\MACHINEWALE\MACHINEWALE\databasemw.mdf" either does not exist or you do not have permission to use it."

what to do now?