Importing data from Excel

Importing data from excel is very easy and you have several options to get you started…

1. Option 1:  Right-click the database name in object explorer, choose tasks, choose “Import Data…” then follow the prompts.
2.  Option 2:  Try this code:
   1. select * into #temp from OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0; Database=c:\itd.xls; HDR=YES’,’select * from [IT$]’);
   2. 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 [].
   3. The above example has the Excel file on the C drive of the server hosting the database you are running the query on.
3. Option 3: Alternative code method employing linked server (for repetitive query work):
   1. Exec sp_addlinkedserver ‘ImportData’,’Jet 4.0′, ‘Microsoft.Jet.OLEDB.4.0’, ‘c:\itd.xls’, NULL, ‘Excel 8.0’;
   2. Select * from ImportData…[IT$]
   3. Notice in the above code there is a “$” following the “IT”. “IT” is the name of the excel sheet. You have to add the $!!!