Posted by lonestarprogramming on 05/04/06 01:34

I would like to create a stored procedure that imports an Excel file to
a sql server table. I need to be able to pass the path to the
spreadsheet to the stored procedure like so:

CREATE PROCEDURE dbo.ImportSpreadsheet

(
@Path nvarchar(120)

)

AS

SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ' + @Path + ';Extended Properties=Excel 8.0')...Audit$


I will call the stored procedure from within my .NET application and
pass it the path as @Path. The path will be chosen by the user at run
time and will be something similar to
C:\Spreadsheets\Audits.xls.

I can make this run in the Query Analyzer:
SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ''C:\Spreadsheets\Audits.xls\Audit_TC.xls'';Extended
Properties=Excel 8.0')...Audit$

I could run the SQL directly from my app, but I like to use stored
procs whenever I can for the speed and flexibility. I can change the
stored proc and not have to recompile/reinstall the app.

Any help would be greatly appreciated.

• Next in forum: Re: How to do an equivelent First() operation
• Prev in forum: Re: Date creation in SQL
• Thread view: Use variable for datasource in Opendatasource

[Reply to this message]