When I create an application, I always ask my client to give me a sample data for testing my application. Almost all my client give it to me on excel file. To copy the data from excel to SQL server, what I always do was to create a new DTS package then connect to the server then connect to excel file then transform. So, when my client gave me tons of excel file, it would be a nightmare to do this over and over so I created this stored procedure to import the data from excel file to SQL Server.

create 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 = '[Sheet1$]' -- assume that the Sheet name on excel file is the default name
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 + ')')

---

Posted 11-21-2006 5:52 PM by clintz