Merge Merge Merge, Merge is the word…

I do love a good pun in the title, and anyone familiar with Family Guy episode “I dream of Jesus” will know how a classic song such as “Surfin’ Bird” by The Trashmen can become annoying in a short space of time.

Nevertheless, this article is a quick overview on the built-in Data Merge function shipped with Adobe InDesign. It is not a tutorial as there are plenty out there on the internet, but more a guide before embarking on using Data Merge for a project.

1 Data Merge is not the only VDP tool available using InDesign.

Yes, it is the only FREE tool, but InDesign also allows XML support. However, the interface for XML within InDesign is difficult to grasp and quite frustrating. There is also little information about using XML with InDesign in the public domain, and the only two reliable sources I’ve found so far are:

• A Designer’s Guide to Adobe InDesign and XML by James Maivald; and
• InDesign CS5 Automation Using XML & Javascript by Grant Gamble

The other factor i’ve found so far with XML publishing is that very few finished projects supplied to me to format are ever supplied as XML files, but rather Word or Excel documents. While Excel can export to XML via PC, the quality of the exported XML file is normally bad.

Apart from XML and Data Merge, there are many InDesign third party plug-ins which behave like Data Merge but have more enhanced features such as programming statements to treat merging data more like a database query, and the ability to construct catalogues. For a list of third party plugins, click here.

I won’t go into the pros and cons of the different methods in this post but will cover them in another article. Today’s post will focus on InDesign’s built-in Data Merge only.

2 Data Merge doesn’t understand the data.

There are many questions posed either on forums or asked of me at work about what Data Merge (DM) can and can’t do, but typically the questions revolve around DM taking data and doing something with it apart from formatting, such as adding text after specific entries, totalling values, swapping backgrounds, etc.

Put simply, DM doesn’t understand what the data is. That is, it doesn’t know if the data is an address, a number, a person’s name, a dollar value, or their favourite colour. The only thing that DM does understand is whether the data is text or a picture, and that is established in the first line of the database being used, when any fields to contain pictures are prefixed by the “@” symbol.

All DM does is populate a layout with the data from a database. It can’t parse the data or make sense of it, DM simply puts the results where the placeholders are.

For example, a database has a field where the answers will either be Yes or No. The brief from the client is that if the field has a No in it, that the word No now appears in Red and 20% bigger; additional text appear in a text box below, and that the background of the page change colour.

The only thing that can be done directly from the brief using InDesign without editing the database  (without post processing – this is covered later in this article) is the formatting of the word No using GREP styles in the Paragraph Styles feature. The other requests would need to appear as additional fields in the database – that is, every “No” entry would need to have the additional text in another field in the database, and the background colour would have to be treated as a picture.

Similarly, DM can’t tell the difference between a person and a business. I’ve seen many direct mail projects where the authors have addressed mail to individuals and businesses and then had a “Dear … ” line but not considered that this salutation needs to be different to the mailing address details. What tends to happen is that an item addressed to me personally will read “Dear Colin”, however if it is addressed to my business, will end up as “Dear Colecandoo”, which doesn’t make sense, and clearly tells me that an individual did not prepare this communication especially for me, but was spat out of a computer generated mailing, losing the impact of direct mail.

It also doesn’t understand how numbers need to be treated. For example, if the data is a dollar value and must appear $xxx,xxx.xx but the database has been supplied as xxxxx.xxxxxx then InDesign doesn’t know to apply specific formatting to the number. This has to be done in the database itself.

So ultimately, clean and sensible data with all relevant information is the key.

3 Data Merge doesn’t adjust the casing of text.

If a database is supplied in all caps, there is no ability in InDesign to force the text to lowercase or titlecase. However the reverse is true (a database supplied in lowercase can be forced to allcaps using character or paragraph styles) but only of lower to upper, this will not work for title case.

Again, using the “Dear …” principle, if the database was supplied in all caps, the result in InDesign will be “Dear COLIN” which again, makes it obvious that this communication is a “stencil letter”.

This effectively means having to format the text to the appropriate case in the database itself. If this is Microsoft Excel, this can be done with limited success, as Excel does not have a change case function like InDesign, BUT does allow casing to take place as part of a formula. I won’t go into this at great depth but will recommend this example as to how to change the casing. Using the linked example, the name “JOHN STEELE” becomes “john steele” if using the LOWER function, but becomes “John Steele” if using the PROPER function. However, a name which should appear “Jack van McAvaney” will appear as Jack Van Mcavaney” using the PROPER function.

Again, clean and sensible data with all relevant information is STILL key.

4 Data Merge won’t sort the records.

As stated before, all DM does is populate a layout with the data from a database. DM can’t sort the data by zip-code, surname, how much they spent, their favourite colour, etc. The finished order must be correct in the database itself.

I can’t stress how strongly that the database itself must be shiny, polished and perfect before it can be considered for DM.

5 Soft Returns from Excel won’t get picked up during the export to CSV/TXT.

Normally associated with direct mail campaigns where, in the Excel file used to make the CSV file, the data entry operator has copied and pasted a soft return to separate line one and line two of an address.

When this excel file is then saved into a CSV or TXT file, what happens is that soft return now becomes a hard return, effectively shifting the rest of the data involved with that record to a brand new record which will make no sense.

Instead, the database should have had a field for line one and another field for line two. This may mean many empty entries for line one, but that can be overcome by the “Remove Blank Lines for Empty Fields” within the content placement options of the DM.

Likewise, some special characters in an Excel database may not export correctly. Without going into detail, the best suggestion is to use a PLAIN TEXT editor such as Textwrangler, Notepad, Textedit (i.e. NOT Word) to check exported databases for characters that shouldn’t be there, or records being out of place.

6 Data Merge won’t flow the results into the same textbox.

Many Adobe forum posts have asked the question “i’ve set up my text box but when i hit multiple records per page, nothing happens… why?”.

It is important to understand that DM used for multiple records per page repeats whatever elements are on the initial page (leaves master page items alone) but it doesn’t thread the text together, or put the text into one text frame like a directory merge using Microsoft Word.

This makes DM ideal for finished results such as pre-imposed business cards or invites, yearbook photo pages or “contact sheets” but makes DM a poor choice for catalogue work where the size of entries will change from one record to another.

For this kind of work I would either:

• use an XML based solution;
• use a third party plug-in; or
• (unbelievably) do the merge in Word using its directory merge, and then place this file as text and format like any normal artwork.

7 Consider the final process.

In my line of work, the DM quantities i’m involved with range from hundreds to tens of thousands, so data becomes a commodity and a hindrance. If preparing DM for a commercial printer, i’d recommend making the artwork in two layers:

• Base artwork (won’t change from letter to letter); and
• Variable artwork (which contains the DM elements and is different for each record)

I say this because finished VDP can be done in one of three ways:

1. Variable records printed onto offset printed shells;
2. Variable records made as one PDF, the base made as another PDF, and then merged on a RIP; or
3. Variable records combined with the base in InDesign to print as a composite file.

The first method would be used for huge DM campaigns where time and cost are factors. The second would be where time and data size are factors, and the third would be for data-light files.

In the first two cases, the variable components are split from the base components, and this is best done using layers.

8 Don’t double-handle.

I’ve had many DM projects where the client wants some records to have particular features not in the original design which can’t be done in the actual merge, but could be done if the DM was merged to a new file and then tweaked. Effectively post-processing artwork generated by DM.

I don’t believe in doing this as it turns VDP projects into design projects, and also makes it difficult to incorporate wholesale changes which can be made.

My advice is the prepare a DM where the InDesign file can be exported directly to finished art without further intervention or post processing.

9 The “Record Limit per Document” doesn’t work when exporting to PDF.

There is a DM feature where a file can be merged to a new InDesign file which will be a set amount of records long. For example, it is possible to take a single sided business card with five records and merge it to five individual InDesign files.

However, this feature does NOT WORK when merging to PDF. Instead, if using the “Export to PDF” function from DM, even if the checkbox in the “Record Limit per Document” checkbox is turned on in the options panel of the create merged document, the same file above would now become one PDF five pages long.

Doesn’t sound like a big issue, until dealing with DM projects that can have 15,000 records to be broken up into 500 record lots… when this feature is ESSENTIAL!

I have reported this to the bug/feature request to Adobe but I must be the only one using it. If you think this should be fixed, please tell them here!

10 Make the placeholders big enough for the field names.

I’ve noticed a glitch in earlier versions of InDesign where if my textbox is not big enough to handle the actual placeholder (e.g. <<NAME>>) but looks correct when the DM preview is on, strange results can happen when merging directly to PDF, namely the merge stops and repeats on the record which had the entry which was too big to fit.

The easiest workaround to this is to make the textboxes big enough to handle the actual variable data placeholders. This has the added advantage of being able to see the complete design while the placeholders are being viewed as opposed to working in preview mode.

While writing this, I did notice the issue was not present in CS5 or CS5.5, but still a good rule to follow nonetheless.

so that’s it!