InDesign CC
A Data Merge Methodology
A linear how-to for merging data into templated layouts in InDesign CC using version CC 2018. This version of the method is developed specifically around Lowe's US projects that may or may not use Spanish translation.
TL;DR – Just get to it, already
If you don't want to spend your life reading this post or have already read through the details below, here is a summary of the steps to take for proper Data Merge.
Open client Excel file, save it immediately to a new working .xls file.
Duplicate the worksheet with the data you need onto a fixed worksheet.
In the fixed worksheet: remove all unneeded data, text outside columns & rows, etc. and generally clean up the data you want
In the fixed worksheet: make sure the field names are the first row and conform to program-friendly formatting. i.e.: alphanumeric and underscores (_) only. For images, make sure to put an apostrophe before the @ that needs to be in front of the field name. Example: '@image.
Create a settings worksheet to hold any values you need to reference in the final to be added or manipulated for the final:
Include the path to the folder containing all your images. Follow older mac specs when typing out the folder name, make sure to include either the absolute path or relative path as described below to the folder plus a semicolon at the end.
Setup any LOOKUP tables you may need for the final, i.e.: to relate data in the spreadsheet to a logo image, etc.
Duplicate that fixed worksheet into a data worksheet. that will be the final data spreadsheet. Enter all the formulas in to make the values exactly as you need them imported into InDesign.
Export the .xls data worksheet to a CSV file. The format should be CSV UTF-8 (Comma delimited).
Open the exported CSV file in VS Code and save the file with UTF-16 LE encoding.
Create your layout in InDesign, making it as clean and exact as you with enough space in the text boxes to receive the longest data being exported. Create the paragraph, character, and object styles you need that will apply to any data coming in as well. Save the file with a tmpl_ prefix.
Select data source in InDesign and choose your CSV file, the fields from the excel sheet should come in properly at this stage.
Replace all your layout text and images on the document page with the respective data merge field placeholders.
Check the Preview checkbox in the bottom left of the palette and move through the records to make sure your layout is working as intended. Fix anything that needs fixing with Preview turned on to make things easier. Update paragraph styles, character styles, and object styles as needed for the final.
Turn off Preview and choose Create Merged Document. Adjust any options in that dialog that are necessary to the success of the merge.
When the document gets merged, you may see a warning of overset text, missing images, etc. You'll need to troubleshoot any of those items manually. Remember that once merged, there's always a couple scenarios where you'll need to add a soft break in a text line, etc. Templates only work to a certain point. Save that final layout file without the tmpl_ prefix.
You should be complete at this point and have two InDesign files (template and merged).
If client changes or a revised spreadsheet come in, make the adjustments to the working Excel file and repeat the merge process. Since you already have a rigged template, you shouldn't have to repeat those steps unless new data needs to be added to the layout. Be sure to Update Data Source in your template before merging.
Schedule for Weds 7/18/18 meeting
Run through a merge quickly, to show:
A. The stages in action
B. Talk about how long to spend on each section
Go through THE STAGES and steps for the subsections of each stage individually.
Go through EXTRAS that will help you on your way as well as develop new skills for tackling variable data template-style designs.
An Intro: Getting Nerdy
The majority of your time in this whole process will probably be spent in Excel and and plain-text coding editor (Visual Studio Code) preparing the data to be merged. The actual layout and merging is the easy part. This is due to a number of factors, these being the big culprits:
You're designers and (most likely) unfamiliar with aspects of Excel like advanced formulas across an entire workbook.
"Normalizing data" is something you heard someone say, then you immediately turned tail and ran quickly the other direction.
Clients don't get all "Hey, let's make sure this content is perfect so they don't spend all sorts of time having to normalize it" when it comes to providing the data.
You went to school for design and don't want to wade around in code. Otherwise, you would've gone to school for programming.
Unfortunately the more advanced production methodologies like Data Merge require getting your hands dirty with the above so it's best to man- and woman-up and…
Practice doing it in real-world scenarios !
When to Data Merge
DataMerge is really meant for automatically creating a lot of layouts that don't have a lot of variations and would otherwise take a large amount of time to do manually. Example: 50+ channel signs for lowe's that have anywhere from a few to a lot of different data and linked images in different sections.
The Stages
There are 4 stages to create final documents from a given table of data and InDesign layout:
1. Normalize & Sanitize
2. Lay It Out
3. Rig It Up
4. Merge Away
Stage 1: Normalize & Sanitize
Software Required
Two pieces of software (beyond the Adobe CC stuff) are absolutely necessary for this methodology to work. They are:
- Microsoft Excel
- Visual Studio Code, which we'll refer to as VS Code in this doc
Together, these will allow you to:
Accept the client's data in a format that every client uses (Excel, yuck).
Normalize that data into clean data that's (almost) ready to go into InDesign.
Quickly revise the data, which WILL CHANGE about 3 minutes after they first send it to you. And about 4-5 times after that. And again just before you submit it to prepress. And after it's been printed at least once.
Make sure it's ready to go into InDesign.
Excel
Excel's a necessary evil in this case, it's the best way to hold data in table form, period. You'll need to bone-up on some formula writing skills so you don't go nuts trying to force it to do something. These are some of the most used formulas/actions you'll need to use in Excel:
CONCAT – Combining the contents of two or more cells together into a new cell, this one is absolutely necessary.
Referencing cells on a different sheet, also necessary.
Forcing values to remain the same when AUTOFILLING.
LOOKUP - allows you to create a reference list, for example equating the name of a vendor brand to a logo file.
CSV Saving
Spreadsheets can only be imported to InDesign in .csv (comma separated value) format. This is a text-only format where the contents of each cell in spreadsheet are separated by commas, they may or may not have quotes around them, and rows are delimited by paragraph breaks. You'll need to:
- Export the file in CSV format from Excel
- Open the CSV file in VS Code and set the encoding
before bringing it into InDesign.
Exporing from Office 365 Excel
You'll need to export in CVS UTF-8 (Comma delimited) format for the worksheet to export and open in VS Code properly. Note that it will NOT work to export from Excel and import into InDesign directly if there are Spanish characters in the data. For this reason, it's best to run it through VS Code every time just to be sure the data is encoded properly to bring into InDesign.
At a bare minimum, this will be VS Code's main purpose: covert the exported CSV data file into a format that InDesign can understand.
Visual Studio Code
VS Code is more than a plain text editor, it’s a code editor. This fills in the Excel gap in the Normalize and Sanitize stage with features like:
Multiple cursor editing, including changing a selection without jumping into find/replace
More powerful find/replace if you choose to use it, including regular expressions
Encoding options
A self-contained application that doesn't require machine permission to install
It's FREE (yay!!!)
Honestly—it’s the best thing Microsoft ever created. There, I was finally able to say they did something right. Now I feel dirty.
But it's true, VS Code rocks.
https://code.visualstudio.com/Download
If you want to check out the features of the program, beyond what we go through in this doc, look at the official documentation at:
https://code.visualstudio.com/docs
REFERENCE: vs-code-playground.txt
Normalizing
Normalizing is the process of taking the client's provided Excel data and formatting it to an InDesign-specific format for Data Merge.
Duplicating data onto different worksheets
You need to take the clients data, remove all the unnecessary stuff, and manipulate that into a straight-forward data table with row-headers for InDesign to recognize.
You'll be creating the following additional sheets, preferably into a complete copy of the original:
original
Actually, it's just renaming whatever they originally provide you. It's important to keep exactly what they sent.
settings
Create a settings sheet to hold your the paths to your images and any consistent LOOKUP data you'll need.
LOOKUP data: LOOKUPs in Excel are darned powerful ways to alter the contents of a cell based on the contents of another cell. Meaning if the client names data one way and you want it to appear in the InDesign file another way without having to find/change the original data, you can use a lookup to handle this. It's appropriate when the provided data is part of categories that are predictable, like a fixed set of icons that'll be placed into a layout based on exact text the client provided. Or vendor logos that differ from product to product.
Paths to images: As I rant about down below, you'll see that linking images in merged files is a giant pain. To ease this pain, you'll want to edit image paths in a much easier way that diredtly in the data file. Keeping one image path that can be altered when the job moves to a different designer/machine is necessary for your and other folks' sanity.
fixed
Or something similar to that name. This will be the original sheet with all the un-needed stuff removed and the columns fixed – thus the named "fixed". You'll need to :
- Remove any top header cells at the top that AREN'T the names of the columns
- Remove any data that aren't part of those columns
- Actually, remove ANYTHING that's not either a header name or data in an actual column
data
You'll duplicate the fixed sheet into a final sheet called data that will ONLY be the data that you want to go into InDesign
What makes up the data
There are two elements that make up every column in the data spreadsheet.
Headers
The first cell at the top of the columns is the header cell, which also serves as the field name when brought into InDesign. Fields are placed directly into the InDesign layout and the the field names are the one link you can see when you import the data.
Headers: A word on naming conventions
DataMerge is the world of programming and it's important to remember that programs work better when you DON'T feed them un-needed spaces. In our case, you should keep the names of the columns as alpha-numeric and underscores (a-z A-Z 0-9 _). Hyphens can wreck havok as well actually, so avoid those. Examples of good headers:
productname
desc_eng
dims_spn
@icon_usage
@product_image
Data
The data itself is everything under the Headers. The rules for the data itself are:
If data in a column extends vertically below another column, it essentially makes another row, so part of normalizing is making sure all the data in the columns is what needs to be there and nothing else.
Data will need to be transformed in many instances, you'll need to create the formulas per column and duplicate them down the column wherever applicable.
Adding image paths: absolute path method
You'll need the header of any column that contains an image path to start with @. As mentioned earlier, this doesn't work by default in Excel since it has to conform to older spreadsheet formula syntax which always started with @. To get around this, add an apostrophe character in front of the field name followed by the @, so:
'@image_path
For each image path, you'll need to CONCAT the path with an image name. Images may be part of a static list that you keep in settings, or they might exist on the fixed spreadsheet, or you may need to create a field to hold them on their own to be CONCAT'd with the image path afterward.
Assuming you have the name of the image file name located somewhere, you can CONCAT them to the image path with this formula:
CONCAT(<cell with image path>, <cell with image file name>)
example:
CONCAT(settings!C4, H4)
How to copy image paths: Finder
You can create a path copying automation in OS X to simplify the path finding operation. Taken from OSX Daily.
Launch Automator and create a new Service.
Use the search function to look for Copy to Clipboard and drag that into the rightside panel of the Service.
Set Service receives selected to files or folders and in to Finder.
Save the Service with a name like Copy Path.
You'll need to add the name of your hard drive in front of the path, so for example change:
/Users/RobK/Desktop/datamerge
to
RobK HD/Users/RobK/Desktop/datamerge
Then use the VS Code add next occurance feature (⌘+D) to select all the slashes and change them to colons, thus conforming to ab older Apple style path:
RobK HD:Users:RobK:Desktop:datamerge:
How to copy image paths: InDesign
You can use InDesign to get the path to the image instead of the Finder.
Place an image from the respective images folder into your template.
In the Links panel, go to the flyout menu and choose
Copy Info > Copy Platform Style Path
That will put the absolute image path in the older Mac style (with colons) on the clipboard, allowing you to paste it into Excel and prepare it as shown above for formulas.
Using relative image paths
This is a feature that I haven't thoroughly tested at the moment, but it seems that you can write image paths relative to the data source instead of using absolute paths. This can really shorten up the pathing in the CSV file so it's worth mentioning.
Basically, if you have a file structure that looks like this:
template.indd
|
|--\_data
| |
| |--data.csv
|
|--\_images
|
|--image1.tif
|--image2.tif
...(etc)
you can write out the image path in relation to the data.csv file by backing out of the data folder then into the _images folder like so:
..:_images:image1.tif
The two dots at the front signal InDesign to back out one folder.
So it's something you can try if you want to minimize your data, but be aware that you might have to go back to the original absolute path method to link images if it's not working.
Updates from the client: redoing some steps
The client will absolutely re-do the spreadsheet at least once in the process. And you'll probably have rigged up the templates and merged or been close to merging when that happens. In that case, you'll need to:
- Recopy the new sheet they provide into your worksheet that you've already duplicated the sheets into, replacing the original.
- Duplicate that original content into fixed and clean it up as detailed in the fixed section above.
- Make sure any new or revised common LOOKUP items are handled in settings.
- Manually fix anything that you fixed originally (see the next section below) and look for any new fixes, all this in the data sheet of course.
Yea, I know, it's not that fast. But it is more reliable. Reliability is key here.
Fix stuff manually
No matter how well we normalize the data. Sometimes there are cases where the client just won't do the right thing as far as formatting the data so you can bring it in without failing. You'll have to manually edit the data in the data sheet no matter what.
Image Pathing: Tell me Adobe, why so difficult?
For reasons only known to Adobe, image pathing in DataMerge is complex as hell and requires absolute paths in the data to the image locations. It unfortunately provides us with a wealth of setbacks:
- Your data fields for images will be long: really, really long in many cases
- You can't keep the images on a file server
- If you move the project folder to someone else's machine, the image paths will have to change for their new location
- Excel doesn't accept the
@character at the start of a cell, which lets DataMerge know that the field content is, in fact, a path to an image
Thanks a lot. Hate you, Adobe.
But you can get over all this stuff as long as you remember to keep a clean settings sheet at the beginning of the normalize stage.
Sanitizing
Sanitizing is the process of taking the normalized data out of Excel and making it import-ready for InDesign. This is simply a matter of converting the encoding in VS Code.
Convert encoding in VS Code
Once you have a CSV out of Excel, you'll need to open it in VS Code and convert the encoding so InDesign can understand the file. The steps are:
Open the CSV data file in VS Code.
At the bottom of the window, the encoding of the file, UTF-8, should appear. Click that word.
The action dialog will open at the top of the window with 2 options showing. Choose Save with Encoding.
That will open another action dialog with several encoding options listed. Choose UTF-16 LE.
Just choosing the encoding will automatically convert the CSV file to proper the proper encoding and the file is now ready to import into InDesign.
It seems like Microsoft revises the CSV export format on a regular basis. Because of this, it's necessary to check the data once this process is complete (in InDesign, using the Data Merge preview) just to make sure everything's kosher.
Stage 2: Lay It Out
Create a layout like you normally do for anything else. You'll want to place all the content on a normal document page, NOT on a master page. Well, you can place it on a master page but it could get confusing during the merge, so just don't do that until you're comforatable with the system.
Use the content from the data for the layout so you can properly judge where to put everything and how large it should be. Using the item with the longest data is helpful here.
Pay attention to how you're placing images in the image frames. If you're not exact centering the images, remember that you'll either have to adjust any other images that have different aspects after you merge the data. This can get to be a pain after the client's 4th revision to the spreadsheet, so it's worthwhile to stage images going into the same textbox so they scale and center the same way.
REFERENCE: product cards - run through/tmpl_flooring cards_normal_v03.indd
Stage 3: Rig It Up
Once you've done the actual layout you want using something from the actual data and taken care of your images, you're ready to rig up the template.
Place the fields from the CSV file that you want to merge into the InDesign layout, you can do this by...
Putting the cursor in the text box where you want the field text placed and double clicking the field in the Data Merge palette.
Selecting existing text and double clicking the field in the Data Merge palette to overwrite the selected text.
You can also drag the field from the DataMerge palette directly into the text box
You'll see a placeholder that looks like <<fieldname>> once you've done that. At this point, you've successfully rigged up a text box to contain data in the merge process.
Note that you can just add any other text around the field placeholder that you want, you don't need the text box to contain only that merge field.
Image fields dragged over the image frames will give them a dotted blue outline with the field name in the frame. That shows you that the image is, in fact, linked.
Preview randomly
There are plenty of opportunities to preview the live data as you're doing the rigging process. Meaning you don’t have to worry about getting it right the first time. Just click on Preview at the lower left of the Data Merge palette to preview the live data and use the field navigation controls to the right of that to see the data change. This is the best way to make sure you're rigging and design are working the way you want.
Changes to the data? You need to update it in InDesign
After you've linked the fields in the layout, the client will probably come back with revisions. When you do revise the data, you'll need to update it in InDesign. The data is, afterall, just a linked resources like any other link. So if you change the data and it doesn't show up, it's probably because the datasource was not updated.
Saving the template file
You'll want your templates to be separate files from the final merged documents. Save the rigged template with a tmpl_ prefix to keep them distinct from the actual layouts that will be sent to print.
Stage 4: Merge Away
At this stage, you're ready to merge the data into a final document.
No matter how well you go through the data in Excel, there are always line breaks, overset text, etc. that will happen in the merged layout. You’ll need to check every page (quickly usually does it) to make sure text fits well.
Saving the final layout file
As we saw in Rig It Up, your templates and final layouts should be two different files. Save the final file without the tmpl_ prefix.
Extras
Go through this post to see why tagging text and image frames in templated layout can raise productivity for Data Merge methodology. Switch positions of text frames and images in a couple seconds without having to use master pages.
Selectively tagging certain text to be a specific character style automatically can be especially handy in data merge.
When you can't (or didn't...shame on you) normally everything fully, GREP find & replace is availabe in InDesign to do last minute normalization fixes.