4

i had a problem .... i have and old excels files and i want to save them into SQL database ... MY Q is : if i want to do for example in .xls from (C16:C28) want to take it and put it into table1 in field NAMES .. how i can write it in query ?

note am using Microsoft SQL Server Management .. and try to get all my xls and import it into data base via sql query take from those files data and put it into my database ..

INSERT INTO [table1] (Names) VALUES ('&C16&') to ('&C28&')

is this true ? idk how to write it .. need help

NewStudent
  • 137
  • 1
  • 2
  • 8
  • If it's a once off you can use the [Import and Export Wizard](http://msdn.microsoft.com/en-us/library/ms140052.aspx) to do this for you. [If you don't mind using your intuition/Google, then it's pretty straightforward](http://www.mindfly.com/blog/wp-content/uploads/images/sqlImportExport.jpg). – ta.speot.is Dec 26 '12 at 09:01
  • There is a similar question on StackOverflow that shows how to connect to and select from an Excel spreadsheet [SO Question on Excel selects](http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database) For reference on connection strings to Excel see also: [Excel connection strings](http://www.connectionstrings.com/excel) and here on Microsoft.com [Excel selects from SQL](http://support.microsoft.com/kb/321686) – Richard Vivian Dec 26 '12 at 08:53
  • but my Q is not whole column .. no i want specific field in range .. – NewStudent Dec 26 '12 at 09:04
  • If you are looking for a specific range, try to name the range in Excel. Then connection string refers to the Named data range. See the Microsoft article link in my answer above. – Richard Vivian Dec 26 '12 at 09:09

1 Answers1

2

When you use openrecordset you can specify a range [Sheet1$C16:C28] on the table in the query parameter

INSERT INTO [table1] (Names)
SELECT *
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 
  'Excel 8.0;Database=C:\Foo.xls;HDR=NO', 
   'select * from [Sheet1$C16:C28]') AS t
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • INSERT INTO always come with VALUES .. try to check again on ur code .. i feel there somthing wrong :$ what is T in the last for ? – NewStudent Dec 26 '12 at 09:11
  • 1
    @NewStudent INSERT doesn't always come with VALUES. See the section "Using the SELECT and EXECUTE options to insert data from other tables" in the [online help](http://msdn.microsoft.com/en-us/library/ms174335(v=sql.110).aspx). `as t` is there because OPENROWSEST create a derived table. SQL Server likes to have aliases for derived tables but you can try and drop it and see if it still works. – Conrad Frix Dec 26 '12 at 09:17