• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Get data directly from Excel

Guru ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

Hi all,

Quite often I have to write scripts that use data originated from an Excel worksheet. I (and most scripters) believed that the easiest approach was to use a CSV-file exported from Excel. However, this takes an extra step for the user so I decided to write a function that opens an Excel book in background, reads the data from its first spreadsheet and returns array.

The function has three arguments:

  1. excelFilePath — The platform-specific full path name for the xlsx-file — fsName. If you pass it as a string, make sure to double the backslashes in the path like in the line below:
     var excelFilePath = "D:\\My Test Folder\\SampleBook.xlsx";
  2. splitChar — [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)
    If it isn't set, semicolon will be used by default.
  3. sheetNumber — [Optional] the worksheet number: either String or  Number. If it isn't set, the first worksheet will be used by default 

The data in Excel

The same data transfered to InDesign as Array

The function (current version):

Main();

function Main() {

    // The platform-specific full path name for the xlsx-file -- fsName

    // If you pass it as a string, make sure to double the backslashes in the path like in the line below

    var excelFilePath = "D:\\My Test Folder\\SampleBook.xlsx";

   

    // [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)

    // If it isn't set, semicolon will be used by default

    var splitChar = ";";

   

    // [Optional] the worksheet number: either string or number. If it isn't set, the first worksheet will be used by default

    sheetNumber = "1";

    var data = GetDataFromExcelPC(excelFilePath, splitChar, sheetNumber); // returns array

}

function GetDataFromExcelPC(excelFilePath, splitChar, sheetNumber) {

    if (typeof splitChar === "undefined") var splitChar = ";";

    if (typeof sheetNumber === "undefined") var sheetNumber = "1";

    var vbs = 'Public s, excelFilePath\r';

    vbs += 'Function ReadFromExcel()\r';

    vbs += 'Set objExcel = CreateObject("Excel.Application")\r';

    vbs += 'Set objBook = objExcel.Workbooks.Open("' + excelFilePath + '")\r';

    vbs += 'Set objSheet =  objExcel.ActiveWorkbook.WorkSheets(' + sheetNumber + ')\r';

    vbs += 'objExcel.Visible = False\r';

    vbs += 'matrix = objSheet.UsedRange\r';

    vbs += 'maxDim0 = UBound(matrix, 1)\r';

    vbs += 'maxDim1 = UBound(matrix, 2)\r';

    vbs += 'For i = 1 To maxDim0\r';

    vbs += 'For j = 1 To maxDim1\r';

    vbs += 'If j = maxDim1 Then\r';

    vbs += 's = s & matrix(i, j)\r';

    vbs += 'Else\r';

    vbs += 's = s & matrix(i, j) & "' + splitChar + '"\r';

    vbs += 'End If\r';

    vbs += 'Next\r';

    vbs += 's = s & vbCr\r';

    vbs += 'Next\r';

    vbs += 'objBook.close\r';

    vbs += 'Set objBook = Nothing\r';

    vbs += 'Set objExcel = Nothing\r';

    vbs += 'End Function\r';

    vbs += 'Function SetArgValue()\r';

    vbs += 'Set objInDesign = CreateObject("InDesign.Application")\r';

    vbs += 'objInDesign.ScriptArgs.SetValue "excelData", s\r';

    vbs += 'End Function\r';

    vbs += 'ReadFromExcel()\r';

    vbs += 'SetArgValue()\r';

   

    app.doScript(vbs, ScriptLanguage.VISUAL_BASIC, undefined, UndoModes.FAST_ENTIRE_SCRIPT);

   

    var str = app.scriptArgs.getValue("excelData");

    app.scriptArgs.clear();

   

    var tempArrLine, line,

    data = [],

    tempArrData = str.split("\r");

   

    for (var i = 0; i < tempArrData.length; i++) {

        line = tempArrData;

        if (line == "") continue;

        tempArrLine = line.split(splitChar);

        data.push(tempArrLine);

    }

   

    return data;

}

I wrote and tested it in InDesign CC 2017 and Excel 2016 (Version 16) on Windows 10. I wonder if/how it works in other versions of Excel. Also, I'd like to get some feedback: bug reports, suggestions and new ideas.

Here's the permanent link on my site.

— Kas

TOPICS
Scripting

Views

13.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
People's Champ ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

Funny enough I am involved in a XLSX based project and I could proceed the file with this :

var xlsx = File ( '/myFile.xlsx' );

var fo = Folder ( Folder.temp+"/myFolder" );

fo.create();

app.unpackageUCF ( xlsx, fo );

then you can introspect xml files

HTH

Loic

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

Hi Kas and Loic,

yeah, funny. I'm also involved in a XLSX based project right now.

Kasyan asked for ideas:

What I'm doing is to place the XSLX file with an InDesign document and read out the data from there with table.contents.

That could return a String object (one cell in the table) or an Array of Strings (more cells than one in the table).

Advantage:

No need to have Excel installed.

Disadvantage:

Sometimes the import filter does not return a table object after placing, but a simple text frame.
I already saw this problem with InDesign CS4 using the UI's place command. So I always have to check for a Table object. If there is none I will convert the text—if there is any text—to table.

Other obstacles:

If the Excel file resides on the user's computer everything is working as expected.

But if the user is connected via VPN and the Excel file resides somewhere on the network, InDesign's place command ( by scripting, not via the UI ) will return nothing reasonable. If the user is placing the Excel file via UI all the data flows to a page. That's a bit odd…
At least a check on the returned contents where the placing is done by scripting suggests that the Excel file written is empty.

Currently I cannot test with the customer's computer to tackle this problem.

The customer is on Windows 10 I think and is using InDesign CC 2017.

Maybe I first have to duplicate the Excel file from the network to the local machine to proceed to tackle the VPN problem?

Waiting until a EOF is reached? Then Loic's approach could be the better solution. Have to dig into that…

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

Thanks Loic and Uwe for your prompt replies! 

Uwe, I used the approach you suggested a few days ago in this post.

—Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

Ah. Thanks.

Forgot to mention that I use page.place() to get the Excel data in. You are suggesting textFrame.place() .
With my page.place() sometimes a text frame with a table object will be returned, sometime a text frame with paragraphs where the cell contents is separated by tabs. I cannot see a reason why one thing should happen before the other one.

For my customer's VPN related problem:

Don't know if something will change, if I use textFrame.place() over page.place() .

I let you know…

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

My idea was to make a temporary text frame somewhere on the pasteboard first to avoid messing up the layout.

—Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 16, 2017 Feb 16, 2017

Copy link to clipboard

Copied

Yes. That's a good reason.

In my case I know the page is empty. 🙂

And I was using the fifth argument that is available with page.place() to autoflow the contents.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
People's Champ ,
Feb 17, 2017 Feb 17, 2017

Copy link to clipboard

Copied

I did think of flowing the table so I can use it to generate a database. But in my case, the excel is so huge that InDesign takes ages to render. I was looking for alternatives ways of achieving this.

I also gave this node module a try. It just worked like a charm (and in a blink) on my mac but i could never set it on my windows:

json-cli

I have looked at dll/framework or other CLI tools but with no success.

In my last approach (unpackageUCF) I get XML fiels I can easily read and work with. But the width of the table makes teh database construction time consuming anyway.

FWIW

Loic

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 17, 2017 Feb 17, 2017

Copy link to clipboard

Copied

Loic.Aigon  wrote

…

I did think of flowing the table so I can use it to generate a database. But in my case, the excel is so huge that InDesign takes ages to render. I was looking for alternatives ways of achieving this.

…

Hi Loic,

thank you for pointing to json-cli.

FWIW: There are several constraints with InDesign tables and incoming data from Excel files.

1. The number of columns of the table object in InDesign is limited to 200 when flowing data from an external source.

That could be overcome with a trick where one can flow in text and convert to table afterwards, but a table with more than 200 columns will not survive an IDML roundtrip.

2. The number of rows is limited to 20,000, I think.


3. And then we have the contents array of a table, that sometimes cannot be build with the values unless the table flows into frames and is not overset.

Reading out that array from a huge, freshly imported Excel file where most of the table is overset can yield in empty items of the returned array after the rows hit the overset. Whereas it is filled with contents if the table is flowing through text frames without overset.

4. Speed. You already mentioned it. And InDesign document file size as well.

The longer the table in one story, the slower InDesign will process it. Even if the text of the table is formatted uniformly.
Maybe you can get along gaining some speed by not showing the document window when processing it.

Nevertheless the file size of the document will be huge if compared to other stories with uniform formatting and similar length of contents where no tables are involved.

In one of my projects where I had to build hundreds of InDesign tables out of one huge Excel file I placed the table, read out the contents array, did some restructuring of contents in memory and build a text file out of the result, did not assign the changed contents again using table.contents = , but removed the old table, imported the text file as text and converted to table. That was way faster than assigning the new contents.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Feb 17, 2017 Feb 17, 2017

Copy link to clipboard

Copied

Hi Uwe,

I wonder if placing an Excel table into a separate -- temporary -- InDesign document created from template/scratch would make things run faster.

Loic mentioned XML and that reminded me another option: we can export XML from Excel as it is described here and read it by script using it a a sort of database (without actually placing it into an InDesign document).

I made such a script a few years ago for a company that makes 'year books' for schools and it worked fast. They provided me with InDesign templates and a sample XML which contained the info: image names, paths, scaling, rotation, etc.

BTW I'm already writing the analogous function for Mac.

Regards,

Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Feb 18, 2017 Feb 18, 2017

Copy link to clipboard

Copied

Yesterday I wrote the function for Mac (at the bottom of the page), but it's totally untested since I was polishing it up on my home PC today; will be able to test it only on Wednesday when I get to my work.

— Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Me too.

I have taken the place route. But, I think I have found a problem with the Excel place method.

My spread sheet.

Screen Shot 2017-02-20 at 09.29.50.png

The result after doing a place with the UI. ( I get the same result using my code )

Screen Shot 2017-02-20 at 09.31.33.png

This is a snippet from a very large spread sheet. Maybe my spread sheet is corrupt, I don't think so, as when I export from excel to a tab delimited file the -1 appears.

I am using Mac CC 2015.4

Here is my place code.

//===

function getxlsxData( xFile )

{

    var retValue = "";

    var myPreset = app.documentPresets.add();

    myPreset.createPrimaryTextFrame = true;

    var tmpDoc = app.documents.add(false, {documentPreset:myPreset});

    with( newFrame = tmpDoc.pages[0].textFrames[0] )

    {

        place ( xFile );

        retValue = parentStory.contents;

    }

    myPreset.remove ();

    tmpDoc.close ( SaveOptions.NO );

    return ( retValue );

}

//=====

P.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Pickory  wrote

… Maybe my spread sheet is corrupt, I don't think so, as when I export from excel to a tab delimited file the -1 appears. …

Hi Pickory,

no.

I think your spread sheet's cells are formatted in the wrong way.

Should be all over "text" formatted, I think.

At least that should work best with InDesign's import filter.

Another problem we maybe could exclude, if we are working with Excel files directly and not using the Excel Import filter.

Did you try several values with TableFormattingOptions ?

TableFormattingOptions.EXCEL_FORMATTED_TABLE

TableFormattingOptions.EXCEL_FORMAT_ONLY_ONCE

TableFormattingOptions.EXCEL_UNFORMATTED_TABLE

TableFormattingOptions.EXCEL_UNFORMATTED_TABBED_TEXT

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Hi Uwe,

Thanks for taking the time.

I am not sure what you mean "Should be all over "text" formatted, I think".

I don't really want to ask the Excel user to do anything to their spread sheet.

I haven't try any of the table options as I really just want the tabbed text.

P.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Hi Pickory,

yes: indeed I mean the various formatting options with table cells in Excel.


And with some of them I also had problems importing the right values to InDesign.

Loic's method, unpacking the xslx file and inspecting the XML data has its charm.

Other than that the best bet could be to open the file with Excel, select all cells and do the formatting as text before importing to InDesign.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Thanks Uwe,

I was worried that might be the case. For the moment I am going to ask Excel to do all the work, export to tabbed text.

P.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
People's Champ ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Hi Pickory,

CSV is fine most of the times but it can have its downsides. Dealing with carriage returns inside data is a nightmare and character encodings  can be a problem too.

Loic's method, unpacking the xslx file and inspecting the XML data has its charm.

It has also its dark side that I keep digging and digging

Retrieving text strings is trivial but for other types of data, the stored string is often nothing but what is rendered in MS Excel. I get this case wher a cell hosts a number such as 20112017 which is internally stored as 201120e17 (don't ask me why). So you can't presume the stored string really stands for the displayed value.

The only "interest" I can see in reading the inner xml files is to get agnostic of the OS and avoid dependancies (like Excel installed) but of course if one can guarantee the context, there is no point of not using MS Excel in combination with Applescript or VB.

Loic

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 20, 2017 Feb 20, 2017

Copy link to clipboard

Copied

Loic.Aigon  wrote

… It has also its dark side that I keep digging and digging

…

Oh yes. Exactly that is what I feared.

With the XML there should be a formatting command—an attribute perhaps—for the cell and Excel has to interpret that and render the contents accordingly. So. Back to square one: We need Excel installed to get the right contents. Maybe also the right version of Excel? I'm not sure…

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Feb 21, 2017 Feb 21, 2017

Copy link to clipboard

Copied

I just made a quick test adding to my test sheet different types of data:

  • date
  • formula
  • negative numbers (e.g. -1)
  • long numbers (e.g.1000000000000000000 which Excel displays as 1E+18)

I all cases my function transfers them to InDesign exactly as I see it in Excel.

I have a very basic knowledge about Excel because I don't use it in my everyday practice.

A couple of years ago I played a little with VBA scripting to find out if it's possible to transfer data from Excel to InDesign and vice versa. The answer was: yes, it's possible and quite easy! I didn't spend much time on this: read the chapter about Excel in "Mastering VBA for Microsoft Office 2007" by Richard Mansfield, and the first tutorial that came across.

It took me about 10 minutes to google for 'AppleScript Excel' tutorial and read it so that I could figure out how to do the same things on Mac.

The stuff I used for learning is quite old, and I used only basic features so I guess it should work with old versions of Excel. Also, I tested my function in the latest version on Windows (version 16) and in Excel 2011 (version 14) on Mac.

I wonder is there any dark side in my approach? Maybe it's too slow? (I haven't tested with huge spreadsheets).

I realize that I just scratched the surface: digging further opens up huge possibilities -- InDesign interacting directly with such apps as Access, Word, Outlook, databases -- maybe even with any scriptable  app installed on the computer.

— Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Feb 27, 2017 Feb 27, 2017

Copy link to clipboard

Copied

Here I wrote the function for Mac. I tested it in InDesign CC 2015 and Excel 2011 (Version 11) on Mac OS X 10.10 (Yoshemite).

Theoretically it should work in all versions of InDesign from CS (version 3) to CC 2017 (version 12), but I don't have them all in my disposal to test it.

I noticed that the visibility of Excel doesn't work on Mac: it's always visible no matter if you set it to true or false.

Main();

function Main() {

    // The platform-specific full path name for the xlsx-file -- fsName

    // If you pass it as a string, make sure to double the backslashes in the path like in the line below

    var excelFilePath = "Test:My Folder:SampleBook.xlsx";

   

    // [Optional] the character to use for splitting the rows in the spreadsheed.

    // If it isn't set, pipe (|) will be used by default   

    var splitCharRows = "|";

   

    // [Optional] the character to use for splitting the columns in the spreadsheed: e.g. semicolon (;) or tab (\t)

    // If it isn't set, semicolon will be used by default

    var splitCharColumns = ";";

   

    // [Optional] the worksheet number: either string or number. If it isn't set, the first worksheet will be used by default

    var sheetNumber = "1";

   

     // Returns an array in case of success; null -- if something went wrong: e.g. called on PC, too old version of InDesign.

    var data = GetDataFromExcelMac(excelFilePath, splitCharRows, splitCharColumns, sheetNumber);

}

function GetDataFromExcelMac(excelFilePath, splitCharRows, splitCharColumns, sheetNumber) {

    if (File.fs != "Macintosh") return null;

    if (typeof splitCharRows === "undefined") var splitCharRows = "|";

    if (typeof splitCharColumns === "undefined") var splitCharColumns = ";";

    if (typeof sheetNumber === "undefined") var sheetNumber = "1";

    var appVersion,

    appVersionNum = Number(String(app.version).split(".")[0]);

   

    switch (appVersionNum) {

        case 12:

            appVersion = "CC 2017";

            break;

        case 11:

            appVersion = "CC 2015";

            break;

        case 10:

            appVersion = "CC 2014";

            break;           

        case 9:

            appVersion = "CC";

            break;       

        case 8:

            appVersion = "CS 6";

            break;

        case 7:doScript

            if (app.version.match(/^7\.5/) != null) {

                appVersion = "CS 5.5";

            }

            else {

                appVersion = "CS 5";

            }

            break;

        case 6:

            appVersion = "CS 4";

            break;

        case 5:

            appVersion = "CS 3";

            break;

        case 4:

            appVersion = "CS 2";

            break;

        case 3:

            appVersion = "CS";

            break;           

        default:

        return null;

    }

    var as = 'tell application "Microsoft Excel"\r';

    as += 'open file \"' + excelFilePath + '\"\r';

    as += 'set theWorkbook to active workbook\r';

    as += 'set theSheet to sheet ' + sheetNumber + ' of theWorkbook\r';

    as += 'set theMatrix to value of used range of theSheet\r';

    as += 'set theRowCount to count theMatrix\r';

    as += 'set str to ""\r';

    as += 'set oldDelimiters to AppleScript\'s text item delimiters\r';

    as += 'repeat with countRows from 1 to theRowCount\r';

    as += 'set theRow to item countRows of theMatrix\r';

    as += 'set AppleScript\'s text item delimiters to \"' + splitCharColumns + '\"\r';

    as += 'set str to str & (theRow as string) & \"' + splitCharRows + '\"\r';

    as += 'end repeat\r';

    as += 'set AppleScript\'s text item delimiters to oldDelimiters\r';

    as += 'close theWorkbook saving no\r';

    as += 'end tell\r';

    as += 'tell application "Adobe InDesign ' + appVersion + '\"\r';

    as += 'tell script args\r';

    as += 'set value name "excelData" value str\r';

    as += 'end tell\r';

    as += 'end tell';

   

    if (appVersionNum > 5) { // CS4 and above

        app.doScript(as, ScriptLanguage.APPLESCRIPT_LANGUAGE, undefined, UndoModes.ENTIRE_SCRIPT);

    }

    else { // CS3 and below

        app.doScript(as, ScriptLanguage.APPLESCRIPT_LANGUAGE);

    }

    var str = app.scriptArgs.getValue("excelData");

    app.scriptArgs.clear();

   

    var tempArrLine, line,

    data = [],

    tempArrData = str.split(splitCharRows);

   

    for (var i = 0; i < tempArrData.length; i++) {

        line = tempArrData;

        if (line == "") continue;

        tempArrLine = line.split(splitCharColumns);

        data.push(tempArrLine);

    }

   

    return data;

}

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 30, 2019 Jan 30, 2019

Copy link to clipboard

Copied

Hi! I have been attempting to use your script. I get an array from the excel doc I am testing on, but I think I am accessing things wrong.

I simply put this towards the bottom of the script

retrieved.push(data)

  return data;

I then took, retrieved[0].toString(), and put it in an indesign document. I didn't see the column or row separators, so I can't get the table out of it that I'd like to. Please tell me what I'm doing wrong. I'm sure many things, but if you can guide me, I'd really appreciate it. Thanks!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Jan 31, 2019 Jan 31, 2019

Copy link to clipboard

Copied

Hi there,

No, you don't have to mess with the function: add something at the end. Also, the array elements are already strings so there's no need to use .toString().

Let's illustrate it with a working example accompanied with before and after test files.

For example, we want to replace fonts using the data in an Excel spreadsheet.

2019-01-31_11-40-02.png

Before

2019-01-31_11-38-12.png

After

2019-01-31_11-36-56.png

Note: Letter Gothic wasn't changed since it is not in the list.

The function returns a 'ready to use array' of strings. However, I usually use an additional function, like so, to validate the data: skip empty rows/cells, headers, convert strings to numbers, if necessary, etc.

Here's the script:

main();

//--------------------------------------------------------------------------------------------------------------------------------------------------------

function main() {

    try { // if something goes wrong in the try-catch block, the batch processor won't stop here. It will log the error message and continue further

        var newFont, paragraphStyle, characterStyle, changed, report,

        doc = app.activeDocument, // The frontmost document

        paragraphStyles = doc.allParagraphStyles,

        characterStyles = doc.allCharacterStyles,

        scriptFile = getActiveScript(),

        scriptFolder = scriptFile.parent,

        scriptFolderPath = scriptFolder.fsName,

        excelFilePath = scriptFolderPath + "\\Change fonts list.xlsx",

        excelFile = new File(excelFilePath),

        countParStyles = countCharStyles = countLocals = 0;

       

        if (!excelFile.exists) {

            exit();

        }

        var fontList = GetFontListFromExcel(excelFilePath, ";");

        // Change in paragraph styles

        for (var p = 1; p < paragraphStyles.length; p++) {

            paragraphStyle = paragraphStyles

;

            newFont = getNewFont(paragraphStyle.appliedFont.name, fontList);

            if (newFont != null) {

                paragraphStyle.appliedFont = newFont;

                countParStyles++;

            }

        }

        // Change in character styles

        for (var c = 1; c < characterStyles.length; c++) {

            characterStyle = characterStyles;

            newFont = getNewFont(characterStyles.appliedFont + "\t" + characterStyles.fontStyle, fontList);

            if (newFont != null) {

                characterStyles.appliedFont = newFont;

                countCharStyles++;

            }

        }

   

        for (var i = 0; i < fontList.length; i++) {

            app.findTextPreferences = app.changeTextPreferences = NothingEnum.NOTHING;

            newFont = getNewFont(fontList[0], fontList);

            if (newFont != null) {

                app.findTextPreferences.appliedFont = fontList[0];

                app.changeTextPreferences.appliedFont = newFont;

                changed = doc.changeText();

                countLocals += changed.length;

                app.findTextPreferences = app.changeTextPreferences = NothingEnum.NOTHING;

            }

        }

        if (countParStyles == 0 && countCharStyles == 0 && changed.length == 0) {

            report = "No changes have been made to the document.";

        }

        else {

            report = "Changed fonts in " + countParStyles + " paragraph style" + ((countParStyles == 1)  ? ", " : "s, ") + countCharStyles + " character style" + ((countCharStyles == 1)  ? "," : "s,") + " and " + countLocals + " instance"  + ((countLocals == 1)  ? "" : "s") + " of locally formatted text.";

        }

    }

    catch(err) {}

}

//--------------------------------------------------------------------------------------------------------------------------------------------------------

function getNewFont(oldFontName, fontList) {

    var newFontName,

    newFont = null;

    for (var p = 0; p < fontList.length; p++) {

        newFontName = fontList

[1];

        if (oldFontName == fontList

[0]) {

            newFont = app.fonts.itemByName(newFontName);

            if (newFont.index == -1 || !newFont.isValid) {

                newFont = null;

            }

       

            break;

        }

    }

    return newFont;

}

//--------------------------------------------------------------------------------------------------------------------------------------------------------

function getActiveScript() {

    try {

        return app.activeScript;

    }

    catch(err) {

        return new File(err.fileName);

    }

}

//--------------------------------------------------------------------------------------------------------------------------------------------------------

function GetFontListFromExcel(excelFilePath, splitChar) {

    if (typeof splitChar == "undefined") var splitChar = ";";

       

    var vbs = 'Public s, excelFilePath\r';

    vbs += 'Function ReadFromExcel()\r';

    vbs += 'Set objExcel = CreateObject("Excel.Application")\r';

    vbs += 'Set objBook = objExcel.Workbooks.Open("' + excelFilePath + '")\r';

    vbs += 'Set objSheet =  objExcel.ActiveWorkbook.WorkSheets(1)\r';

    vbs += 'objExcel.Visible = False\r';

    vbs += 'matrix = objSheet.UsedRange\r';

    vbs += 'maxDim0 = UBound(matrix, 1)\r';

    vbs += 'maxDim1 = UBound(matrix, 2)\r';

    vbs += 'For i = 1 To maxDim0\r';

    vbs += 'For j = 1 To maxDim1\r';

    vbs += 'If j = maxDim1 Then\r';

    vbs += 's = s & matrix(i, j)\r';

    vbs += 'Else\r';   

    vbs += 's = s & matrix(i, j) & "' + splitChar + '"\r';

    vbs += 'End If\r';

    vbs += 'Next\r';

    vbs += 's = s & vbCr\r';

    vbs += 'Next\r';

    vbs += 'objBook.Close\r';

    vbs += 'Set objBook = Nothing\r';

    vbs += 'Set objExcel = Nothing\r';

    vbs += 'End Function\r';

    vbs += 'Function SetArgValue()\r';

    vbs += 'Set objInDesign = CreateObject("InDesign.Application")\r';

    vbs += 'objInDesign.ScriptArgs.SetValue "excelData", s\r';

    vbs += 'End Function\r';

    vbs += 'ReadFromExcel()\r';

    vbs += 'SetArgValue()\r';

   

    app.doScript(vbs, ScriptLanguage.VISUAL_BASIC, undefined, UndoModes.FAST_ENTIRE_SCRIPT);

   

    var str = app.scriptArgs.getValue("excelData");

    app.scriptArgs.clear();

   

    var tempArrLine, line,

    data = [],

    tempArrData = str.split("\r");

    for (var i = 0; i < tempArrData.length; i++) {

        line = tempArrData;

        if (line == "") continue;

        tempArrLine = line.split(splitChar);

        data.push([

                        eval("\"" + tempArrLine[0] + "\""), // A -- Find

                        eval("\"" + tempArrLine[1]+ "\"") // B -- Change to

                        ]);

    }

    return data;

}

Hope it helps!

— Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 31, 2019 Jan 31, 2019

Copy link to clipboard

Copied

And one could also use the array of strings to fill an already existing table with text contents.

Just do table.contents = data . It does not matter* if the number of table cells match the number of entries in the array.

*There will be no error message if the number of cells is less than the number of array entries.

There will be no error message if the number of cells exceeds the number of array entries.

Regards,
Uwe

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 31, 2019 Jan 31, 2019

Copy link to clipboard

Copied

If either one of you can help me solve this for my Mac, I’ll gladly donate to you or the cause of your choice. I’m investigating the xml path to these worksheets, too, but it seems to me that if I could use Kasyan’s scripts successfully, my situation would be perfect. Thanks again!

Paul

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guru ,
Jan 31, 2019 Jan 31, 2019

Copy link to clipboard

Copied

Make sure to use the function for MAC on my site. I recently updated it for cc2019. The link is at the bottom of OP.

How to use it is explained in the comments (see the stuff  at the top).

Unfortunately, i can't help you with mac. I have it only at work. But i got ill and my doctor told me to stay in bed next week.

The mac version works the same way as the pc version. However, the latter is more advanced. I even used the mac version for developing scripts for Windows at work. So, they are interchangeable.

-- Kas

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines