Whenever you want Rename Multiple Files through excel's list, then you will need some skills of Command Line (CMD). Because through CMD you can Rename multiple file with given related List very easily. And this the easiest way to any file conversion or move or copy files from one location to other.
But what process we need to Rename Multiple files through CMD using a list of file name from Excel?
This post also Describe how to use VLOOKUP in Excel.
So friends, for step by step process follow the following steps.
We use ".csv" file format for listing. For creating Files List through CMD:
Start Menu >> Type "cmd" in Search Box >> Click on "cmd.exe"
There is a way to understand through following image.
Easily Create folders and copy files through CMD
Script Steps: CD >> SPACE >> Folder Path
Where to find Listview component and Add in Toolbox in VB 6.0
Following image will explain all doubt.
Suppose, "File_List_Details.xls" contain 2 columns.
Suppose, "Filelist.csv" contain 2 columns.
Get ListView data in TextBox in Visual Basic6.0 code
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.
10. 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.
So, This way you can Easily Use VLOOKUP In Excel.
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.
Design Login form with adodc connection with MS-Access in vb6.0
The following image will explain the rename result for understand.
Now you can see the output of Rename files through CMD.
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
But what process we need to Rename Multiple files through CMD using a list of file name from Excel?
This post also Describe how to use VLOOKUP in Excel.
So friends, for step by step process follow the following steps.
- DIR Command for Creating List of Files from folders
- Extract File's Name from Path from DIR CSV List
- VLOOKUP Formula between CSV file and Excel file
- Rename Files through Excel (Query or Formula)
- Execute Bat file for Rename Files
(1). DIR Command for Creating List of Files from folders
If you want to rename a bunch of files, First, you have to create a list of all files which will proceed for rename. Then Save this files list in ".csv" or ".txt" format.We use ".csv" file format for listing. For creating Files List through CMD:
1.
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.
 |
| Open CMD and type command |
2.
This action shows CMD window, In this window, type command like: DIR, Copy, Ren, MKDir etc. then type source path on it. First you have to enter on paticular drive by typing drive name like:- "C:" or "D:" or "E:", where source folder exist.Easily Create folders and copy files through CMD
3.
Then press "Enter" key from keyboard, drive will be add to CMD. After that, the next process to add folder location path.
4.
Type batch script "CD", then press "SPACE" key from keyboard and then input source path as described above image.
5.
Then press "Enter" Key, Now the path will be add to CMD window.Script Steps: CD >> SPACE >> Folder Path
6.
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
/s : Indicate sub Folder
*.* : Indicate All extension files
Filename : Type file name whatever you want to give
.csv : The extension of Filename
7.
When pressing "Enter" key. It can take a few second to complete process. This script will create files listing to source location.
8.
Go to file's path. you will find a File has been 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's 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?
1.
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).
2.
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 from path |
3.
After apply this formula, file name will be extracted in column "B". The First Column "A" containing file name with path and second column "B" containing only filename.
4.
Now, save CSV file. And open other Excel file which contain the Listing of rename files details related with file name.
5.
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 ---------- |
| 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 tips |
6.
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.
7.
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
8.
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)
9.
After typing VLOOKUP formula in Excel, 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.
10. 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.
So, This way you can Easily Use VLOOKUP In Excel.
(4). Rename Files through Excel (Query or Formula):
1.
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.
2.
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 | " |
3.
After that 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() |
 |
| Execute CONCATENATE Formula |
4.
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
“"” : 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.
5.
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
6.
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. |
| Copy Cell in Notepad and Save it |
7.
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 to understand.(5). Execute Bat File for Rename Files:
8.
After saving Notepad, go to file location and find "RenameBatch.bat" file then double click on it.
9.
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 |
Now you can see the output of Rename files through CMD.
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
Thanks!..
ReplyDelete