Data Merge: Multiple Record Madness
December 28, 2013 18 Comments
There have been plenty of posts on the Adobe InDesign Forum lately concerning issues that users are having with data merge, particularly Multiple Record Data Merge (MRDM) projects. These projects could be “stepping up” artwork onto a larger sheet for trimming (e.g. imposing many business cards onto one sheet) or preparing catalogues for example.
Before rushing into an FAQ of issues concerning MRDM, here is a step-by-step of preparing a MRDM to ensure the minimum of fuss:
1) In a new document, go to the Master page and create the static items that are to appear on every page.
2) Create a new master page BASED ON the master page created in step 2 and in this page, add the items that will be variable, but prepare the set up as if it were for one record only.
(for those familiar with Data Merge, the variable can indeed be on a regular page. The difference is that MRDMs created with variable items on a regular page will not allow the source data to be linked)
3) From the Data Merge palette, choose “Select Data Source” and select the text file that will be used for the merge.
4) Populate the variable placeholders using the fields from the Data Merge Palette, being sure to that the placeholders of the text are visible in their own frames. Once satisfied with its appearance, select “Create Merged Document”
5) A new dialog box appears. In the tab “Records”, make sure that the Records per Document Page dropdown says Multiple Records. Then from the tab “Multiple Record Layout”, set the appropriate margins of the artwork. If using the preview, note that the position of the first record may have moved – this is normal. Change the margins to the margins that were in the static layer, and layout the records as appropriate. Click OK once ready to proceed
6) A progress dialog will appear. After a moment the document will be created either with or without an overset text warning.
There are several things that should be noted:
- The margins in the InDesign file are irrelevant for where the variable data starts – this is determined by the Data Merge Panel;
- That during the construction of the file, it is only possible to view the data 1-up. To preview multiple records, this can only be done from the Create Merged Document from the Data Merge panel.
Common complaints when preparing a Multiple Record Data Merges:
Each page has the same record repeated, so page 1 has record 1 many times, page 2 has record 2 many times, etc.
Likely that many variable placeholders were created and populated thereby filling the page, instead of one series of placeholders for one record only. MRDM works by creating one record and then using the MRDM panel to allow the next records to be inserted based upon details here (e.g. distance between the records, direction of flow of the records)
MRDM records aren’t merging where I want them to merge (e.g. off by half a millimetre or more, or off by miles)
Data merge uses the margins within the MRDM dialog box, NOT the margins in the active document. There is an additional glitch that offsets the starting position by fractions of a millimetre. This may seem insignificant but there is no reason that the software should not place the text to precise measurements.
Data merge has a further glitch if the document was initially created at one size but was then resized (regardless of orientation or size). When merging, the starting offset appears to be where the original size page margins would have been. The following example shows the same file but just made landscape.
This glitch persists despite further resizing, adjusting of margins, or saving as IDML and reopening. The only solution is to cut and paste from the defective document into a brand new document.
When MRDM merges to PDF, the first record does not appear, and instead of the records displaying one after the other, only the first record placeholders appear and is repeated through the document.
This only occurs when exporting directly to PDF. In this instance, the variable data portion has to be on a regular page, and any items that are to remain static need to be on the master page.
The MRDM preview is correct, but the resulting InDesign file has only one record per page. Once the view is changed to see the entire pasteboard, it is clear that the other records are on the pasteboard.
Likely because the variable and static data is on the same master page. Instead, the variable data portion has to be on a regular page, and any items that are to remain static need to be on the master page. If linking to the text is essential, the workaround is to put the background on a master, and then make a second master that is BASED ON that background and put the variable data on that layer. Refer to part 2 of the tutorial at the start of this post for more information.
Other commonly asked merging questions:
Upon selecting data to import, a dialog box says “The data source has one or more empty field names. Please fix the file or select another file”.
The header row has fields that don’t have names. Can happen if the field names have no headers – the header row has to have names in it. Can also happen if an excel file is saved as a txt/csv file but it saves more columns than required. These excess columns need to be deleted in excel before attempting to import the data again.
The text has picked up the formatting of the line underneath.
This has to do with the “Remove Blank Lines For Empty Fields” options in the Content Placement Options portion of the Data Merge palette. This issue has been discussed elsewhere on Colecandoo.
The records are all in their own frames, instead of one long flowing frame like this:
Data Merge works in this fashion, it doesn’t have a “next record” feature that Microsoft Word has in its “Mail Merge” feature. There is a workaround in the form of a script created by Loic Aigon that will do this; or by merging to a new file and then using a script such as Ajar production’s merge textframes that will thread the text so it run into one text frame, but if the data is likely to change then this procedure will have to run all over again. XML workflow is better for this kind of project.
How do I prepare a multiple record merge where records specific to one field are on the same sheet?
This can’t be done from InDesign “Off of the shelf”. Data Merge, whether single or multiple records, can only handle one to one database relationships. It can’t merge items such as itemised invoicing for a client database where the database contains varying record lengths for each client. This is an example of “one to many” database relationship. InDesign can only handle “one to one” database relationships.
How to I preserve carriage returns from my Excel database into Data Merge?
Can’t be done. Data Merge only works with txt or csv files, so ultimately the Excel file has to be saved as one of these formats. A return (whether soft or hard return) in the txt/csv file indicates the end of the record and the start of the next, whether intentional or not. One trick is to substitute the returns with a character that is unlikely to use in normal type (e.g. the “pipe” symbol = | ) and once merged into the file, use find/change to replace the | with a carriage return.
The drawback is that this method cannot be used if merging directly to a PDF as the intermediate step of removing the pipe symbols cannot be done. Another drawback is if the data file for the merge is replaced, the merge will have to be done again. XML is a better choice for this task as carriage returns, non-breaking spaces and other special characters can be preserved.
How do I remove unintentional line breaks (whether soft or hard returns) from my Excel database before using Data Merge?
Dozens of answers exist on the many Microsoft Excel forums that may/may not work within the Windows OS, but on the Mac OS there is no reliable answer that works within Excel itself. Anyone who can answer to this question should feel free to post it in the comments. Best answer so far was again from Loic Aigon’s Blog.
My data is not importing properly (e.g. Records are starting where they are not supposed to, characters are corrupting, etc)
Could be a variety of reasons depending on how the data was prepared, how the fields are separated (whether comma or tab). The data should be checked by finding the corrupted record in InDesign, then comparing the data file in a text editor such as UltraEdit or TextWrangler to see what is happening.