My wife and I are celebrating our 25th year of marriage this year—AND 25 years of working together as graphic designers, specializing in book design for independent authors.
We’ve used Macs and Adobe Systems software all along, and I’ve written various AppleScript scripts to automate various processes from time to time.
One of our products is a 1200-page directory of healthcare professionals, published about twice a year. About 1500 records from a customer-provided spreadsheet are imported into our database (QSA Toolworks’ Helix) where the data is collated and sorted by various criteria, and then output to a tab-delimited text file which is fed into an AppleScript which has Adobe inDesign generate the pages of the directory. I can’t imagine doing this book without that AppleScript script!
And now today, I finished another script for my wife to use on every book she designs which, again, generates inDesign pages while creating linked text frames placed outside of the pages on the pasteboard into which the manuscript is flowed. She’s done this by hand so far. But we just got a new customer who is going to have us create a legal case book of about 1000 pages. So we decided to spend the time writing this script which will make the page setup process take only a few minutes.
PS, I love Script Debugger for an AppleScript editor! Its features make scripting so much easier. One thing I avoided (for some strange reason) was using debug mode. But once I watched the LNS video, it really helped me figure out the sometimes weird AppleScript syntax, along with inDesign’s AppleScript dictionary. I could step through the script and see immediately that something didn’t work and be able to figure it out so much faster.
Congratulations on a successful marriage and career!
It is a real tribute to both of you to have worked and lived so closely for so many years. Few can do that.
Nice. But I’m wondering why bother with the “export tab-delimited text” step? I did a similar project back in the 90s, except the layout tool was Quark XPress, and the script that ‘poured’ the text into the pages just pulled it directly from Helix, with no need for a text file at all.
For completeness’ sake, I also did a small script that sent data back to Helix, in case somebody discovered a typo while proofreading the catalog.
That was the project that immersed me in AppleScript, and (of course) Script Debugger was indispensable for figuring out XPress’ dictionary.
I export a tab-delimited text file actually from Adobe inDesign, because the spreadsheets come to us in MS Excel format, and I really dislike that app. So I open the spreadsheets in Apple Numbers. I edit the spreadsheet data to convert state and province names to the state number values in my states relation. There are also specialty names (e.g. Cardiology, Dentistry, Neurology, etc.) which I edit to specialty numbers from my specialties relation. So when I’ve done all my data edits, I export the Numbers data to the text file. I sometimes do some preliminary stripping of double spaces and various MS PC text artifacts using find and replace in inDesign.
I have not been able to interest the customer in improving the “cleanliness” of the data they collect from their customers. So I have to do a lot of cleanup myself.
Once I clean up the data and export the text file, that’s what I load into Helix.
Then, when I run the script to make the pages, that’s when I have Helix open and use the “utilize {}” notation in the script to read the collection.
In the original post you wrote that you… “output to a tab-delimited file which is fed into [a script that] has Adobe InDesign generate the pages.” Hence, my confusion.
But no matter which end of the process we’re dealing with, exporting to an intermediate file seems like a step best avoided. If the data comes to you in Excel (or Number or whatever) why not read the data via AppleScript and send it directly to Helix (or wherever)?
You say you open the spreadsheet and edit (manually?) the data: I’m betting there are some basic rules you can codify and encapsulate in an AppleScript, saving even more time.
Or maybe not; you know the data, we don’t. I’m just saying anytime you ‘export to a text file’ to move the data on to the next step, there’s probably an opportunity to script it and streamline the process further.
You make some good points, Matt. I appreciate your thoughtful input. Come to think of it, I could script Helix posting from the spreadsheet data directly! I’ll look at that for the next book we do. Thanks!
