Import Excel File (without OPENROWSET or BULK INSERT)

[G12Consult]

Hi,

I have the following SP which I am trying to use to import the contents of an excel file.

ALTER PROCEDURE [dbo].[spImportExcelFile]            
            (@Source varchar(1000)
            , @SourceSheet varchar (100)
            , @DestinationTable varchar (100))
AS
BEGIN
    SET NOCOUNT ON
   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 = '[Master$]' 
   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 + ')')

END

I am receiving the 'file does not exist' error. No records are being added to the table.

To execute I am using

exec spImportExcelFile 'C:\Users\Andrew\Desktop\Junior_DB.xls','Master','ExcelData'

With 'master' being the sheet name and 'ExcelData' being the table

 

[gmmastros]

If the file is on your computer, and SQL server is running on another computer, then this makes perfect sense to me.

You see, the stored procedure is running on the server, so it is looking for the file on the server's C drive, not yours.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

With 'master' being the sheet name and 'ExcelData' being the table

Hmmmmmm?

Been querying Excel “tables” for well over a decade.

In most cases the table name is the sheet name appended with $ as master$ when
1) there is only one table on the sheet
2) no other data is on the sheet
3) the table has headings in row 1.

In cases where a table has been given an explicit Range Name, as opposed to a Range Name defined by a formula, that Range Name can be used.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[G12Consult]

Sorry I my execute code is with the $ sign

exec spImportExcelFile 'C:\Users\Andrew\Desktop\Junior_DB.xls','Master$','ExcelData'

Still not working.

I will do some more troubleshooting. I assume if I am building a .net application, this could be all handled from the front end (the import of data to sql).

 

[SkipVought]

What about this?

exec spImportExcelFile 'C:\Users\Andrew\Desktop\Junior_DB.xls','Master$','[b]Master$[/b]'

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gmmastros]

I am receiving the 'file does not exist' error.

Based on your code, you're not even getting to the portion where you are open the excel file. Seems like the excel file is not where SQL Server expects it to be. Have you tried copying your file to the server and then specifying the server's path to the file?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom