Renamexcel file using T-sql or xp_cmdshell

Question

Post reply

Renamexcel file using T-sql or xp_cmdshell

dallas13

SSCertifiable

Points: 7491
More actions
November 26, 2008 at 11:47 am

#196230

I want to rename one template using SSIS's Execute SQL task or using sql server commands
I need to put timestamp while renameing the original file so u can say i want
C:\abc\test_temp.xls file to be renamed as C:\abc\test_112620081400.xls
i can do that using batch file plz run first 3 lines as a batch file
for /f "tokens=2-4* delims=/ " %%a in ('DATE /T') do set THEDATE=%%c%%b%%a
Ren C:\abc\test_temp.xls test_%THEDATE%.xls
PAUSE
Then execute it works
----------------------------
BUT IF U RUN IT IN SQL using xp_cmdshell IT GIVES ERROR
rem DECLARE @rc int
rem EXEC @rc = master.dbo.xp_cmdshell 'copy C:\abc\test_temp.xls C:\abc\log\test.xls'
rem Exec master..xp_cmdshell'for /f "tokens=2-4* delims=/ " %%a in ('DATE /T') do set THEDATE=%%c%%b%%a'
rem Exec master..xp_cmdshell'Ren C:\abc\log\test.xls test_%THEDATE%.xls'
Error=
rem Msg 102, Level 15, State 1, Line 3
rem Incorrect syntax near 'DATE'.
so ultimately i want to rename a file using sql server any help is appreciated thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

This should work in management studio(don't know why you had the Rem in the SQL code part though)

DECLARE @rc int

DECLARE @dt varchar(8)

DECLARE @cmd nvarchar(1000)

SELECT @dt = Convert(varchar(8),getdate(),112)

SET @cmd = 'Ren C:\abc\log\test.xls test_'+@dt+'.xls'

EXEC @rc = master.dbo.xp_cmdshell 'copy C:\abc\test_temp.xls C:\abc\log\test.xls'

rem Exec master..xp_cmdshell @cmd

I think there's a better way to rename a file using SSIS - maybe use a cmdshell task instead of an SQL task...I can't recall right now and don't have access to SSIS to check it up...

winash SSChampion Points: 11446 More actions · Answer 2

Since you are already using SSIS check these links for information on how to use the filesystem task to do the copying and renaming of files...

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

http://blogs.conchango.com/jamiethomson/archive/2005/09/14/SSIS-Nugget_3A00_-Move-and-rename-a-file-in-one-operation.aspx

dallas13 SSCertifiable Points: 7491 More actions · Answer 3

hi Winash

thanks i tried sql and xp_cmdshell method but i was stuck in syntax inside REN

but u have corrected it so fine but is it possible i can attach timestamp along with date??

i tried changing from 112 to 120 or some other forms which supports hh and mm (i dont want in sec or

milisec) i modify ur code but its not creating the full hh and mm file

i dont know why..?? can u take a look in tht??

Thanks again

winash SSChampion Points: 11446 More actions · Answer 4

You can use the datepart function to get the hour and minute information...using other styles in the convert (120 for example) will not work as the format is hh:mi and : is not allowed in a file name....

You could still use 120 and replace all : with _ or some other allowed separator

DECLARE @rc int

DECLARE @dt varchar(30)

DECLARE @cmd nvarchar(1000)

SELECT @dt = Convert(varchar(8),getdate(),112)+'_'+

Convert(varchar(8),datepart(hh,getdate()))+'_'+

Convert(varchar(8),datepart(mm,getdate()))

-- or can try this too

SELECT @dt = REPLACE(Convert(varchar(30),getdate(),120),':','_')

SET @cmd = 'Ren C:\abc\log\test.xls "test_'+@dt+'.xls"'

EXEC @rc = master.dbo.xp_cmdshell 'copy C:\abc\test_temp.xls C:\abc\log\test.xls'

rem Exec master..xp_cmdshell @cmd

I'd recommend you use the filesystem task in SSIS 🙂

dallas13 SSCertifiable Points: 7491 More actions · Answer 5

hey Thanks again winash

actually the thing is i can use file system task but i need 2 use it 2 times

first for copying file and second time for renaming the file.

Actually my main concern to rename is, i dont want to overwrite the file

and i dont want to delete old files n keep them in the same folder

Well another thing i wan to share here is i have one source OLE DB which gets few

data from sql database then it converts them using data conversion task and then loads them in

excel sheet.

But this is another issue, i have defined excel connection manager and it takes input

from C:\abc\log\test_20081126.xls

but we are creating this file everytime daily new so today it it test_20081126.xls

tomorrow test_20081127.xls and so on... but

my excel connection manager is static and it has C:\abc\log\test_20081126.xls path only

so it will overwrite in the same file instead of using newly created file

and i dont want that.what should be the best way to overcome this???

dallas13 SSCertifiable Points: 7491 More actions · Answer 6

actually its very simple... got it man...anyway thanks