Using a script to input prices from an Excel sheet into tables on InDesign

[DavidW10]

Hi there,

I'm trying to find a script that will match a product code in the first column of a table with a product code (column A) in an excel sheet and pull back the price (column b) and enter it into the third column of a table on InDesign. 

I'm worried it might be a bit too extensive for a simple script to help with. 

This might be a long shot, but I've tried for a couple of hours now to get a script I found to work on InDesign to input prices into tables from an Excel sheet. Script below:

// get the active InDesign document
var doc = app.activeDocument;

// open the Excel file and get the sheet
var excelFile = new File("/path/to/excel/file.xlsx");
var excel = new Excel(excelFile);
var sheet = excel.getSheet("Sheet1");

// loop through all rows in the sheet
for (var i = 1; i <= sheet.getLastRow(); i++) {
  // get the code and description from the Excel sheet
  var code = sheet.getValue(i, "A");
  var description = sheet.getValue(i, "B");

  // loop through all cells in the first column of the InDesign table
  for (var j = 0; j < doc.tables.length; j++) {
    var table = doc.tables[j];
    for (var k = 0; k < table.rows.length; k++) {
      var cell = table.rows[k].cells[0];

      // if the code in the InDesign table matches the code in the Excel sheet
      if (cell.contents == code) {
        // insert the description from the Excel sheet into the third column of the InDesign table
        var newCell = table.rows[k].cells.add();
        newCell.contents = description;
      }
    }
  }
}

Even after editing the file path to go my excel sheet (attached), it gives me an erorr on line 6 - Excel does not have a constructor:

var excel = new Excel(excelFile);

Any help or pointing me into the right direction would help a lot, thanks!

David

[DavidW10]

Thank you!

See attached for the example CSV file and Price List page

[stefanob71205804]

#targetengine indesign

var myCSVfile = File.openDialog("Select CSV file", "CSV files:*.csv", false);

if (myCSVfile != null)
{
var w = new Window ("palette","Update report");
w.orientation = "column";
var contP = w.add ("group");
contP.orientation = "row";
//w.orientation = "row";
var panel1 = contP.add ("panel");
panel1.add("staticText",[0,0,200,20],"Codes not present in the INDD");
var list1 = panel1.add("listbox", [0, 0, 200, 100]);
var panel2 = contP.add ("panel");
panel2.add("staticText",[0,0,200,20],"Codes not present in the CSV");
var list2 = panel2.add("listbox", [0, 0, 200, 100]);
var panel3 = contP.add ("panel");
panel3.add("staticText",[0,0,200,20],"Updated codes");
var list3 = panel3.add("listbox", [0, 0, 200, 100]);

var finded = {};
// Code indesign file
var cif = {};
// Code csv file
var ccf = {};
// Code updated
var cupd = {};

myCSVfile.open("r");
var myCSVFileRead = myCSVfile.read().split("\n");

var w_2 = new Window ("palette","Update in progress");
var panel_text = w_2 .add ("panel");
var text_update = panel_text.add("staticText",[0,0,200,20],"Reading data 0/"+(myCSVFileRead.length-1));
w_2.show();
var totalToSearch = 0;
for(var row = 1;row<myCSVFileRead.length;row++)
{
text_update.text = "Reading data "+row+"/"+(myCSVFileRead.length-1);
if(myCSVFileRead[row] != "")
{
var columns = myCSVFileRead[row].split(";");
var code = columns[0];
if (code.length > 1) {
totalToSearch++;
ccf[code.toUpperCase()] = columns[1];
}
}
}
var idx = 1;
for (var cd in ccf) {
text_update.text = "Search code " + idx + "/" + totalToSearch;
var tosearch = cd;
app.findTextPreferences = null
app.changeTextPreferences = null;
app.findTextPreferences.findWhat = tosearch;
finded[tosearch] = [];

var allCode = app.activeDocument.findText();

for(var i=0;i<allCode.length;i++)
{
var tf = allCode[i];//.parentTextFrames[0];
if (tf.parent.constructor.name === "Cell") {
if (tf.parent.contents == cd) {
cif[tosearch] = tf.parent.parentRow.cells[2];
finded[tosearch].push(tf.parent.parentRow.cells[2]);
}
}
}
idx++;
}

for(var i in ccf)
{
if(!cif.hasOwnProperty(i))
{
list1.add("item",i);
}
}

for(var i in cif)
{
if(!ccf.hasOwnProperty(i))
{
list1.add("item",i);
}
}

var total = 0;

for(var i in finded)
{
total++;
}

var count = 0;
for(var i in finded)
{
text_update.text = "Updating prices "+(count+1)+"/"+(total);
if(ccf.hasOwnProperty(i))
{
cupd[i] = i;
for (var x = 0; x < finded[i].length; x++) {
finded[i][x].contents = String(ccf[i]);
}
list3.add("item",i);
}

count++;
}

w_2.hide();
w.show();
}

[rob day]

Hi @stefanob71205804 , Thanks for the code, I wonder if there will be a problem reading a.csv file and assuming the delimiter between cells is a semi colon, and the semi colon doesn’t also exist in the cell texts. I saved the .xlxs file @DavidW10 posted from OSX’s Numbers app, and the delimiter in the exported .csv is a comma, so your script doesn’t work for me—$.writeln(columns[1]) returns as undefined.

In my script I split the rows on the comma delimiter, but that also creates a problem with the prices which get split on their commas—£1,567.00 becomes £1

Another option might be to temporarily place the .xlxs file as text where the delimiter between the cells would be a tab, then remove it after the updates?

[stefanob71205804]

Send me your saved csv file.

[stefanob71205804]

Change split char from ";" to ", ":

var columns = myCSVFileRead[row].split(", ");

If you want is possible to request split sequence characters before reading file.

[brianp311]

fwiw at this point in the thread, I find Trevor V's CSV Parser here to be fairly efficient and effective, and more reliable for funky datasets than just relying on split(","). 

[rob day]

Hi @DavidW10 , Here is a save .jsx file of the last code I posted, which asks or an .xlxs file.Not sure if you will run into errors with different files, but this works for me on your samples. @stefanob71205804 ’s csv version might also work for you, but you can copy this .jsx file into your scripts folder and try it from InDesign:

https://shared-assets.adobe.com/link/671f2e6f-1e19-4839-6f21-cd2a7da88c6e

[Laubender]

Hi @DavidW10 ,

maybe the following algorithm would serve your needs:

InDesign's ExtendScript can place an Excel table to a new document. Check method place() with object document.

Further you could use ExtendScript to remove all not necessary columns in that placed table and build two arrays with the contents of each of the remaining table columns: myTable > columns[n].contents where n is the index of the column, 0 for the first column, 1 for the second one.

That gets you the raw data you are looking for.

Regards,
Uwe Laubender
( Adobe Community Expert )

[Laubender]

A slightly different idea based on the one with placing that Excel file.

Note that ExtendScript is able to convert an InDesign table, the two columns of data left, to text.

With a simple GREP expression that is using a positive lookbehind with the article number plus a tab you'll be able to find the corresponding price.

Regards,
Uwe Laubender
( Adobe Community Expert )

[rob day]

Hi Uwe, Is there a way to place the .xlxs as a table, and get the contents in the overflow? David’s table is 2608 rows so if I place it on a letter sized page it overflows and I get this when I check for the contents of row 1000:

var doc = app.activeDocument;
var dt = doc.stories.everyItem().tables.everyItem().getElements();
$.writeln(dt[0].rows.length);//returns 2608
$.writeln(dt[0].rows[1000].cells[0].contents)//returns nothing

but, if I place the .xlxs as text where the seperator is a tab, I can get the overflow text contents:

var doc = app.activeDocument;
var dt = doc.stories.everyItem().paragraphs.everyItem().getElements();
$.writeln(dt.length)//returns 2608
$.writeln(dt[1000].contents)//returns HSP191	 £1,000.00 
$.writeln(dt[1000].contents.split("\t")[0]) //returns HSP191

[Peter Kahrel]

@rob day 

Maybe try . . .cells[0].texts[0].contents

[rob day]

Thanks @Peter Kahrel that works.

[Laubender]

Another trick is to grow the minimum size of the pasteboard to a huge value, set the text frame to autogrow vertically, use a 1 Pt sized typeface for the contents of the table. Plus several text columns in the text frame.



Uwe Laubender

 

PS. For some reasons the Reply button in the forum does not work for me.

It simply vanishes when I click it. So this message comes via mail.

[rob day]

Hi Uwe, @Peter Kahrel ’s suggestion worked. This places an .xlxs in a  text frame and gets the entire table even when it’s not visible. Might be more reliable than reading and spliting .csv;

var doc = app.activeDocument;	
var dt = doc.stories.everyItem().tables.everyItem().getElements();
var xlsf = File.openDialog("Please Choose an XLXS file");
var temp = doc.pages[0].textFrames.add();
temp.place(xlsf);

var xrws = temp.parentStory.tables[0].rows
$.writeln(xrws.length); //returns 2608

for (var i = 1; i < xrws.length; i++){
    $.writeln(xrws[i].cells[0].texts[0].contents) //product cell text
    $.writeln(xrws[i].cells[1].texts[0].contents) //price cell text
}; 

temp.remove()

[Robert at ID-Tasker]

So I was right??

[rob day]

The question was how to read an xlxs or cvs table, so you could add that to your VB script. Searching would certainly be faster than looping with a 2600 row table.

[Robert at ID-Tasker]

What's the difference if you load XLSX / CSV manually or through script ?

[rob day]

Hi Robert, When someone posts code that throws an error, generally I’ll try to suggest a fix and not get into script development. @DavidW10 was trying to read an Excel file and the code was breaking. Yes you are right, searching is faster than looping, but I was limiting my first response to the question about reading an Excel table, and had the csv function in my code library.

[Robert at ID-Tasker]

But my reply with VBA code was AFTER OP admitted he is working on a PC and AFTER someone else posted link to another VBA example...

But then you've shut me down that my response is off topic - even when my code was working perfectly fine and have already done requested task quickly and efficiently ...

[rob day]

I certainly didn’t mean to shut you down, I was just explaining why I didn’t initially volunteer to also write the search code.

[Robert at ID-Tasker]

You've asked for the example code - I've posted complete solution in VBA - which you then criticised that is not JavaScript so is off topic ?!?!

Now you've posted complete solution anyway - based on my idea - but backwards, which I admit, can be quicker in some cases. 

[Laubender]

@rob day wrote: "… Might be more reliable than reading and spliting .csv"

Hi Rob,

placing a very large Excel file could take much more longer than you might think.

Well, that was when I started scripting with InDesign CS4 and CS5. Found that it was more reliable to convert the table to text, export the text, read the text to an array and shift things around in that array…

But that was about 14 years ago.

Things certainly have changed.

Regards,
Uwe Laubender
( Adobe Community Expert )

[rob day]

Hi Uwe, The Excel place time doesn’t seem to be a problem—this takes less than 5 secs to run on @DavidW10 ’s sample files:

var doc = app.activeDocument;	
var dtr = doc.stories.everyItem().tables.everyItem().rows.everyItem().getElements();
var xlsf = File.openDialog("Please Choose an XLXS file");
var temp = doc.pages[0].textFrames.add();
temp.place(xlsf);
var xt = temp.parentStory.tables[0]

for (var i = 0; i < dtr.length; i++){
    if (dtr[i].index > 0 && dtr[i].cells[0].contents !="") {
        try {
        dtr[i].cells[2].contents = textSearch(dtr[i].cells[0].contents, xt)
        }catch(e) {temp.remove()}  
    } 
};   
temp.remove()


/**
* Gets price 
* @ param find string 
* @ param the placed xlxs table 
* @ return the xlxs price text 
* 
*/
function textSearch(f, t){
    app.findTextPreferences = app.changeGrepPreferences = app.findChangeGrepOptions = null;
    app.findTextPreferences.findWhat = f;
    var ft = app.activeDocument.findText()
    for (var i = 0; i < ft.length; i++){
        if (ft[i].parent.parent == t) {
            return ft[i].parent.parentRow.cells[1].texts[0].contents
        } 
    };  
}

[Robert at ID-Tasker]

Your approach is a bit different to mine 🙂 

Your code will be quicker if there is less codes in the INDD document than in the XLSX / CSV file - when my will be quicker if there is less in the XLSX / CSV than in the INDD document 🙂