Solved

Trimming file name in folder

Posted on 2008-10-21
30
485 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 21

Expert Comment

by:AmazingTech
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks that's it!!!!
0
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
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
Comment Utility
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
Comment Utility
One last question, I googled this but couldn't find anything what does

FOR /F "Tokens=*"

do?
0
 
LVL 21

Expert Comment

by:AmazingTech
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 21

Expert Comment

by:AmazingTech
Comment Utility
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
Comment Utility
AmazingTech,

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

0
 

Author Comment

by:PROJHOPE
Comment Utility
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
Comment Utility
So these files are not created?

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

Author Comment

by:PROJHOPE
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks for the help anyway!  I will just put this in a batch file!
0
 
LVL 21

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Best file copying tool for large amount of data 3 64
Convert EXE to ISO 6 136
CMD shell elevation.js 4 56
Batch: check service status 11 79
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…
A while back when OPSMGR 2012 was released we were very excited about getting it into our environment and upgrading our 2007 implementation,  we started our planning and we then proceeded with our implementation. All went as planned & our system …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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