?
Solved

Import multiple Excel files to SQL table variables in a stored procedure

Posted on 2007-11-13
5
Medium Priority
?
727 Views
Last Modified: 2012-06-27
There was a solution posted for a SIMILAR problem, but it isn't working for my situation.
I need to import several Exel files with a stored procedure into a table variable.
This stored procedure is being called from the web
@zfile is a parameter

I declare the table variable and the variable for the source file

Declare @Zip table(Zip3 varchar(3), zGroup varchar(25));
Declare @srcFile as varchar(200)

I set the string (the file names will change very time)       
Set @srcFile = 'Excel 8.0;Database=' + @zFile + ';HDR=Yes'      
                  
And try to import to the table variable...

INSERT INTO @Zip (Zip3, zGroup)
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @srcFile)...[Sheet1$]      


Then I get this error

Msg 102, Level 15, State 1, Procedure CreateCPTReport, Line 33
Incorrect syntax near '@srcFile'.

Is there any way you can change the file name each time, this doesn't seem to be working
and I have to do this with 4 tables every SP run
            
0
Comment
Question by:Kmarcum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20277967
Why dont you create the insert string as one line like this
declare @SQL varchar(8000)
set @SQL = 'INSERT INTO ' + @Zip + ' (Zip3, zGroup) SELECT * FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'', ' + @srcFile + ')...[Sheet1$] '
Exec (@SQL)

Try this.
0
 
LVL 12

Accepted Solution

by:
kselvia earned 1600 total points
ID: 20277970
You need dynamic SQL and that can't insert into a table variable.

declare @cmd varchar(1000)
Declare @Zip table(Zip3 varchar(3), zGroup varchar(25));
Declare @srcFile as varchar(200)

-- create temp table
create table #zip (Zip3 varchar(3), zGroup varchar(25))

Set @srcFile = 'Excel 8.0;Database=' + @zFile + ';HDR=Yes'      

set @cmd =
' INSERT INTO #Zip (Zip3, zGroup) SELECT * FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'', ' + @srcFile+ ')...[Sheet1$]'

exec (@cmd)

-- If you really need the table variable
insert @zip
   select * from #zip      

0
 
LVL 6

Assisted Solution

by:Rajesh_mj
Rajesh_mj earned 400 total points
ID: 20277978
0
 

Author Comment

by:Kmarcum
ID: 20314817
Sorry, I got sidetracked on another project I'll try these out over the holiday
0
 

Author Comment

by:Kmarcum
ID: 20612744
Some changes were needed on the server. As soon as it's tested I'll accept an answer.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question