update SQl table from values in excel

0
votes

I am using the SQL Developer or SQl express.

How do i get the values from an excel sheet and update those in a column of my database...

Please help thanks.

i have this and im running it but i get error:

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;Database=C:\books.xls',
            'SELECT * FROM [Sheet1$]')

i get error now

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".

thanks

2
sqlsql-server-2005exceloracle-sqldeveloper
And what error might you be getting? - HLGEM
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. - user175084
Have you checked out the Surface Area Configuration tool as the error indicates? - Phil Hunt
the problem is i cannot find the Surface Area Configuration tool - user175084
See: technet.microsoft.com/en-us/library/ms187569.aspx - Fionnuala

2 Answers

1
votes

Do you have SQL Server Management Studio Express? If so, try using the Import/Export Wizard to import the spreadsheet into a new table.

1
votes

THIS IS HOW MY CODE WORKS

INSERT INTO TEMPTABLE (name)
 SELECT * 
   FROM OPENROWSET(
                   'Microsoft.Jet.OLEDB.4.0', 
                   'Excel 8.0;IMEX=1;HDR=NO;DATABASE=C:\test.xls', 
                   'Select * from [Sheet1$]'
                  );

i hope this helps some1