G'day

Sorry I don't have time to completely review where the problem may be in your script...

..but if you're having trouble with the Excel part you may find the info I wrote at MacGrunt useful.

that script uses Excel to generate two lists for changing filenames — so, somewhat similar to what you are trying to do.

There's a brief explanation of the vagaries of getting data from excel.

Getting the complete set of data from each column right from the start (as I do in that script) will probably be quicker than looping repeatedly through Excel.

Good luck with it.

m.

Recently I wrote a script that reads a CSV-file exported from Excel and renames bookmarks (including children) in the active document. But it is written in JavaScript. If you're interested, I can post it. It will require some minor changes according to your needs.

Kas

Here is the script:

//======================================================================================
const gScriptName = "Rename InDesign bookmarks";
const gScriptVersion = "2.0";
 
var gCsvArr, doc, gDocName;
var gCounter = 0;
var gAllBookmarks = [];
 
Main();
 
//===================================== FUNCTIONS  ======================================
function Main() {
    var bookmark;
    if (app.documents.length == 0) ErrorExit("Please open a document and try again.", true);
    var doc = app.activeDocument;
    gDocName = GetFileName(doc.name);
    var csvFile = File.openDialog("Locate a CSV file", "CSV Files:*.CSV");
    if (csvFile == null) exit();
    gCsvArr = ReadCsvData(csvFile);
    
    var bookmarks = doc.bookmarks;
    
    for (var i = 0; i < bookmarks.length; i++) {
        bookmark = bookmarks[i];
        GetAllBookmarks(bookmark);
    }
    
    if (gAllBookmarks.length == 0) ErrorExit("There are no bookmarks in this document.", true);
    
    ReplaceFunnyCharsInBookmarkNames();
    ProcessBookmarks();
    GetNotRenamedBookmarks(csvFile.name);
    
    alert("Finished. " + gCounter + " out of " + gAllBookmarks.length + " bookmarks were renamed.", gScriptName + " - " + gScriptVersion);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
function ReplaceFunnyCharsInBookmarkNames() {
    for (var i = 0; i < gAllBookmarks.length; i++) {
        bookmark = gAllBookmarks[i];    
        bookmark.name = bookmark.name.replace(/(\r|\n)/g, "");
    }
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
function GetAllBookmarks(bookmark) {
    bookmark.label = "";
    gAllBookmarks.push(bookmark);
    
    var bookmarks = bookmark.bookmarks;
        for (var i = 0; i < bookmarks.length; i++) {
            GetAllBookmarks(bookmarks[i]);
        }
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetNotRenamedBookmarks(csvFileName) {
    var bookmark;
    var notRenamedBookmarks = [];
    
    for (var i = 0; i < gAllBookmarks.length; i++) {
        bookmark = gAllBookmarks[i];
        if (bookmark.label != "ok") notRenamedBookmarks.push(bookmark);
    }
    
    if (notRenamedBookmarks.length > 0) {
        WriteToFile(GetDate());
        WriteToFile("InDesign file: " + gDocName);
        WriteToFile("CSV-file: " + csvFileName + "\r");
        
        for (var k = 0; k < notRenamedBookmarks.length; k++) {
            WriteToFile(notRenamedBookmarks[k].name);
        }
        WriteToFile("\r");
    }
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function ReadCsvData(csvFile) {
    var line, temp, csvRecord, tempArr;
    var csvData = [];
    csvFile.open("r");
    
    while (!csvFile.eof) { 
        line = csvFile.readln();
        if (line.match(/^Volume/) != null) continue; // skip the header
        if (/;{2,}/g.test(line)) continue; // skip empty lines with two or more semicolumns
        tempArr = line.split(";");
        csvData.push(tempArr);
    }
 
    csvFile.close();
    
    return csvData;
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function ProcessBookmarks() {
    var bookmark, findWhat, changeTo, columnC;
    
    for (var i = 0; i < gAllBookmarks.length; i++) {
        bookmark = gAllBookmarks[i];
        for (var j = 0; j < gCsvArr.length; j++) {
            findWhat = gCsvArr[j][1].replace(/(^"|"$)/g, ""); // column B
            changeTo = gCsvArr[j][3].replace(/(^"|"$)/g, ""); //  column C (0 - 1st, 1 - 2nd, 2 - 3rd and so on)
        
            if (findWhat == bookmark.name) {
                bookmark.name = changeTo;
                bookmark.label = "ok";
                gCounter++;
            }
        } // for j 
    } // for i
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function ErrorExit(error, icon) {
    alert(error, gScriptName + " - " + gScriptVersion, icon);
    exit();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function WriteToFile(text) {
    var file = new File("~/Desktop/" + gDocName + ".txt");
    if (file.exists) {
        file.open("e");
        file.seek(0, 2);
    }
    else {
        file.open("w");
    }
    file.write(text + "\r"); 
    file.close();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetFileName(fileName) {
    var str = "";
    var res = fileName.lastIndexOf(".");
    if (res == -1) {
        str = fileName;
    }
    else {
        str = fileName.substr(0, res);
    }
    return str;
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetDate() {
    var date = new Date();
    if ((date.getYear() - 100) < 10) {
        var year = "0" + new String((date.getYear() - 100));
    } else {
        var year = new String ((date.getYear() - 100));
    }
    var dateString = (date.getMonth() + 1) + "/" + date.getDate() + "/" + year + " " + date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds();
    return dateString;
 }

You have to edit  ReadCsvData and ProcessBookmarks functions (see comments)

For example, in my sheet, the header contains word "Volume" (cell A:1) so I use this line to skip it:

if (line.match(/^Volume/) != null) continue;

In findWhat and changeTo variables you have to set number of columns you want to use (I use B and C)

This script was written for csv-files exported from Excel 2011 (Mac). BTW, my version of Excel 2007 (PC) exports csv-file a little differently.

Hope this helps.

Kas

Here is a newer version of the script (I forgot about it yesterday) which works with csv-files exported from Excel 2011 for Mac, the previous one is for CSVs exported from Excel 2007 (PC).

const gScriptName = "Rename InDesign bookmarks";
const gScriptVersion = "2.3";
 
var gCsvArr, gDocName;
var gCounter = 0;
var gAllBookmarks = [];
 
Main();
 
//===================================== FUNCTIONS  ======================================
function Main() {
    var bookmark;
    if (app.documents.length == 0) ErrorExit("Please open a document and try again.", true);
    var doc = app.activeDocument;
    gDocName = GetFileName(doc.name);
    var csvFile = File.openDialog("Locate a CSV file", "CSV Files:*.CSV");
    if (csvFile == null) exit();
    gCsvArr = ReadCsvData(csvFile);
    
    var bookmarks = doc.bookmarks;
    
    for (var i = 0; i < bookmarks.length; i++) {
        bookmark = bookmarks[i];
        GetAllBookmarks(bookmark);
    }
    
    if (gAllBookmarks.length == 0) ErrorExit("There are no bookmarks in this document.", true);
    
    ReplaceFunnyCharsInBookmarkNames();
    ProcessBookmarks();
    GetNotRenamedBookmarks(csvFile.name);
    
    alert("Finished. " + gCounter + " out of " + gAllBookmarks.length + " bookmarks were renamed.", gScriptName + " - " + gScriptVersion);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
function ReplaceFunnyCharsInBookmarkNames() {
    for (var i = 0; i < gAllBookmarks.length; i++) {
        bookmark = gAllBookmarks[i];    
        bookmark.name = bookmark.name.replace(/(\r|\n)/g, "");
    }
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
function GetAllBookmarks(bookmark) {
    bookmark.label = "";
    gAllBookmarks.push(bookmark);
    
    var bookmarks = bookmark.bookmarks;
        for (var i = 0; i < bookmarks.length; i++) {
            GetAllBookmarks(bookmarks[i]);
        }
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetNotRenamedBookmarks(csvFileName) {
    var bookmark;
    var notRenamedBookmarks = [];
    
    for (var i = 0; i < gAllBookmarks.length; i++) {
        bookmark = gAllBookmarks[i];
        if (bookmark.label != "ok") notRenamedBookmarks.push(bookmark);
    }
    
    if (notRenamedBookmarks.length > 0) {
        WriteToFile(GetDate());
        WriteToFile("InDesign file: " + gDocName);
        WriteToFile("CSV-file: " + csvFileName + "\r");
        
        for (var k = 0; k < notRenamedBookmarks.length; k++) {
            WriteToFile(notRenamedBookmarks[k].name);
        }
        WriteToFile("\r");
    }
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function ReadCsvData(csvFile) {
    var line, temp, csvRecord, tempArr;
    var csvData = [];
    csvFile.open("r");
    
    while (!csvFile.eof) { 
        line = csvFile.readln();
        if (line.match(/Volume/) != null) continue; // skip the header
        if (line == "") continue; // skip empty lines
        if (/;{2,}/g.test(line)) continue; // skip empty lines with two or more semicolons
        if (/,{2,}/g.test(line)) continue; // skip empty lines with two or more commas
        tempArr = line.split('""","""');
        csvData.push(tempArr);
    }
 
    csvFile.close();
    
    return csvData;
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function ProcessBookmarks() {
    var bookmark, findWhat, changeTo;
    
    for (var i = 0; i < gAllBookmarks.length; i++) {
        bookmark = gAllBookmarks[i];
        for (var j = 0; j < gCsvArr.length; j++) {
            findWhat = gCsvArr[j][1].replace(/(^"+|"+$)/g, "");
            changeTo = gCsvArr[j][3].replace(/(^"+|"+$)/g, "");
            if (findWhat == "" || changeTo == "") continue; // skip if there is a missing value from the column B or D (the search and replace columns)
            
            if (findWhat == bookmark.name) {
                bookmark.name = changeTo;
                bookmark.label = "ok";
                gCounter++;
            }
        } // for j 
    } // for i
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function ErrorExit(error, icon) {
    alert(error, gScriptName + " - " + gScriptVersion, icon);
    exit();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function WriteToFile(text) {
    var file = new File("~/Desktop/" + gDocName + ".txt");
    if (file.exists) {
        file.open("e");
        file.seek(0, 2);
    }
    else {
        file.open("w");
    }
    file.write(text + "\r"); 
    file.close();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetFileName(fileName) {
    var str = "";
    var res = fileName.lastIndexOf(".");
    if (res == -1) {
        str = fileName;
    }
    else {
        str = fileName.substr(0, res);
    }
    return str;
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------
function GetDate() {
    var date = new Date();
    if ((date.getYear() - 100) < 10) {
        var year = "0" + new String((date.getYear() - 100));
    } else {
        var year = new String ((date.getYear() - 100));
    }
    var dateString = (date.getMonth() + 1) + "/" + date.getDate() + "/" + year + " " + date.getHours() + ":" + date.getMinutes() + ":" + date.getSeconds();
    return dateString;
 }
//--------------------------------------------------------------------------------------------------------------------------------------------------------

Could you send me an example of you csv file?

I can't give you the "real" csv-file because it was given me by my client and I keep such materials confidential.

I created samples myself and posted them here. I don't have Excel 2011 for Mac so they were created on PC -- some hidden characters may be different.

Here is how it looks in Excel:

A csv exported from Excel 2011 (Mac) looks like so:

And here is a sample of csv-file exported from Excel 2007 (PC):

PC version -- in "save as" dialog box, there is an option: CSV separated by commas (I have Russian version of Excel -- it's my own translation), but in fact the exported file is semicolon separated.

MAC version -- my client told me that he could export only comma separated files. However, commas are used in text too. That's why cells are enclosed by three pairs of quotes.

This is the main difference between Mac and PC versions of csv-files.

Kas