10 Replies Latest reply on Sep 11, 2016 8:31 AM by dflfjgjl45453

    Help with excel import and delete pages script

    dflfjgjl45453

      Hello. I'll try to make it as simple as possible. I have some data from excel (saved as tab-delimited) that I need to import into a 5 page PDF. I am using the script below to import, and it works very well. All the fields import and saves individual PDF's (one for each record). However, I need to expand that functionality by deleting certain pages before it saves the document by looking at different checkboxes. Code below is what I use to import the records.

       

      // specify the filename of the data file
      var fileName = "/Users/MacMike/Desktop/Test.txt";  // the tab delimited text file containing the data
      var outputDir = "/Users/MacMike/Desktop/Dump/";    // make sure this ends with a '/'
      
      var err = 0;
      var idx = 0;
      while (err == 0) {
          err = this.importTextData(fileName, idx);    // imports the next record
      
          if (err == -1)
              app.alert("Error: Cannot Open File");
          else if (err == -2) 
              app.alert("Error: Cannot Load Data");
          else if (err == 1)
              app.alert("Warning: Missing Data");
          else if (err == 2)
              app.alert("Warning: User Cancelled Row Select");
          else if (err == 3)
              app.alert("Warning: User Cancelled File Select");
          else if (err == 0) {
              this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf"); // saves the file
              idx++;
          }
      }
      

       

      As I mentioned before importing works amazingly well.

       

      My PDF consists of 5 pages (p0=registration-info, p1=Dir-contract, p2=ndarp-trademark agreement, p3=oyb-contract, p4=oyb-trademark agreement). My idea is that upon importing the data, the script will look at some checkboxes and determine which contracts to delete out of the PDF and then save it. I wrote out the syntax of what I think it should look like. I am not a programmer, and know just a little. I looked through the documentation and this is what I came up with. I just don't know how to combine it to work. Below is code I came up with.

       

      var dir = this.getField("Associate Director"); // checkbox
      var aldir = this.getField("Alumni Director"); // checkbox
      
      var fac = this.getField("Facilitator"); // checkbox
      var alfac = this.getField("Alumni Facilitator"); // checkbox
      
      var oyb = this.getField("Optimize Your Brain"); //checkbox
      var poyb = this.getField("DVD and Workbook Previously Purchased"); // checkbox
      
      // Below are all the possible training options.
      if (dir.value=="Checked" || aldir.value=="" || fac.value=="" || alfac.value=="" || oyb.value=="" || poyb.value=="") {
          this.deletePages({nStart:3, nEnd:4})
      }
      else if (dir.value=="" || aldir.value=="Checked" || fac.value=="" || alfac.value=="" || oyb.value=="" || poyb.value=="") {
          this.deletePages({nStart:3, nEnd:4})
      }
      
      else if (dir.value=="" || aldir.value=="" || fac.value=="Checked" || alfac.value=="" || oyb.value=="" || poyb.value=="") {
          this.deletePages({nStart:2, nEnd:4})
      }
      else if (dir.value=="" || aldir.value=="" || fac.value=="" || alfac.value=="Checked" || oyb.value=="" || poyb.value=="") {
          this.deletePages({nStart:2, nEnd:4})
      }
      
      else if (dir.value=="" || aldir.value=="" || fac.value=="Checked" || alfac.value=="" || oyb.value=="Checked" || poyb.value=="") {
          this.deletePages({nStart:2, nEnd:2})
      }
      else if (dir.value=="" || aldir.value=="" || fac.value=="" || alfac.value=="Checked" || oyb.value=="Checked" || poyb.value=="") {
          this.deletePages({nStart:2, nEnd:2})
      }
      else if (dir.value=="" || aldir.value=="" || fac.value=="Checked" || alfac.value=="" || oyb.value=="" || poyb.value=="Checked") {
          this.deletePages({nStart:2, nEnd:2})
      }
      else if (dir.value=="" || aldir.value=="" || fac.value=="" || alfac.value=="Checked" || oyb.value=="" || poyb.value=="Checked") {
          this.deletePages({nStart:2, nEnd:2})
      }
      
      else if (dir.value=="Checked" || aldir.value=="" || fac.value=="" || alfac.value=="" || oyb.value=="Checked" || poyb.value=="") {
          this.deletePages(none) // I realize this is incorrect. Just showing that this option results in no deleted pages.
      }
      else if (dir.value=="" || aldir.value=="Checked" || fac.value=="" || alfac.value=="" || oyb.value=="Checked" || poyb.value=="") {
          this.deletePages(none) // I realize this is incorrect. Just showing that this option results in no deleted pages.
      }
      else if (dir.value=="Checked" || aldir.value=="" || fac.value=="" || alfac.value=="" || oyb.value=="" || poyb.value=="Checked") {
          this.deletePages(none) // I realize this is incorrect. Just showing that this option results in no deleted pages.
      }
      else if (dir.value=="" || aldir.value=="Checked" || fac.value=="" || alfac.value=="" || oyb.value=="" || poyb.value=="Checked") {
          this.deletePages(none) // I realize this is incorrect. Just showing that this option results in no deleted pages.
      }
      
      else if (dir.value=="" || aldir.value=="" || fac.value=="" || alfac.value=="" || oyb.value=="Checked" || poyb.value=="") {
          this.deletePages({nStart:1, nEnd:2})
      }
      else (dir.value=="" || aldir.value=="" || fac.value=="" || alfac.value=="" || oyb.value=="" || poyb.value=="Checked") {
          this.deletePages({nStart:1, nEnd:2})
      }
      

       

      How do I combine these two so that I can create a temp document import my data, check the status of checkboxes and delete the appropriate pages and save the file then go to the next record? I have the first part done. It imports great, and saves right, but I don't know what to do next. Thanks for any help!

       

      Or is there another way to do this?


      Michael

        • 1. Re: Help with excel import and delete pages script
          George_Johnson MVP & Adobe Community Professional

          The value of a check box when it's not selected is the string "Off", not blank, "off", or anything else. Its value when selected is whatever the export value is set to. So you'll first need to correct your code, which should work. You would add it after you import the row of data and before you save the file. It would make sense to include it as a function that you can call.

          1 person found this helpful
          • 2. Re: Help with excel import and delete pages script
            Karl Heinz Kremer Adobe Community Professional

            Here is what I would do: Convert the script that analyzes the document and deletes the pages into a function, and then call this function in the line just before the file is saved (line 21 in your first script).

            1 person found this helpful
            • 3. Re: Help with excel import and delete pages script
              dflfjgjl45453 Level 1

              Ok, I took both of your suggestions as best I know now. I shortened the code to just include a couple of the deletePages calls. I'm not certain I set up the function the right way. The export value is set to "Checked". What about the last "else if"? What do I do since I don't want any pages deleted? Do I just delete that "this.deletePages() statement? Let me know what you think.

               

              // variables for importing excel data
              var err = 0;
              var idx = 0;
              var fileName = "/Users/MacMike/Desktop/Test.txt";  // the tab delimited text
              var outputDir = "/Users/MacMike/Desktop/Dump/";    // make sure this ends with a '/'
              
              // Variables for file analysis
              var dir = this.getField("Associate Director"); // checkbox
              var aldir = this.getField("Alumni Director"); // checkbox
              var fac = this.getField("Facilitator"); // checkbox
              var alfac = this.getField("Alumni Facilitator"); // checkbox
              var oyb = this.getField("Optimize Your Brain"); //checkbox
              var poyb = this.getField("DVD and Workbook Previously Purchased"); // checkbox
              
              function seekandDestroy(dir, aldir, fac, alfac, oyb, poyb) {
                  if (dir.value=="Checked" || aldir.value=="off" || fac.value=="off" || alfac.value=="off" || oyb.value=="off" || poyb.value=="off") {
                      this.deletePages({nStart:3, nEnd:4})
                  }
                  else if (dir.value=="off" || aldir.value=="Checked" || fac.value=="off" || alfac.value=="off" || oyb.value=="off" || poyb.value=="off") {
                      this.deletePages({nStart:3, nEnd:4})
                  }
                  else if (dir.value=="Checked" || aldir.value=="off" || fac.value=="off" || alfac.value=="off" || oyb.value=="Checked" || poyb.value=="off") {
                      this.deletePages(none) // I realize this is incorrect. I don't know how to state this "do nothing"
                  }
                      // the rest of the code here. Shortened for simplicity.
              }
              
              while (err == 0) {
                  err = this.importTextData(fileName, idx);    // imports the next record
              
                  if (err == -1)
                      app.alert("Error: Cannot Open File");
                  else if (err == -2) 
                      app.alert("Error: Cannot Load Data");
                  else if (err == 1)
                      app.alert("Warning: Missing Data");
                  else if (err == 2)
                      app.alert("Warning: User Cancelled Row Select");
                  else if (err == 3)
                      app.alert("Warning: User Cancelled File Select");
                  else if (err == 0) {
                      seekandDestroy();
                      this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf"); 
                      idx++;
                  }
              }
              
               
              
              • 4. Re: Help with excel import and delete pages script
                George_Johnson MVP & Adobe Community Professional

                As mentioned earlier, you need to compare the check box values to the string "Off", not "off" or anything else.

                • 5. Re: Help with excel import and delete pages script
                  Karl Heinz Kremer Adobe Community Professional

                  You may want to read up on JavaScript functions. You declare your function with a number of parameters:

                   

                  function seekandDestroy(dir, aldir, fac, alfac, oyb, poyb) { ... }

                   

                  But, when you call it, you are not providing any arguments:

                   

                   

                  seekandDestroy();
                  

                   

                   

                  When you define a function that expects parameters, you have to specify these parameters when you call the function:

                   

                  function doSomething(paramA, paramB) {
                      return paramA + paramB;
                  }
                  
                  var x = doSomething(3, 5);
                  

                   

                  I assume that the section in which you define your variables (lines 7 to 13) should be added just before the call to seekandDestroy() in line 42, but you would then have to call it like this:

                   

                  seekandDestroy(dir, aldir, fac, alfac, oyb, poyb);
                  

                   

                  Or, you can move lines  7 to 13 into the function body, and remove the parameter list from the function definition.

                  1 person found this helpful
                  • 6. Re: Help with excel import and delete pages script
                    dflfjgjl45453 Level 1

                    George,

                     

                    Do you mean like this:

                     

                    (dir.value=="off" && aldir.value=="Checked" && fac.value=="off" && alfac.value=="off" && oyb.value=="off" && poyb.value=="off")
                    

                     

                    I just realized this || meant "or" and && meant and.

                    • 7. Re: Help with excel import and delete pages script
                      George_Johnson MVP & Adobe Community Professional

                      Yes, but you need to use "Off", not "off":

                       

                      (dir.value=="Off" && aldir.value=="Checked" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Off")

                       

                       

                      in addition to what Karl instructed.

                      1 person found this helpful
                      • 8. Re: Help with excel import and delete pages script
                        dflfjgjl45453 Level 1

                        Wow. Ok. I got it working beautifully. There was a lot of trial and error. Because the script as it was would, would open the original PDF, delete the required pages, then save the file. Then as it would go to the next record there would be missing pages in the open PDF and bomb out. I hunted and searched for a way to do this. I found "this.insertPages" in the documentation. So what finally worked was move the "saveAs" into the function and put it under each check variation. So now during the check for the checkboxes it does the check, deletes the required pages, saves, inserts the deleted pages back in from the original file, exits the function, and finally goes to the next record.

                         

                        Below is my final script:

                         

                        // This code looks at an excel (tab delimited) file, imports the records into a PDF form 10 Pages long.
                        // Then checks a series of checkboxes and deletes the pages that aren't associated with first page.
                        // After it deletes these pages it reinserts the deleted pages so it can do the checks for the next record in the (tab delimited) file.
                        
                        // variables for importing excel data
                        var err = 0;
                        var idx = 0;
                        var fileName = "/Users/MacMike/Desktop/Test.txt";  // the tab delimited text
                        var outputDir = "/Users/MacMike/Desktop/Dump/";    // make sure this ends with a '/'
                        
                        //Checking a bank of 6 checkboxes and determine which pages need to be deleted, save the file, and the insearch the deleted pages again.
                        function seekandDestroy() {
                            if (dir.value=="Checked" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Off") {
                                this.deletePages({nStart:6, nEnd:9})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:5, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:6, nEnd:9 });
                            }
                            else if (dir.value=="Off" && aldir.value=="Checked" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Off") {
                                this.deletePages({nStart:6, nEnd:9})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:5, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:6, nEnd:9 });
                            }
                        
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Checked" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Off") {
                                this.deletePages({nStart:3, nEnd:9})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:2, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:3, nEnd:9 });
                            }
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Checked" && oyb.value=="Off" && poyb.value=="Off") {
                                this.deletePages({nStart:3, nEnd:9})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:2, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:3, nEnd:9 });
                            }
                        
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Checked" && alfac.value=="Off" && oyb.value=="Checked" && poyb.value=="Off") {
                                this.deletePages({nStart:3, nEnd:5})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:2, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:3, nEnd:5 });
                            }
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Checked" && oyb.value=="Checked" && poyb.value=="Off") {
                                this.deletePages({nStart:3, nEnd:5})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:2, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:3, nEnd:5 });
                            }
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Checked" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Checked") {
                                this.deletePages({nStart:3, nEnd:5})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:2, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:3, nEnd:5 });
                            }
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Checked" && oyb.value=="Off" && poyb.value=="Checked") {
                                this.deletePages({nStart:3, nEnd:5})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:2, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:3, nEnd:5 });
                            }
                        
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Checked" && poyb.value=="Off") {
                                this.deletePages({nStart:1, nEnd:5})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:0, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:1, nEnd:5 });
                            }
                            else if (dir.value=="Off" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Checked") {
                                this.deletePages({nStart:1, nEnd:5})
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                                this.insertPages({nPage:0, cPath:"/Users/MacMike/Desktop/TT Reg & Contracts.pdf", nStart:1, nEnd:5 });
                            }
                            else if (dir.value=="Checked" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Checked" && poyb.value=="Off") {
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                            }
                            else if (dir.value=="Checked" && aldir.value=="Off" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Checked") {
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                            }
                            else if (dir.value=="Off" && aldir.value=="Checked" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Checked" && poyb.value=="Off") {
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                            }
                            else if (dir.value=="Off" && aldir.value=="Checked" && fac.value=="Off" && alfac.value=="Off" && oyb.value=="Off" && poyb.value=="Checked") {
                                this.saveAs(outputDir + this.getField("Full Name (First Last)").value + "-" + this.getField("Event Title").value + ".pdf") 
                            }
                        }
                        
                        // Imports records does the above function then go the next record, all the while doing error reporting.
                        while (err == 0) {
                            err = this.importTextData(fileName, idx);    // imports the next record
                        
                            if (err == -1)
                                app.alert("Error: Cannot Open File");
                            else if (err == -2) 
                                app.alert("Error: Cannot Load Data");
                            else if (err == 1)
                                app.alert("Warning: Missing Data");
                            else if (err == 2)
                                app.alert("Warning: User Cancelled Row Select");
                            else if (err == 3)
                                app.alert("Warning: User Cancelled File Select");
                            else if (err == 0) {
                                var dir = this.getField("Associate Director"); // checkbox
                                var aldir = this.getField("Alumni Associate Director"); // checkbox
                                var fac = this.getField("Facilitator"); // checkbox
                                var alfac = this.getField("Alumni Facilitator"); // checkbox
                                var oyb = this.getField("Optimize Your Brain Site Coordinator"); //checkbox
                                var poyb = this.getField("DVD and Workbook Previously Purchased"); // checkbox
                                seekandDestroy(dir, aldir, fac, alfac, oyb, poyb); //performs the above function
                                idx++; //goes to next record
                            }
                        }
                        
                        • 9. Re: Help with excel import and delete pages script
                          Karl Heinz Kremer Adobe Community Professional

                          Now that you mention it, it's obvious Sorry that I did not think this through enough, but congrats to figuring it out. Chances are you learned quite a bit in the process.

                          • 10. Re: Help with excel import and delete pages script
                            dflfjgjl45453 Level 1

                            I did learn a lot! In fact, after I did this I made a script that would then take the created pdf and grab the email address out of the PDF then email the finished contract to the registrant with a reformatted email.