Solved

How to do a find replace on file names within a directory with a script.

Posted on 2011-03-21
6
406 Views
Last Modified: 2012-05-11
I have a an excel sheet that will have first/last name and an account number.

I have a directory of files that are named via acct number, I would like to script a way to rename all those account numbers to first/last name.  For example find replace "account number" with the name listed by the account number.

Does anybody know of a good way to do that, or have a working script
0
Comment
Question by:Preston55
6 Comments
 
LVL 9

Accepted Solution

by:
McOz earned 250 total points
ID: 35182813
Here is a very simple code which should do what you need:
Sub renameFiles()
    myPath = "C:\New Folder\" 'the path to your directory (remember backslash at end)
    i = 2 'start on second row, assuming you have headers
    On Error Resume Next 'if a matching file is not found in the directory, skip it
    Do Until Cells(i, 1).Value = "" 'loop down the excel sheet until the first empty cell
        oldName = myPath & Cells(i, 1).Value & ".xlsx" 
        Name oldName As myPath & Cells(i, 2).Value & Cells(i, 3).Value & ".xlsx"
        i = i + 1
    Loop
End Sub

Open in new window


It works with a sheet such as the one attached. Book2.xlsx
0
 
LVL 32

Assisted Solution

by:DrDamnit
DrDamnit earned 250 total points
ID: 35182850
Fart.

Find And Replace Text.

http://sourceforge.net/projects/fart-it/files/

You can do a find and replace on file names, their contents, type, etc...

Easily run from a batch file, or from a single command line.
0
 

Author Comment

by:Preston55
ID: 35189222
side question on that, some of the files have extended names for example

john smith is account number 34576,

so he may have two files which would be 34576_abcdef.tif and 34576_mnop.tif

With using those scripts can it preserver the _abcdef, yet change the 34576 to John Smith

Hope that makes sense, Thanks Again.

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 9

Expert Comment

by:McOz
ID: 35189472
OK, if that is the case you will need to use this code:
Sub renameFiles()
    myPath = "C:\New Folder\" 'the path to your directory (remember backslash at end)
    i = 2 'start on second row, assuming you have headers
    Do Until Cells(i, 1).Value = "" 'loop down the excel sheet until the first empty cell
        strFile = Dir(myPath & Cells(i, 1).Value & "*.xls*", vbNormal)
        Do While strFile <> ""
            Name strFile As Replace(strFile, Cells(i, 1).Value, Cells(i, 2).Value & Cells(i, 3).Value)
            strFile = Dir
        Loop
        i = i + 1
    Loop
End Sub

Open in new window


This will replace the account number in any filename that begins with the account number.

Cheers
0
 
LVL 10

Expert Comment

by:wls3
ID: 35199222
Is this Excel--as in .xls/.xslx--or .csv?  Also, do you have a preference on which scripting language?  I see you put several.

Also, as someone who has a predilection for using Excel to manipulate stuff in a pinch, I am wondering why not try this low-rent solution: assuming your have columns A (first name), B (last name) and C (account number), why not use something this is in column D:

="ren C:\your\folder\path\"&C1&".ext C:\your\folder\path\"&A1&B1&".ext"

Open in new window


The output would be:

A: Will
B: Steele
C: 12345
D: ren C:\your\folder\path\12345.ext C:\your\folder\path\WillSteele.ext

Drag this down to your last row and then copy/paste Column D values into a .bat file.  Double-click the .bat file and renaming is complete.  I would also STRONGLY recommend testing and backing up your directory before running this.
0
 
LVL 10

Expert Comment

by:wls3
ID: 35199232
Sorry, posted too soon.  The command in traditional cmd shell would be:
="ren C:\your\folder\path\"&C1&".ext "&A1&B1&".ext"

Open in new window

Do not use the full path to prefix the second file name.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
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…

790 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