10 Replies Latest reply on Feb 21, 2013 2:52 AM by MF99

    Find and Replace reference an Excel file

    odynenydo

      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!

        • 1. Re: Find and Replace reference an Excel file
          [Jongware] Most Valuable Participant

          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.)

          1 person found this helpful
          • 2. Re: Find and Replace reference an Excel file
            odynenydo Level 1

            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!

            • 3. Re: Find and Replace reference an Excel file
              low Jackson

              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.

              1 person found this helpful
              • 4. Re: Find and Replace reference an Excel file
                odynenydo Level 1

                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!

                • 5. Re: Find and Replace reference an Excel file
                  [Jongware] Most Valuable Participant

                  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.

                  • 6. Re: Find and Replace reference an Excel file
                    odynenydo Level 1

                    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!

                    • 7. Re: Find and Replace reference an Excel file
                      [Jongware] Most Valuable Participant

                      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.

                      • 8. Re: Find and Replace reference an Excel file
                        odynenydo Level 1

                        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!

                        • 9. Re: Find and Replace reference an Excel file
                          celuloyd

                          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

                          • 10. Re: Find and Replace reference an Excel file
                            MF99

                            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.