Saturday, March 31, 2018

Rename multiple Files through CMD using Excel easy Tips

Rename multiple Files through CMD using Excel List easy Tips

Whenever you want rename files through excel's list, then consider about command line argument. Because this the easiest way to any file conversion or move or copy. how to rename multiple files through CMD using a list of file name from Excel?

So friends, for step by step process follow the following steps.

(1). Create DIR CSV list of files from folder
(2). Extract file name from path from DIR CSV list
(3). VLOOKUP formula between CSV file and Excel file
(4). Rename query in Excel
(5). Execute Bat file for rename

(1). Create DIR CSV list of files from folder: If we want to rename a bunch of files, we have to create a list of all files, which we have to process for rename. Save this files list in ".csv" or ".txt" format. We use ".csv" file format for listing. For creating ".csv" file format of files list, go to "Start" menu of windows and type "cmd".

Start Menu >> Type "cmd" in Search Box >> Click on "cmd.exe"

There is a way to understand through following image.
type command for rename multiple files
Open CMD and type command

This action shows CMD screen, In this screen, type command like: DIR, Copy, Ren, MKDir etc. Type path directory on it. First, type drive name like:- "C:" or "D:" or "E:", where source folder exist.

Easily Create folders and copy files through CMD

Then press "Enter" key from keyboard, drive will be add to this screen. After that, the next process to add folder location path.

Type batch script "CD", then press "SPACE" key from keyboard and then type path. You can give path through selecting folder. After selecting Path, copy this path. And right click on "cmd" screen and select "Paste" option. Remember "cmd" cannot conduct "Ctrl+V" option for paste. Just right click and select "Paste" option for it. When paste folder path will done, press "Enter" Key, Now the path will be add to this screen in new line.

Script Steps: CD >> SPACE >> Folder Path

Now, the next step to execute a batch script to listing of files.
First, type "DIR /b/s *.*>FilesList.csv" then press "ENTER" key from keyboard.
Typing Instruction: “DIR /b/s *.*> Filename.csv”

/b : Indicate Base Folder
/s : Indicate sub Folder
*.* : Indicate All extension files
Filename : Type file name whatever you want to give
.csv : The extension of Filename

When apply pressing "Enter" key. It can take a few second to complete process. This script will create files listing to source location. Go to file's path. There is a file created with name "FileList.csv" on that particular location. Go to that Path. Find "FileList.csv" file and open it.

Where to find Listview component and Add in Toolbox in VB 6.0

(2). Extract File name from Path from DIR CSV List : When we open this file, column "A" containing a list of files details with path location. But, the problem is, how can we extract the file name from that path from cell?

So, we have to apply a formula in column "B". The following formula extract file name form path.

=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)- LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

Note: (A1 is the cell that you want to extract the filename from Path).

After typing above formula press "Enter" key from keyboard, then drag the cell to the end of the row as shown in bellow image. After that, the filename is extracted from each cell.

Following image will explain all doubt.
Extract Filename for rename multiple files
Extract filename from path
After apply this formula, file name extract in column "B". The First Column "A" containing file name with path and second column "B" containing only filename. Now, save CSV file. And open other Excel file which have the Listing of rename files details related with file name.

Suppose, details Excel name is "File_List_Details.xls" and DIR file name is "Filelist.csv". Open both files side by side in Excel workbook.

Suppose, "File_List_Details.xls" contain 2 columns.
A B
000001.jpg
000004.jpg
000005.jpg
000006.jpg
000008.jpg
---------
Chrysanthemum
Jellyfish
Koala
Lighthouse
Tulips
----------
Suppose, "Filelist.csv" contain 2 columns.
A B
D:\SKOTechlearn_Images\Tips_And_Triks\000001.jpg D:\SKOTechlearn_Images\Tips_And_Triks\000002.jpg D:\SKOTechlearn_Images\Tips_And_Triks\000003.jpg D:\SKOTechlearn_Images\Tips_And_Triks\000004.jpg
---------
000001.jpg 000002.jpg 000003.jpg 000004.jpg
----------
VLOOKUP Formula to rename files
VLOOKUP Formula tips
When open both files in Excel workbook side by side, select column A and B from "File_List_Details.xls" file and select column B from "Filelist.csv" file.

(3). VLOOKUP formula between CSV file and Excel file: Now, the time to match file list from both CSV and Excel file. But first, Select column B from "Filelist.csv" and define its name 'List' And select column A and B from "File_List_Details.xls" and define its name 'Details' as show in above image.

Get ListView data in TextBox in Visual Basic6.0 code

After defining the selected column name in both file. Come to the cell C1 from "Filelist.csv" file sheet. Type formula:

=VLOOKUP(List,File_List_Details.xlsx!Details,2,FALSE)

After typing VLOOKUP formula, Press "Enter" key from keyboard. This formula check cell's value from another excel sheet column. That means, 'List' will check containing column’s cells value with 'Details' column’s cells value. As well as 'List' will find value, it can show the related column’s value from 'Details' to column C in "Filelist.csv" file sheet.

When it correctly present value in column C, then drag cell to the end of the row. This process will present the value related in column B from "File_List_Details.xls" file sheet will show in column C of "Filelist.csv" file sheet as shown in above image.

(4). Rename Query in Excel : Before process on renaming Query, assign " (double inverted Comma) in cell D.

" (double inverted Comma) : It will assign in front and end of folder path in cell.

Like: "D:\SKOTechlearn_Images\Tips_And_Triks\.....\000001.jpg"

This is necessary, because if a folder contain name with space Like: 'File Name'. Then, batch process can not consider it as a folder. So for it, assign double Inverted comma in the end and front of Path. Then come to column D and type " (double Inverted comma). And drag it to the end of the row.
A B C D
D:\SKOTechlearn_Images\Tips_And_Triks\000001.jpg 000001.jpg Chrysanthemum "
Type '=CONCATENATE()' formula in column E. Then Press on Function Fx Button as shown in bellow image.
A B C D E
D:\SKOTechlearn_Images\Tips_And_Triks\000001.jpg 000001.jpg Chrysanthemum " =CONCATENATE()
CONCATENATE Formula for rename multiple files
Execute CONCATENATE Formula
When we click on function Fx button, a "Function Arguments" box will open. In this box just add cell with batch script.

"Ren ": Ren is batch script of rename File (Ren + SPACE key).

“"” : Select column where " inverted comma assign Like:- Select column D1.

"D:\SKOTechlearn_Images\...." : Select that column in which source path define. Like:- Select Column A1.

" " : Press SPACE Key from keyboard.

"Chrysanthemum" : Select that column in which the renaming value define. Like: Select Column C1.

".jpg" : Type extension of file which have been renamed. Like:- .jpg

Note:- This is requre to give same extension name of renaming file as defined in source file extension.

Fill every 'Text' (like:- Text1, Text2, Text3 etc) in "Function Arguments" window. After that, click on "OK" button.

Design Login form with adodc connection with MS-Access in vb6.0

The CONCATENATE value will show in column E1. Drag and drop this column to the end of the row. Select all row value from column E and paste it on Notepad as shown in bellow image.
save notepad for rename multiple files
Copy Cell in Notepad  and Save it
Save this Notepad with extension name ".Bat" (like:- "RenameBatch.Bat"). Save this file in any of drive’s folder. But I will save this Notepad in existing rename file location. Just follow the picture for easily understand.

(5). Execute Bat file for rename: After saving Notepad, go to file location and find "RenameBatch.bat" file then double click on it. It will execute and rename files within a second. Now, the files will be renamed successfully. (Like:- "000001.jpg" to "Chrysanthemum.jpg" ).
The following image will explain the rename result for understand.
Files comparison of rename multiple files
Files comparison
Conclusion: The renaming of bunch of files is not easy to process. But if you are strong in command line and also strong in excel formula like: VLOOKUP, then it is easy to learn for everyone. But if you don’t know about command line or VLOOKUP process. Then apply above process with step by step instruction and process for easily rename bunch of files at a time.

Now, SKOTechLearn Tips has described tips to learn that How to Rename multiple Files through CMD using Excel List?

Coding to Edit or delete selected Listview Items in vb6.0 

3 comments:

  1. Great post! This is very useful for me and gain more information, Thanks for sharing with us.

    Article submission sites
    Guest posting sites

    ReplyDelete
  2. I read Many Post about Excel and Other Courses but I really Impressed about your Writing Way and How to Express to words.. It’s really helpful for us Thanks for sharing,, keep writing
    Advanced excel training in delhi
    Advanced excel training in Noida
    Advanced excel training in Gurgaon

    ReplyDelete