Data Merge: Multiple Record Madness

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.

mrm01

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)

mrm02a

mrm02b

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”

mrm04

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

mrm05a

mrm05b

6) A progress dialog will appear. After a moment the document will be created either with or without an overset text warning.

mrm06a

mrm06b

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.

mrm07a

mrm07b

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.

mrm08a

mrm08b

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.

mrm09a

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.

mrm09b

mrm09c

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.

mrm10a

mrm10b

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”.

mrm13

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:

mrm11

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?

mrm12

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.

Advertisements

About colmin8r
A prepress operator since 1997 specialising in Adobe InDesign.

18 Responses to Data Merge: Multiple Record Madness

  1. JP says:

    Hi, thanks for a great tutorial! Any more info on the fraction of a millimetre offset issue? I have noticed this, and this is the only page I can find that mentions it. It is so irritating.

    • colmin8r says:

      Unfortunately there is no more information on the offset issue. The fault still exists in InDesign CC 9.1, but am yet to test it in 9.2.1… but I would be amazed if the issue was resolved yet.

      • JP says:

        It isn’t. I’m using 9.2.1, and it is still happening. I did a few tests, and if you try to allow for the offset by altering the margins, it offsets by a smaller amount so it’s still out. It’s a real head scratcher. Have you found any other reference to this issue online?

  2. colmin8r says:

    I think what started this article was the same questions asked repeatedly in the Adobe InDesign Forums, particularly the preview not matching the outcome. There wasn’t one specific item that I can remember directly but the general issues are discussed on the Adobe InDesign Forum. Unfortunately it is just discussed, and not resolved. But there is the opportunity to report it as a bug.

  3. ifnot4george says:

    I can’t believe I’m saying this, but due to a few bugs I’ve found in versions after CS5 coupled with the fact that InDesign doesn’t provide a way for me to merge addresses onto postcards that are laid out 2up on an 8.5″ x 11″ page, front and back, without creating a file for the front and a file for the back and sending it off to the printer who (invariably) matches the wrong back with the front, I’m now trying to set up a merge in MS Word. That’s right. MS Word. I’m so frustrated I could cry.

    • colmin8r says:

      I feel the frustration that users have with the multiple record feature of Data Merge and I try to steer clear of using it. Instead, if I have a scenario like yours, I would prepare 1-up artwork with the appropriate pages (e.g. an InDesign file with two pages – front and back) and then output a PDF that I would then take into imposition software and let that software take care of the details of getting the fronts and backs imposed properly. Any printer who is using EFI’s Command Workstation with the Seequence plug-in to do this work should be able to handle this task.

      I have written in other posts and in InDesign Magazine that Word’s Mail Merge actually has more features than Adobe InDesign’s Data Merge, and one day it would be nice for Adobe to respond to suggestions and criticism and improve on the feature that largely hasn’t changed in the last six revisions of InDesign.

  4. Sebastian says:

    Thank you for this comprehensive post! Really helped me out a lot.

  5. I’m trying to create multiple pages with ticket numbers. When I did a test the data merge worked perfectly, putting the tickets on multiple pages. When I had to change it to only have the tickets on one side of the paper because of our perforation machine, when I create the data merge it multiplies the tickets but instead of going right to another page after filling one, the tickets keep going outside of the margins onto the non-printing area of the page. What am I doing wrong? I’ve tried to start from scratch and it keeps doing it. I have to have these tickets done by Friday.

    • colmin8r says:

      Without looking at the file that was prepared it is hard to say. I won’t be in a position to give you such a quick reply but if you need help urgently, try the adobe forums and in particular, look for two other posters “MW Design” and “Peter Spier” – Peter is their most dedicated contributor, and MW Design is quite familiar with the variable data questions.

      • Thank you!

  6. I just found a post that Peter Spier replied to and it fixed my issue. Thank you so much!!!!

  7. John Lambert says:

    Hi there colmin8r,
    Many thanks for this excellent tutorial! Despite this being by far the most well structured page on this topic, I’ve hit a brick wall with my doc 😦
    Following your described steps, I get to point 4: “Populate the variable placeholders using the fields from the Data Merge Palette [..]”. As you don’t mention that the variable placeholders need to be placed on a regular page, I understand that they should be at the newly created master page – correct? However, I then get an error message saying that there are now placeholders and that they need to be placed on a regular page.
    I don’t know what I’m doing wrong – or if it’s even possible to achieve what I want… Basically, is there a way to create static items (incl. footers and page numbering) BEFORE making the data merge, or is the only way to add them afterwards?
    Please let me know if I should clarify anything or send over my files to you. Any assistance would be greatly appreciated! Many thanks!

    • colmin8r says:

      I think your question is answered in part two of the tutorial:

      “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)”

      • John Lambert says:

        This doesn’t seem to help 😦 After adding the variable items and placeholders on the “B-master”, I try to do the data merge, but get a prompt telling me there are no placeholders and I can therefore not merge. Then, I tried to apply the B-master to my regular page and then do the merge. That works! However, it doesn’t merge correctly, and replicates the same values over an over. The layout of the pages look fine though. Any idea what I’m doing wrong? I have a feeling the above steps are not correct, as you didn’t mention them in the tutorial? I’m really stuck here 😦

  8. Hi, not sure if you can help or if there is anyway of doing what I need but thought I would give it a try. I work on multiple catalogues for a company and am thinking of using the Data Merge to populate footers, logo’s and company name changes throughout the different publications.
    The problem comes in when I am going to have nearly 8000 PDFs to create, Data Merge allows you to make multiple pages from one document through the Data Merge window but when your using a external program to export 1000’s of PDFs from the indd files they will all come out with the link info on the document instead of the actual information needed. Thanks for your help in advance.

    • colmin8r says:

      Hello there.
      I’m afraid I don’t understand your situation. Are you able to PM me with more information and perhaps some screengrabs to my email address that can be found on the requests page of the colecandoo blog?

  9. Melody Laker says:

    Hi there, can you help me ? I am trying to get my records to lay on top of two different colour versions of the same place card. But as soon as i put more than one in, it just repeats the record across both coloured versions. How do i fix this?

    • colmin8r says:

      I would like to try and help you but I need some more information. Rather than try to diagnose the issue over an open forum, may I please suggest that you contact me directly via the contacts page: https://colecandoo.com/requests-2/ and I will try to assist you.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: