Solved

loop through a spreadsheet, move files, then send email - Stored Procedure

Posted on 2009-03-30
8
304 Views
Last Modified: 2012-05-06
Hi, I want to know if this is possible, I have a spreadsheet like below I want to loop through each cell in column A (ProdID is the header cell A1):

ProdID            
AAA123
AAA124
AAA125

Then go and check if these ProdID are listed in a table on sqlserver which looks like below:

ProdID             Path
AAA123         C:\Bobme\Gray\AAA123.pdf
AAA124         C:\Bobme\Gray\AAA124.pdf
AAA125         C:\Bobme\Gray\AAA125.pdf

If it does then I want it to copy the file from the relevant Path and paste this into a a temp folder i.e C:\Temp. Once the loop has finished I want it to send the files that has been copied into the temp file and send a email to the user, i.e xp_sendmail. I know there is a limit on the attachments via xp_sendmail bu just want to know if this is possible or is there a alternative solution to this process?

Thanks
0
Comment
Question by:sqlman08
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 24026224
you can do it
1) Import the excel into the DB
2) Copy he file EXEC  master.dbo.xp_cmdshell 'COPY C:\temp\'+@File Name
3) send Mail xp_sendmail
repeat step 2, 3
0
 
LVL 1

Author Comment

by:sqlman08
ID: 24032850
ok, I have done a dts package to import the spreadhseet to a table. Now how do I join to the other table find a match and copy the files into a temp folder?
0
 
LVL 2

Expert Comment

by:FJS159
ID: 24044547
Select ProdID from TableA Inner Join TableB on TableA.ProdId = TableB.ProdId

Will give you only those items in TableB that exists in TableA

Your ProdID appears sequentially numbered.  I would loop through the results starting with the min ID and add 1 each time until done.  
left(path,len(Path)-7)+@I+'.pdf'  will incriment the number portion of your filename and attach PDF onto the end.  This assumes your filenames start at 0.
You could also insert your email notification into this loop.


declare @I as int, @filename as varchar(200)
set @I = 0    
WHILE (SELECT @I  ) < (select count(ProdID) from TableA Inner Join TableB on TableA.ProdId = TableB.ProdId)

BEGIN

select @I = @I +1

Select @filename = left(path,len(Path)-7)+@I+'.pdf' from from TableA Inner Join TableB on TableA.ProdId = TableB.ProdId where @I = right(ProdID,3)

EXEC  master.dbo.xp_cmdshell 'COPY C:\temp\'+@FileName


END
0
 
LVL 1

Author Comment

by:sqlman08
ID: 24052538
do not cancel I like FJS159 has a better solution which I am currently testing
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:sqlman08
ID: 24059889
hi, I tried t run this in query analyzer

declare @I as int, @filename as varchar(200)
set @I = 0    
WHILE (SELECT @I  ) < (select count(ps.ProdID) from tablea ps Inner Join tableb po on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdID)))

BEGIN

select @I = @I +1

Select @filename = left(po.path,len(po.Path)-7)+@I+'.pdf' from tablea ps Inner Join tableb po
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdID)) where @I = right(ps.ProdID,3)

EXEC  master.dbo.xp_cmdshell 'COPY C:\Temp\'+@filename


but I get the following error:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '+'.



0
 
LVL 2

Expert Comment

by:FJS159
ID: 24060397
Sorry I just copied the cmdshell line from vinurajr, it was just an example but wasn't complete and I think had an error.
The cmdshell must be the same as you would type in DOS and requires a source & destination for the copy command.
First make sure you have permission to copy files.  try a simple command with no variables.  ie
EXEC  master.dbo.xp_cmdshell 'COPY C:\file.txt c:\file.tmp';
go

If that works try assigning the entire value for your copy command to a variable, then passing that.  ie
USE master;
declare @filename as nvarchar(50), @filename2 as nvarchar(50)
set @filename2 = 'file.txt'
set @filename = 'COPY C:\'+@filename2+' c:\file.tmp'
EXEC  master.dbo.xp_cmdshell @filename
go
0
 
LVL 1

Author Comment

by:sqlman08
ID: 24089329
Hi, the following works when I use this query:

USE master;
declare @filename as nvarchar(50), @filename2 as nvarchar(50)
set @filename2 = 'file.txt'
set @filename = 'COPY C:\'+@filename2+' c:\file.tmp'
EXEC  master.dbo.xp_cmdshell @filename


When I now run this:
declare @I as int, @filename as varchar(200)
set @I = 0    
WHILE (SELECT @I  ) < (select count(ps.ProdIDo) from tablea ps Inner Join tableb po on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdID)))


BEGIN

select @I = @I +1

      
Select @filename = left(po.path,len(po.Path)-7)+@I+'.pdf' from tablea ps Inner Join tableb po
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdID)) where @I = right(ps.ProdID,3)

EXEC  master.dbo.xp_cmdshell 'COPY C:\pdftemp\'+@filename

 I get the follwoing error:

Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '+'.


0
 
LVL 2

Accepted Solution

by:
FJS159 earned 500 total points
ID: 24152659
Don't use the + in your cmdshell line.  Try:

declare @filename2 as nvarchar(100)
select @I = @I +1

     
Select @filename = left(po.path,len(po.Path)-7)+@I+'.pdf' from tablea ps Inner Join tableb po
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdID)) where @I = right(ps.ProdID,3)

set @filename2 = 'COPY C:\pdftemp\'+@filename
EXEC  master.dbo.xp_cmdshell  @filename2
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now