As I am currently looking into ways in which I could automate dataloading from multiple file formats using stored procedures. I stumbled accross OPENROWSET, which proves to have more than what i bargined for with being able to support multple file types which include (access, Excel, csv, text)
So for this post I am looking into the Excel data loading. I know what you are all thinking, that there are many more better ways out there to load data into a sql table than using OPENROWSET, but I am Limited to using Sql server 2000 and creating a stored procdure like below allows the importer to have more flexibility that if i was to say use a DTP package or other build in import/export functions.
So below is a script for a stored procedure that helps handle the OPENROWSET function. With only 3 parameters to specify, inorder to load an excel file/sheet into a sql table.
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
-- sets default sheet name to Sheet1 if not specfied
if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]'
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 + ')')