can Advance Renamer replace name from Excel spreadsheet?

Advanced Renamer forum
#1 : 07/12-21 22:16
Alexandra Mariana Roman
Alexandra Mariana Roman
Posts: 1
Hello!

We need Advance renamer to rename multiple files with full names and badges of employees from an Excel Spreadsheet (maybe using macros) . We need to correlate the file name with the correct badge no. of the employee like in the example.

EE_610200xx Last name, first name of employee_ Identity card, 2021

Can it be possible?

Thank you!


07/12-21 22:16
#2 : 14/12-21 19:08
David Lee
David Lee
Posts: 1125
You haven't given enough information for a detailed specific reply.

However you can read lines from a csv file using the <File Line> tag and then use a regular expression to parse each line into its individual fields (using the commas as separators).

Lets assume that you have an Excel spreadsheet with three columns. The first column contains your badge numbers, the second column the employees' first names and the third column their last names.

Assume that all the filenames are the badge numbers and you wish to add the corresponding first and last names from the spreadsheet.

First of all save your Excel spreadsheet as a csv file - as list.csv

Create a script method and enter the following code (12 lines) into the Pre batch script window
Edit the first line to contain the full path to your csv file - but note that you must use "\\" as the separator (instead of the single back-slash used by Windows).

csv = 'C:\\Users\\YourName\\YourPath\\list.csv';
csv = '::\"' +csv + '\"';
var field_1 = {};
var field_2 = {};
j=1;
while (line = app.parseTags('<File Line:' + j + csv + '>')) {
match = line.match(/(.*),(.*),(.*)/);
index = match[1];
field_1[index] = match[2];
field_2[index] = match[3];
j++;
}


Now enter the following code (4 lines) into the main script window:

name = item.name;
if (field_1[name]) {
return name + '-' + field_1[name] + '-' + field_2[name];
}


In the Pre batch script, Advanced Renamer will build two tables using columns 2 & 3 of the spreadsheet with column 1 as the index in each case.

These tables are then available when the main script is run on each filename.

If the filename exists in the first column of the spreadsheet then the script will return:
BadgeNo-FirstName-LastName for each new file name.

If the filename is not matched in the spreadsheet then the filename will remain unchanged.






14/12-21 19:08 - edited 14/12-21 19:11