Trimming file name in folder

Something like this...

FileName="DBSpuen20081021.bak"
For x = 1 to Len(FileName) 
  TestString=Mid(FileName,x,1)
  If Not IsNumeric(TestString) Then NewFileName = NewFileName & TestString
Next
msgbox NewFileName

Select allOpen in new window

thanks for the help but I do not see how this code you have presented will loop through all 51 files.  In your script you have a variable that you must provide.  

I use to have this call outside of the SQL database via a .bat file but I am trying to incorporate it all in one place.  The original was used with Kix scripts

      IF LEN($filename) >20
            $goodname=LEFT($filename,LEN($filename) -20) + ".bak"

I just need to loop, do the conversion in DOS so I can wrap it around an xp_cmdshell

Give this a try.

Remove ECHO before REN to actually make it rename the file.

SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=C:\Files
 
FOR /F "Tokens=*" %%a in ('dir /a-d /b "%FOLDER%\*.bak"') DO (
    Set Filename=%%~na
    Set Filename=!Filename:~0,-8!
    ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa
    ECHO REN "%FOLDER%\%%a" "!Filename!%%~xa"
)

Select allOpen in new window

Opps I missed the last " in the ECHO Renaming line.

SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=C:\Files
 
FOR /F "Tokens=*" %%a in ('dir /a-d /b "%FOLDER%\*.bak"') DO (
    Set Filename=%%~na
    Set Filename=!Filename:~0,-8!
    ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
    ECHO REN "%FOLDER%\%%a" "!Filename!%%~xa"
)

Select allOpen in new window

Amazing Tech

That's for the script it works except the file has a apostrophe at the end of the .bak extension

For example DBSpuen20081021.bak becomes DBSpuen.bak'

I tried to remove the ' from the last line before the closed parenthesis but that didn't work...



 

SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=E:\SQL Backup\Data
 
FOR /F "Tokens=*" %%a in ('dir /a-d /b "%FOLDER%\*.bak"') DO (
    Set Filename=%%~na
    Set Filename=!Filename:~0,-8!
    ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
    REN "%FOLDER%\%%a" "!Filename!%%~xa"')

Select allOpen in new window

Thanks that's it!!!!

Doesn't that only trim it to 7 chars?  I thought you said there were 6 & 9 character length filenames?

The other DBS names. Maybe something like

DBSABC20080101.bak
DBSABCDEF20080101.bak

The !Filename:~0,-8! removes the last 8 characters of the filename just the date portion so 6, 9, 20 will be retained.

One last question, I googled this but couldn't find anything what does

FOR /F "Tokens=*"

do?

FOR /?

Tokens=*

Basically don't separate the output. It will store the entire filename into %%a if you had spaces in your name then the first part would be %%a second part would be %%b and so on.  you can also tell it to take the second part and store it in %%a by using Tokens=2.

AmazingTech

One last question I promise...as mentioned I want to place this within a SQL set command then execute with xp_command stored procedure (not sure how familar you are with SQL Server)

In order to make the set variable I have to enclose the code in quotes.  When I enclose the quotes the command does not work because I have to double-quote the dir /a-d line

Any thoughts?

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=E:\SQL Backup\Data
 
FOR /F "Tokens=*" %%a in ("dir /a-d /b "%FOLDER%\*.bak"") DO (
    Set Filename=%%~na
    Set Filename=!Filename:~0,-8!
    ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
    REN "%FOLDER%\%%a" "!Filename!%%~xa"
)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

How about this?

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'SETLOCAL ENABLEDELAYEDEXPANSION
E:
CD "\SQL Backup\Data"
 
FOR %%a in (*.bak) DO (
    Set Filename=%%~na
    Set Filename=!Filename:~0,-8!
    ECHO Renaming "%%a" to "!Filename!%%~xa"
    REN "%%a" "!Filename!%%~xa"
)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

Very bizarre,

If I take the doscommand2 and place in a .bat file and run through DOS...works fine...

If I take the script above and run in SQL, I get output NULL, 1 row affected but nothing occurs...

If you want to take another stab at it let me know otherwise I am going to get lazy and do SET @doscommand 2 = 'e:\file.bat'

I suppose it is quite possible it doesn't understand some of the commands. But when you call a .bat file it uses the cmd.exe to run the batch file.

Let's try a few commands at a time.

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'E:
CD "\SQL Backup\Data"
 
FOR %%a in (*.bak) DO (
    ECHO File "%%a">>e:\Filelog.txt
)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

And try this:

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'SETLOCAL ENABLEDELAYEDEXPANSION
E:
CD "\SQL Backup\Data"
ECHO %CD%>>e:\FileLog2.txt 
FOR %%a in (*.bak) DO (
    Set Filename=%%~na
    Set Filename=!Filename:~0,-8!
    ECHO "!Filename!%%~xa">>e:\Filelog2.txt
)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

AmazingTech,

Again thanks running both scripts gives a 1 row affected yet nothing occurs....

If I do the two last scripts in DOS, the first one executes but with no results, the second one errors running in DOS

So these files are not created?

E:\filelog.txt
E:\filelog2.txt

If I run the scripts in SQL no filelog txt, but if I run them as a bat file in DOS the files are created...attached are the files.

Note other xp_cmdshell batch files run successfully in SQL so xp_cmdshell is not the problem.
Filelog.txt
FileLog2.txt

Does this work?

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'E: & CD "\SQL Backup\Data" & FOR %%a in (*.bak) DO (ECHO File "%%a">>e:\Filelog.txt)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

Output received on execute

%%a was unexpected at this time.


I thought that in SQL I had to first say SET QUOTED_IDENTIFIER ON to have the mix of "" and ' but that didn't work either...

Amazing Tech let me step outside the box for second...perhaps the xp_cmdshell cannot handle the script as a whole

I could break it down

SET doscommand 1 - first part
SET doscommand 2 - second part
SET doscommand 3 - third part

execute xp_cmdshell doscommand1 + doscommand2 + doscommand3

Is this possible with your script provided?

Yes. I've been reading http://msdn.microsoft.com/en-us/library/ms175046.aspx

So I'm removing the fluff. Hopefully this will work. Your error above helps in noting it's 1 comand line operation.

Try this.

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'SETLOCAL ENABLEDELAYEDEXPANSION & E: & CD "\SQL Backup\Data" & FOR %a in (*.bak) DO (Set Filename=%~na& REN %a !Filename:~0,-8!%~xa)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

I think we are getting closer...I get this error in SQL

E:\SQL Backup\Data>(Set Filename=DBSallc10212008  & REN DBSallc10212008.bak !Filename:~0,-8!.bak )
The syntax of the command is incorrect.

I get this error in DOS running your script

The following usage of the path operator in batch-parameter substitution is invalid: %~xa)

Looks to have figured out %~xa I think it's the !Filename:~0,-8!

Try this.

DECLARE @doscommand2 varchar(2000)
SET @doscommand2 = 'SETLOCAL ENABLEDELAYEDEXPANSION & E: & CD "\SQL Backup\Data" & FOR %a in (*.bak) DO (Set Filename=%~na& REN %a !Filename:~0,-8!.bak)'
EXEC master.dbo.xp_cmdshell @doscommand2

Select allOpen in new window

When I run this in SQL I get this

NULL
E:\SQL Backup\Data>(Set Filename=DBSallc10212008  & REN DBSallc10212008.bak !Filename:~0,-8!.bak )
The syntax of the command is incorrect.

When I do it in DOS I get this

SETLOCAL ENABLEDELAYEDEXPANSION & E: & CD "\SQL Backup\Data" & FOR %a in (*.bak) DO (Set Filename=%~na& REN %a !Filename:~0,-8!.bak)

It just lists the command

Sorry it looks like the SETLOCAL ENABLEDELAYEDEXPANSION is not working for the single line.

Even when I try it in 2 lines it seems it doesn't work either.

thanks for the help anyway!  I will just put this in a batch file!

OK. If I think of something I'll post it here.