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 $!!!

 

Leave a Reply

Your email address will not be published.