Last Monday, I began my new job as a multimedia specialist at Rowan University’s School of Osteopathic Medicine. One of the benefits of coming in to a new job is that you see get to look at the various tasks being done with fresh eyes.
One such area was in the yearbooks that media services makes for each incoming class. Early on, class sizes were small, and it was possible to lay out each yearbook one by one in Microsoft Word. However, with the 2018 class growing beyond 150 students, formatting each student’s photo, name, degree, university, specialty and hobbies quickly became unmanageable (especially when late adds or drops meant that each student had to be shifted over individually).
Enter InDesign and Excel (or any spreadsheet really).
To create this yearbook, we will be merging the data from our spreadsheet into a template we create in InDesign. To help populate this spreadsheet, we first need a script that will help point InDesign to the images associated with each student. Thankfully, the folks over at InDesignSecrets (where I found the initial steps to this walkthrough) already have such a script which you can download here.
To install the script, open the scripts panel in InDesign by going to Window –> Utilities –> Scripts. You can then right-click on User and select Reveal In Finder. You should now be able to drop the file you downloaded, imagesToCSV104.jsx, into the Scripts Panel folder and you should see it in InDesign immediately.
Next, you need to point the script to your folder of images. Before you do this, you want to make sure that your images are renamed in some standard format. In my case, I chose lastName_firstName so that I can sort alphabetically by last name and more easily merge the data with any future data. Now, double-click the script in your scripts panel and it will ask you to select a folder with images. This will create a .csv file that includes two columns; the filename and the path to the image.
This is where the InDesignSecrets write-up leaves you, but we want more information than just a name and image. Instead, we are going to open that .csv up in a spreadsheet program (Excel, Google Spreadsheet, Numbers, etc.) and add additional data.
This can be done in a few ways. In my case, I had to enter all of the data by hand from freshman orientation forms, but in the future, this form should be digital and the two columns from the script can simply be pasted in. When saving the new .csv file, be sure that you save it into the folder with the images.
Now its time to build the template for your yearbook. For my template, I want one graphic frame and six text frames underneath that (for the six columns of data from my spreadsheet). Once you have your frames laid out, you can map the various columns of the spreadsheet to what frame it will merge with in InDesign. First, click on the Data Merge panel (if you don’t see it, go to Window –> Utilities –> Data Merge). Clicking on the top right of that panel will allow you to select your data source. In this case, navigate to the folder with your images and select the .csv you made earlier. You should now see the fields that you created in the spreadsheet. To map them to the template, click on an empty frame, and then click on the field that should go there. You can also set fonts, alignment, font size, etc.
With your template mapped out, you can now click on Create Merged Document on the bottom right of the Data Merge panel. Set your records to merge Multiple Records per document page, and then select preview to get an idea of how each record will show up on your new template. You can then play with the margins and spacing until you are happy with the overall layout.
Once you are happy with the general layout, hit OK and InDesign will create a new document with the merged items. It may also pop up an overset text report, showing you where the text went over the text frame. In my case, because some students didn’t fill out every field, and some had answers that are far longer than others, I am going to go back in to clean up some of the formatting. Once complete, new entries can easily be added to the spreadsheet, and the data merged again, eliminating the hassle of having to reposition students individually.