Help with excel import and delete pages script

Report · Sep 01, 2016

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

Report · Sep 01, 2016

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.

Report · Sep 01, 2016

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

Report · Sep 01, 2016

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

Report · Sep 01, 2016

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

Report · Sep 01, 2016

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.

Report · Sep 01, 2016

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.

Report · Sep 01, 2016

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.

Report · Sep 02, 2016

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

Report · Sep 02, 2016

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.

Report · Sep 11, 2016

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.

Help with excel import and delete pages script

1 Correct answer