title: Stored procedure to bulk import Excel sheet data into SQL Server database table
body: <pre><code>alter procedure _sp_ImportExcelFile            
              (@Source varchar(1000)
              , @SourceSheet varchar (100)
              , @DestinationTable varchar (100))
  as
     declare @retval int
  
     EXEC master..xp_fileexist @Source, @retval output -- check if file exists
  
     if @retval = 0
              begin
                          print 'file does not exist.'
                          return
              end
  
     if @SourceSheet is null or @SourceSheet = ''
              set @SourceSheet = '[projects_export$]' 
     else
              set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'
  
     if @DestinationTable is null or @DestinationTable = ''
              set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)
  
     exec('select * into [' + @DestinationTable + '] from openrowset
  (''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @Source + ''', ' + 
  @SourceSheet + ')')
  </code></pre>
  
  <p>and I am using this as execute statement</p>
  
  <pre><code>exec _sp_ImportExcelFile 'D:\exceldata\','projects_export','ExcelData'
  </code></pre>
  
  <p>where <code>projects_export</code> is the name of the sheet and <code>ExcelData</code> is the name of my table
  but get the error as <code>file doesn't exist</code></p>