OPENROWSET/OPENDATASOURCE with Excel 2007/SQL 2005
in [Programming SQL]
|
Prev: using "USE dbname" before ALTER DATABASE
Next: Script to check the Syntax on all stored procedures in a database
From: Thomas G on 6 Nov 2009 11:28 Hi I have been having the same issue and found a solution for reading Data in from Excel Files. To achive this, The Sheet name in Excell must not have spaces, The Directory the file is in must be accessible to the SQL Server Service user Account The 'Ad Hoc Distributed Queries' Advanced SQL Config option must be enables USE: [b]SP_CONFIGURE 'show advanced options',1 RECONFIGURE WITH OVERRIDE SP_CONFIGURE 'Ad Hoc Distributed Queries',1 RECONFIGURE WITH OVERRIDE[/b] Then Create the following Stored Procedure in your DB: [code] CREATE PROC stp_ReadXLS @file varchar(1000) ,@template varchar(300) ,@hashtable varchar(300) = 'loader_table' ,@excell_version varchar(2) = '8' AS DECLARE @SQL_T varchar(4000) EXECUTE AS login ='Sup_sp_exec' Begin Try SET @SQL_T = 'SELECT * INTO ##'+@hashtable+' FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) ' EXEC (@SQL_T) END TRY BEGIN Catch SET @SQL_T = 'INSERT INTO ##'+@hashtable+' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) ' EXEC (@SQL_T) END Catch PRINT ('Populated ##'+@hashtable+' table.....') [/code] The Stored Proc Will read in the file and create a ## TABLE with the Contents. IF you Run the SP again, it will ADD new records to the same table, so that you could collate multiple XLS files into one. Also, you have the option to Work with Newer and older Excel files by providing the @excell_version parameter with the version number. DON'T FORGET TO DROP THE ## TABLE AFTER YOU ARE FINISHED. Hope this helps. From http://www.developmentnow.com/g/113_2007_7_0_0_998417/OPENROWSETOPENDATASOURCE-with-Excel-2007SQL-2005.htm Posted via DevelopmentNow.com Groups http://www.developmentnow.com/g/
From: Thomas G on 6 Nov 2009 11:29
Hi I have been having the same issue and found a solution for reading Data in from Excel Files. To achive this, The Sheet name in Excell must not have spaces, The Directory the file is in must be accessible to the SQL Server Service user Account The 'Ad Hoc Distributed Queries' Advanced SQL Config option must be enables USE: [b]SP_CONFIGURE 'show advanced options',1 RECONFIGURE WITH OVERRIDE SP_CONFIGURE 'Ad Hoc Distributed Queries',1 RECONFIGURE WITH OVERRIDE[/b] Then Create the following Stored Procedure in your DB: [code] CREATE PROC stp_ReadXLS @file varchar(1000) ,@template varchar(300) ,@hashtable varchar(300) = 'loader_table' ,@excell_version varchar(2) = '8' AS DECLARE @SQL_T varchar(4000) EXECUTE AS login ='Sup_sp_exec' Begin Try SET @SQL_T = 'SELECT * INTO ##'+@hashtable+' FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) ' EXEC (@SQL_T) END TRY BEGIN Catch SET @SQL_T = 'INSERT INTO ##'+@hashtable+' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel '+@excell_version+'.0;Database='+@file+''', ['+@template+']) ' EXEC (@SQL_T) END Catch PRINT ('Populated ##'+@hashtable+' table.....') [/code] The Stored Proc Will read in the file and create a ## TABLE with the Contents. IF you Run the SP again, it will ADD new records to the same table, so that you could collate multiple XLS files into one. Also, you have the option to Work with Newer and older Excel files by providing the @excell_version parameter with the version number. DON'T FORGET TO DROP THE ## TABLE AFTER YOU ARE FINISHED. Hope this helps. From http://www.developmentnow.com/g/113_2007_7_0_0_998417/OPENROWSETOPENDATASOURCE-with-Excel-2007SQL-2005.htm Posted via DevelopmentNow.com Groups http://www.developmentnow.com/g/ |