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

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

Explorer ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

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

TOPICS
Import and export , Scripting

Views

1.7K

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 Beginner ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

There is no class that can read an excel file within indesign, to read the excel file you would have to unzip the xlsx file and read the xml files relating to the sheet you are interested in, alternatively you could use a csv file, i.e. a text file. If you need to use the excel file I can develop a class for reading the file for a fee.

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
Explorer ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

A CSV would be fine, but I'm not familiar with scripts and it still gives me the same error: line 6 - Excel does not have a constructor: var excel = new Excel(excelFile);

 

How would I fix this?

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 Beginner ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

// 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");

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

if (myCSVfile != null)
{
myCSVfile.open("r");
var myCSVFileRead = myCSVfile.read().split("\n");

// loop through all rows in the sheet
for (var i = 1; i <= myCSVFileRead.length; i++) {
// get the code and description from the Excel sheet
var columns = myCSVFileRead[row].split(',"');
var code = columns[0];
var description = columns[1];

// 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;
}
}
}
}
}

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
Explorer ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

This seems to have got further, but now I have the following error:

DavidW10_1-1670947838915.png

 

Appreciate your help with this 🙂

 

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 ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

A CSV would be fine

 

Hi @DavidW10 , If the file can be .csv you can use Extendscript to read the csv file as text and convert the contents into an array:

 

So this is my test  .csv:

 

Screen Shot 3.png

 


var s = readFile(File(Folder.desktop + "/Pricelist.csv"));
var r = s.split("\n")
var t = [];
var row;
for (var i = 0; i < r.length; i++){
    row = r[i].split(",")
    t.push(row)
};   


var code;
var price;
for (var j = 1; j < t.length; j++){
    //j is the row, 0 is column 1, and 1 is column 2
    code = t[j][0];
    price = t[j][1];

    $.writeln("Product: " + code + "    Price: " + price);
    //Product: Widget 1    Price: $204.00
    //Product: Widget 2    Price: $57.50
    //Product: Widget 3    Price: $99.99
};   

/**
* Gets text from a text file  
* @ param path to the file
* @ return the file’s text 
*/

function readFile(p) {
	var f = new File(p);
	f.open("r");  
	var x = f.read();  
	f.close();
	return x;
}

 

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
Explorer ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Thanks for your response, i've tried this code after editing the file path to match mine but it doesn't seem to do anything when I run it on InDesign, no errors, but doesn't work either - not sure if i'm doing something wrong here? I even changed the CSV document to match your titles.

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 Beginner ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Before running the script, do you select the table to be updated? Or would you simply like the script to search for all codes and update the tables where they are found?

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 Beginner ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Can you share with me a page of your indesign file and the relevant cvs file with the price update?

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

The sample I posted doesn’t do anything, just shows how to convert the csv text into a nested array, I was assuming you would incorporate it into your table code. 

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Hi @DavidW10 , there are some other problems with your code’s loops. Here’s an annotated example using the nested array from a csv file on a document containing tables—I haven’t included any error checking :

 

 

 

 

 


var doc = app.activeDocument;

//get all of the documents tables
var dt = doc.stories.everyItem().tables.everyItem().getElements();

//read a csv file and convert its contents into an array
var t = csvArray(File(Folder.desktop + "/Pricelist.csv"))

// loop through all rows in the nested array
var code, description, table, cell;
for (var i = 1; i < t.length; i++){
    // get the code and description from the csv array
    code = t[i][0];
    description = t[i][1];
    // loop through all cells in the first column of the InDesign table
    for (var j = 0; j < dt.length; j++) {
      table = dt[j];
      for (var k = 0; k < table.rows.length; k++) {
        cell = table.rows[k].cells[0];
        //if the code in the InDesign table matches the code in the Excel sheet
        //insert the description from the csv into the third column of the InDesign table
        if (cell.contents == code) {
            while (table.columns.length < 3) {
                table.columns.add()
            } 
            table.rows[k].cells[2].contents = description
        }
      }
    }
  } 



/**
* Gets text from a csv file and make a nested array 
* @ param path to the file
* @ return an array 
*/

function csvArray(p) {
	var f = new File(p);
	f.open("r");  
	var x = f.read();  
	f.close();
    var r = x.split("\n")
    var t = [];
    var row;
    for (var i = 0; i < r.length; i++){
        row = r[i].split(",")
        t.push(row)
    };
	return t;
}

 

 

 

 

 

Before and after:

Screen Shot 23.png

Screen Shot 21.png

 

csv is attached

 

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Another two cents from me 😉 brainstorming ideas 😉 and in no way dismissing your code @rob day - just trying to make it quicker.

 

As the codes - from the OP's file - look quite unique - I think it would be quicker to search for those and then check if Parent is a cell in the first column ?

 

Instead of checking the same cells over and over again ...

 

â–’â–º ID-Tasker / ID-Tasker Server - work smart not hard â—„â–’

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Hi @Robert Tkaczyk , My first post was to show how the text from a cvs might be converted into a nested array with ExtendScript— as an alternative to new Excel(), which breaks right away.

 

I think @DavidW10 ran my sample assuming it would do something other than construct the array, so I simply adapted the loops in the original code to use the  array. I’m sure your search idea could be faster if you want to post the code.

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

â–’â–º ID-Tasker / ID-Tasker Server - work smart not hard â—„â–’

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Just to be clear my array code is JavaScript, not VB. @DavidW10  wants to get the content from an external .cvs file, not a selected InDesign table.

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

You wanted an example code - have not specified the language 😉

 

I'm not JS expert like you and I don't think it makes a difference to @DavidW10 what is the source - Excel, CSV or Table in InDesign - so as long as it works ... and he is on Windows anyway ...

 

You are much better than me with JS - so it should be pretty straighforward for you to adapt my algorithm ?

 

â–’â–º ID-Tasker / ID-Tasker Server - work smart not hard â—„â–’

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

See the original post—the sample code is JS. David is asking how to get the contents from an excel or cvs file, not an ID table.

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

Of course, no problem.

 

â–’â–º ID-Tasker / ID-Tasker Server - work smart not hard â—„â–’

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 ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

Have a look at the following discussion, it uses VBScript to read Excel file you can use a similiar thing to use Applescript on a MAC.

https://community.adobe.com/t5/indesign-discussions/get-data-directly-from-excel/m-p/8943275#M45960

-Manan

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
Explorer ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

Hi there, I am currently on windows and using Notepad++, I get this error when trying to use that script you have linked.

 

Appreciate your help!

 

DavidW10_0-1670938386742.png

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
Advisor ,
Dec 13, 2022 Dec 13, 2022

Copy link to clipboard

Copied

Hello @DavidW10,

 

The error message shows that the script is failing when trying to find the .csv file.

Make sure the .csv file is in the same location and is named as shown in the top portion of the error message, or change the file path in the script to point to the location of the .csv file.

 

Regards,

Mike

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

The directory ScriptsScripts Panel looks dodgy. It schould be Scripts/Scripts Panel

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 ,
Dec 14, 2022 Dec 14, 2022

Copy link to clipboard

Copied

I've done a lot of tools to build catalogs directly from Excel - but they are to complicated to post the source code - so here is an idea for you - instead of trying to read and process external files - just copy Excel as another table and work on tables in InDesign - source & destination - will be much easier for you.

 

â–’â–º ID-Tasker / ID-Tasker Server - work smart not hard â—„â–’

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
Explorer ,
Dec 15, 2022 Dec 15, 2022

Copy link to clipboard

Copied

I don't have any scripting experience at all and aren't really sure what to do with the scripts i've been given (other than put them in the right place and run them) but our Price List is 136 pages long with about 4 tables on each page, and our prices change often so to manually change each price would take a long, long time. (some aren't done by one multiplication, a range of products could be by 3% but others 20%). So i'm just trying to get a script to work which will make my life 20 times easier haha.

 

Appreciate you all trying to help me

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 Beginner ,
Dec 15, 2022 Dec 15, 2022

Copy link to clipboard

Copied

I can help you, plese send me a csv file and one page indesign example.

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