Importing data from excel is very easy and you have several options to get you started…
- Option 1: Right-click the database name in object explorer, choose tasks, choose “Import Data…” then follow the prompts.
- Option 2: Try this code:
- select * into #temp from OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0; Database=c:\itd.xls; HDR=YES’,’select * from [IT$]’);
- Notice in the above code there is a “$” following the “IT”. “IT” is the name of the excel sheet. If your excel sheets don’t have names, you should put “[sheet1$]”. Notice the sheet name is enclosed in square brackets [].
- The above example has the Excel file on the C drive of the server hosting the database you are running the query on.
- Option 3: Alternative code method employing linked server (for repetitive query work):
- Exec sp_addlinkedserver ‘ImportData’,’Jet 4.0′, ‘Microsoft.Jet.OLEDB.4.0’, ‘c:\itd.xls’, NULL, ‘Excel 8.0’;
- Select * from ImportData…[IT$]
- Notice in the above code there is a “$” following the “IT”. “IT” is the name of the excel sheet. You have to add the $!!!