sqlman08
asked on
loop through a spreadsheet, move files, then send email - Stored Procedure
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
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
ASKER
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?
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
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+
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+
EXEC master.dbo.xp_cmdshell 'COPY C:\temp\'+@FileName
END
ASKER
do not cancel I like FJS159 has a better solution which I am currently testing
ASKER
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.ProdI D)))
BEGIN
select @I = @I +1
Select @filename = left(po.path,len(po.Path)- 7)+@I+'.pd f' from tablea ps Inner Join tableb po
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdI D)) 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 '+'.
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.ProdI
BEGIN
select @I = @I +1
Select @filename = left(po.path,len(po.Path)-
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdI
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 '+'.
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
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
ASKER
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.ProdI D)))
BEGIN
select @I = @I +1
Select @filename = left(po.path,len(po.Path)- 7)+@I+'.pd f' from tablea ps Inner Join tableb po
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdI D)) 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 '+'.
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.ProdI
BEGIN
select @I = @I +1
Select @filename = left(po.path,len(po.Path)-
on ltrim(rtrim(ps.ProdID)) = 'OP/'+ltrim(rtrim(po.ProdI
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 '+'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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