1 Reply Latest reply on May 3, 2014 4:04 AM by Kasyan Servetsky

    Search and replace, contents from excel file

    DawidRuzica

      Hello!

      I found this cool script and was wondering if something can be adjusted :

       


      var myFile = File.openDialog("Choose a tab delimitted txt file:");
      if (!myFile){
      exit();
      }     
      
      myFile.open('r');
      while (myFile.eof==false){
           line=myFile.readln();
           line = line.split("\t");
           myFind = line[0];
           myChange = line[1];
           app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.nothing;
           app.findGrepPreferences.findWhat = myFind;
           app.changeGrepPreferences.changeTo=myChange;
           app.documents.everyItem().changeGrep();
           app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.nothing;
           }
      myFile.close();
      

      What I have is excel document with first column of skus (tons of them) . I would like to look them up in exisiting Indesign files (they exist on pages) and create a new layer with text from column 2 3 and 4 of Excel.

       

      For example look up sku 112233 --> then create new text box right above it (new layer) with item 112233 $1.99 $2.99 $4.99. If possible to also keep font colors styles from excel ?

       

      the existing script replaces value directly in exisiting text box.

       

      Also can this search be "to match who words only" ?

       

      thank you very much!

        • 1. Re: Search and replace, contents from excel file
          Kasyan Servetsky Level 5

          Here's the final version of the script in case someone is interested:

          /* Copyright 2014, Kasyan Servetsky
          May 3, 2014
          Written by Kasyan Servetsky
          http://www.kasyan.ho.com.ua
          e-mail: askoldich@yahoo.com */
          //======================================================================================
          var scriptName = "Make Labels - 1.3",
          doc, data, layer, parStyle, objStyle, swatch,
          horShift = 0,
          verShift = -0.02;
          
          PreCheck();
          
          //===================================== FUNCTIONS ======================================
          function Main() {
              app.findGrepPreferences = app.changeGrepPreferences = NothingEnum.NOTHING;
              app.findGrepPreferences.findWhat = ".+";
              app.findGrepPreferences.appliedCharacterStyle = "sku price";
              var foundItems = doc.findGrep();
              for (var i = 0; i < foundItems.length; i++) {
                  ProcessLink(foundItems[i]);
              }
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function ProcessLink(foundItem) {
              var line = GetLine(foundItem.contents);
              if (line != null) {
                  var x = foundItem.horizontalOffset + horShift;
                  var y = foundItem.baseline + verShift;
                  var page = foundItem.parentTextFrames[0].parentPage;
                  var textFrame = page.textFrames.add(layer, {contents: line, geometricBounds: [y-.12, x, y, x+.8], appliedObjectStyle: objStyle});
                  textFrame.fit(FitOptions.FRAME_TO_CONTENT);
              }
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function GetLine(num) {
              for (var i = 0; i < data.length; i++) {
                  if (data[i][0] == num) {
                      return data[i][0] + "  " + data[i][1];
                  }
              }
              return null;  
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function ReadFile(file) {
              var line, tempArr,
              data = [];
            
              file.open("r");
            
              while (!file.eof) {
                  line = file.readln();
                  tempArr = line.split("\t");
                  data.push([tempArr[0], tempArr[1]]);
              }
          
              file.close();
              return data;
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function PreCheck() {
              if (app.documents.length == 0) ErrorExit("Please open a document and try again.", true);
              doc = app.activeDocument;
              if (!app.activeDocument.saved) ErrorExit("The current document has not been saved since it was created. Please save the document and try again.", true);
              var filePath = doc.filePath.absoluteURI + "/catalog.txt";
              var file = new File(filePath);
              if (!file.exists) ErrorExit("\"catalog.txt\" should be in the same folder as the active document.", true);
              data = ReadFile(file);
              doc.viewPreferences.horizontalMeasurementUnits = doc.viewPreferences.verticalMeasurementUnits = MeasurementUnits.INCHES;
              layer = MakeLayer("Hyperlink");
              parStyle = MakeParStyle("Label", {appliedFont: "Minion Pro", fontStyle: "Regular", pointSize: 5});
              swatch = MakeColor("===== Label =====", ColorSpace.CMYK, ColorModel.process, [0, 0, 100, 0]);
              objStyle = MakeObjStyle("Label", {   enableParagraphStyle: true,
                                                              appliedParagraphStyle: parStyle,
                                                              enableFill: true,
                                                              fillColor: swatch,
                                                              enableStroke: false,
                                                              enableStrokeAndCornerOptions: false
                                                              });
            
              Main();
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function MakeObjStyle(name, properties) {
              var objStyle = doc.objectStyles.item(name);
              if (!objStyle.isValid) {
                  objStyle = doc.objectStyles.add({name: name});
                  if (properties != undefined) objStyle.properties = properties;
              }
              return objStyle;
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function MakeParStyle(name, properties) {
              var parStyle = doc.paragraphStyles.item(name);
              if (!parStyle.isValid) {
                  parStyle = doc.paragraphStyles.add({name: name});
                  if (properties != undefined) parStyle.properties = properties;
              }
              return parStyle;
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function MakeLayer(name, layerColor) {
              var layer = doc.layers.item(name);
              if (!layer.isValid) {
                  layer = doc.layers.add({name: name});
                  layer.move(LocationOptions.AT_BEGINNING);
                  if (layerColor != undefined) layer.layerColor = layerColor;
              }
              return layer;
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function MakeColor(colorName, colorSpace, colorModel, colorValue) {
              var color = doc.colors.item(colorName);
              if (!color.isValid) {
                  color = doc.colors.add({name: colorName, space: colorSpace, model: colorModel, colorValue: colorValue});
              }
              return color;
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------
          function ErrorExit(error, icon) {
              alert(error, scriptName, icon);
              exit();
          }
          //--------------------------------------------------------------------------------------------------------------------------------------------------------