Solved

Trimming file name in folder

Posted on 2008-10-21
30
488 Views
Last Modified: 2013-12-01
I have 51 files with a format of [database name][backupdate].  For example DBSpuen20081021.bak

I want to do a trim of the date so the file is DBSpuen.bak

Now the kicker....98% of the file name length is 7 but two other database names are 6 and 9 characters respectively.

How can I use the LEN command so it accidentally trim any part of the database name?  (The db names are all alpha characters)
0
Comment
Question by:PROJHOPE
  • 14
  • 14
  • 2
30 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 22771558
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

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22776251
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

0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22777205
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"
)

Open in new window

0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 21

Expert Comment

by:AmazingTech
ID: 22777213
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"
)

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22778884
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"')

Open in new window

0
 
LVL 21

Accepted Solution

by:
AmazingTech earned 125 total points
ID: 22778971
What if you put the bracket on the next line?

I like to line up the close bracket with the command it opened with.

So.

IF something equals something (
     FOR ..... .... DO (
            RUN
            IF  something equals something (
                  RUN
            )
            RUN
            RUN
     )
    RUN
    RUN
)  
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"
)

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22779005
Thanks that's it!!!!
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 22779038
Doesn't that only trim it to 7 chars?  I thought you said there were 6 & 9 character length filenames?
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22779388
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.
0
 

Author Comment

by:PROJHOPE
ID: 22779417
One last question, I googled this but couldn't find anything what does

FOR /F "Tokens=*"

do?
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22779455
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.
0
 

Author Comment

by:PROJHOPE
ID: 22779792
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

Open in new window

0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22779870
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

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22780579
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'

0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22781014
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

Open in new window

0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22781033
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

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22786350
AmazingTech,

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

0
 

Author Comment

by:PROJHOPE
ID: 22786394
If I do the two last scripts in DOS, the first one executes but with no results, the second one errors running in DOS
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22786586
So these files are not created?

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

Author Comment

by:PROJHOPE
ID: 22786685
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
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22786759
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

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22786785
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...

0
 

Author Comment

by:PROJHOPE
ID: 22786877
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?
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22786973
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

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22787690
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)
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22787951
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

Open in new window

0
 

Author Comment

by:PROJHOPE
ID: 22788026
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
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22788404
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.
0
 

Author Comment

by:PROJHOPE
ID: 22788519
thanks for the help anyway!  I will just put this in a batch file!
0
 
LVL 21

Expert Comment

by:AmazingTech
ID: 22788602
OK. If I think of something I'll post it here.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

TOMORROW TOMORROW.BAT is inspired by a question I get asked over and over again; that is, "How can I use batch file commands to obtain tomorrow's date?" The crux of this batch file revolves around the XCOPY command - a technique I discovered w…
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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