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

Find and Replace reference an Excel file

New Here ,
Sep 16, 2009 Sep 16, 2009

Hello all,

I apologize if this has been posted before.

I've recently completed putting together a large catalog. I need to replace all of my company's product codes with different product codes for one of our customers (our customer sells our products, but uses their own product codes) in the InDesign files.

I have an excel document that lists all of our product codes in column A and our customer's product codes in column B. Is there any script that exists that would be able to tell InDesign to look at this excel file, find all of the product codes from column A, and replace them with the product codes that are listed in column B?

I don't have any knowledge about writing these scripts, but if someone could tell me that this is even possible and could point me in the right direction, that would help a lot!

Thanks!

TOPICS
Scripting
2.7K
Translate
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

correct answers 1 Correct answer

Community Expert , Sep 17, 2009 Sep 17, 2009

Well, how about this. See if you can follow how it works (it's quite straightforward )

the_table = app.selection[0].tables[0];

app.findChangeTextOptions = null;
with (app.findChangeTextOptions)
{
caseSensitive = true;
wholeWord = true;
}

app.findTextPreferences = null;
app.changeTextPreferences = null;

for (row=0; row<the_table.rows.length; row++)
{
   if (the_table.rows[row].cells[0].contents == '')
     continue;
   app.findTextPreferences.findWhat = the_table.rows[row].cells[0].contents;
   app.changeText

...
Translate
Community Expert ,
Sep 16, 2009 Sep 16, 2009

Getting the right idea is a good start. Couple o'questions here.

1. ID cannot (easily) read an external Excel file, but it would be no problem at all if you can import that table into your document in a separate frame. If you do so, it boils down to replacing what's in column 1 with what's in column 2. Correct?

2. Are those original codes unique? As in, unique enough to be found with absolute certainty? Four-letter codes, for example, would be bad; long, all numerics, and all of equal length is good -- well, as long as there is no chance of accidentally picking up and changing a telephone number (that would be bad again). Mixed alphabetics and numeric would be best (but I suppose you have nothing to say about this at this stage ). Redundancy is good -- do these codes have something like a character or paragraph style, or a font or color that's unique to them?

3. Those product codes, do they all appear in one single long story, or are we talking document-wide changes? (Actually a trivial distinction, but document-wide might also destroy your change table.)

4. Is this a once-off, or would you like to be prepared for the next catalog? (Just to avoid some dirty tricks I'd be prepared to offer, for a one-time-only.)

Given reasonable answers to these, it's possible to write a script that:

  • iterates over the table of changes per row
  • finds what's in column 1
  • changes to column 2

.. while taking care of case sensitivity, entire-words-only, etc. etc. Should be no prob at all.

[Edit] 5. A few nifty tricks were added to CS2, CS3 and to CS4 (as there is no stopping those guys at Adobe), and a few of them changed details of the scripting interface. It'd be nice if I could use the very latest tricks -- what version do you have? (Mac or Windows does not matter -- it's Javascript all the way for me, boyo.)

Translate
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 ,
Sep 17, 2009 Sep 17, 2009

1. Yes, I just want something to run through the file, find product numbers in column A and replace them with what corresponds in column B.

2. Yes, they're unique. Our product code contains numbers and letters, but they contain varying amounts of numbers and letters - ex: F456P, G907RC, and the replacement numbers are numerical, all of them containing 8 numbers, all of them unique - ex: 09548122, 87235008

3. Document wide changes. Right now, the product codes are in their own unique text box because they correspond to a specific image of that product. We wanted to be able to easily move around items within the catalog in case there was a change or addition.

4. It would be nice to be prepared for the next catalog.

5. I have CS3.

Is that enough information?  I appreciate it very much, thank you! I'm glad to know that it's at least possible!

Translate
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 ,
Sep 17, 2009 Sep 17, 2009

Well, how about this. See if you can follow how it works (it's quite straightforward )

the_table = app.selection[0].tables[0];

app.findChangeTextOptions = null;
with (app.findChangeTextOptions)
{
caseSensitive = true;
wholeWord = true;
}

app.findTextPreferences = null;
app.changeTextPreferences = null;

for (row=0; row<the_table.rows.length; row++)
{
   if (the_table.rows[row].cells[0].contents == '')
     continue;
   app.findTextPreferences.findWhat = the_table.rows[row].cells[0].contents;
   app.changeTextPreferences.changeTo = the_table.rows[row].cells[1].contents;
   app.activeDocument.changeText();
}

Copy, paste in the ExtendScript Toolkit editor that came with InDesign. Save as "ChangeByTable.jsx" into your scripts folder. When saved in the correct place, it will immediately become available in the Scripts panel.

To use: insert your table with from-to replacements somewhere in your document. The first column should hold the find texts, the 2nd the replacement -- one set per row. You don't have to worry if part of the table is overset (not entirely visible) -- the script doesn't care about that.

Then select the text frame that contains the table, and run the script. If it worked, you will see that the 1st column contains the same text as the 2nd.

Translate
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 ,
Sep 17, 2009 Sep 17, 2009

I appreciate your help! I feel bad that you went to all this trouble though, becayse what I want to do is slightly different. Instead of changing the information in the table (in the second column), I just want to use the table as a reference to replace other text that is throughout the document.

So for example, I have about 30 text boxes on one page, each with 3 or more product codes. I'm looking for a way to replace the product codes with these NEW numerical product codes by referencing a table (and in this table, I have the old product codes in column A and the new numerical ones in column B).

Any ideas?  Thank you so much!

Translate
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 ,
Sep 17, 2009 Sep 17, 2009

Well, that's what the script does -- I checked before posting . Did you try it? What doesn't work?

[Edit] Oh, I think I get it. Yes -- the contents of the table itself is also changed; that's because it works on the entire document. Honestly.

Translate
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 ,
Sep 18, 2009 Sep 18, 2009

Oh, it worked! I didn't try it originally, I just misunderstood what the script was supposed to do on your description. You do have to show the whole table though, it can't be hidden in the text box - it will only change those that you can fully see in the table.  Which isn't a big deal, I'm just letting you know.

It's awesome though, this will save me so much time! Thank you so much!

Translate
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
Guest
Feb 18, 2013 Feb 18, 2013

I know it's been more than 3 years since this thread ended, but I'm hoping someone can help me.

I'm using ID CS4 on Windows and am trying to do the same thing as the OP. I followed all the instructions (or so I believe I did) but the only numbers that changed were the numbers in the table. The part numbers that are actually in the document weren't changed. They are in text boxes and are not in tables.

Any suggestions?

Thanks,

Lloyd

Translate
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
Guest
Feb 21, 2013 Feb 21, 2013
LATEST

Hi All, I was searching yesterday for a script that will help batch convert sterling prices to euro prices using a table. This script works great. the only problem I have found is it does not work on the hidden part of the table. My table is 610 rows, I set the type to 2pt so I can get as much in as possible then paste this onto the A4 page, most of the table is still not showing and the conversion only seems to work on what is visible. As a workaround I can split my conversion table into 4 and run the script once on each table to get all the conversions.

Translate
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
Guest
Sep 17, 2009 Sep 17, 2009

Easy, Indesign ships with a script called 'FindChangeByList' this script sucks in a text file, so all you have to do is copy your 2 excel columns in to the find & replace sections of this file and run the script, this will change document wide or on a selection. I'm gonna do a video tutorial of this technique soon showing exactly how to do this as its a bit odd to set up but incredibly useful.

Translate
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 ,
Sep 17, 2009 Sep 17, 2009

That would be incredibly helpful! I saw that script and tried to read it, but I didn't fully understand it so I decided to not mess with it. A video on how to set it up though would be great!

Translate
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