Adobe Certified Expert - InDesign CS2
subscribe via iTunes
RSS 2.0

Episode 43: Data Merge (VIDEO)

Who says data and design are mutually exclusive? Not InDesign, that’s for sure. Under the right circumstances, data-driven layout is the way to go. Unlike XML, which makes designers run from their computers, the Data Merge feature in InDesign is an easy way to accomplish fast, flexible data-driven layout creation and updates. Using a subscriber’s files as an example, this episode takes a video look a topic that was tackled way back in the audio-only days of Episode #7.

Watch the episode here (15:00 | 28.6MB), or you can subscribe via iTunes.


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

56 Responses to “Episode 43: Data Merge (VIDEO)”

  1. Since watching this tutorial, I now use this method to create my clients monthly product offer sheets. They simply email me an excel spreadsheet with Product code/description/link to the relevant images/price/new price/sizes in stock/any other info needed… which is all exported direct from their SAGE accounting system in one easy report. The relevant images (i have stored locally in one folder as part numbers, which match the image links from the clients sage package).

    Then i use the same process on the final flyer to add the recipients addresses, again exported into excel/csv from SAGE by the client.

    A basic 100 product A3ds flyer can be produced ‘in minutes’ giving me more time to be creative and punchy with the overall layout. Plus, Any last minute price changes are no longer an issue!

    THANK YOU!

  2. Thanks for sharing this great information, it really helps a lot. Need your help, I am creating a catalog. I got 300 items to put in with a corresponding page. Using Excel, I have the list of items and a column for page_number to indicate where the item should be placed. I was wondering how can I make inDesign automate to create a text label for the item specific only for a page. I hope you can help me. Thanks very much.

    data example:
    Page Item# Price
    2 10001 $20.99
    2 10007 $10.99
    2 10006 $32.99
    2 10004 $14.99
    2 10046 $32.99
    3 10024 $14.99
    3 10016 $32.99
    3 10036 $14.99
    4 10306 $32.99
    4 10403 $32.99
    4 10502 $14.99
    4 10708 $14.99

  3. Dan — Thanks for sharing your experience. The whole point of the podcast is to reveal those features that make the work itself easier so that we can all spend time making it better. It’s always great to hear these “case histories” from the audience, and very rewarding for me to know that the ideas are being incorporated into people’s everyday projects.

  4. Minji — You can’t designate on what page an item will be placed via Data Merge (or even with XML, to my knowledge). The records will flow into the layout in the order in which they’re entered in the data source. These features rely on consistency.

    If you had the exact same number of items that had to go on each page, Data Merge might get you there. You’d just set up your template to fit, say, 6 items per page. From what you show above, there are 5 items destined for page 2, 3 for page 3, and 4 for page 4.

    The only way data merge could be used for this would be to create dummy records in the Excel file, then delete those imported records in the merged InDesign document. Unfortunately, that’ll leave you with empty spots in the layout.

    It’s also difficult for me to determine what, exactly, these “items” are made up of based on what you’ve described above, so a suggestion for solving your particular problem isn’t easy.

  5. Hi Michael, thanks for your message. Yes the count of items are not the same on every page, a page might have only 12 items but on the next will have 20. The catalog is sorted by brand name so it depends how many products can fit on a page. We have like shirts, jackets, caps, shoes, accessories, etc. I think I have to do it manually since the number of items on a page is not the same always. Sorry for me :) If you have other solution just let us know. Thanks very much!

  6. hi michael, i am trying to save my file as a tab delimited but i do not get that option by save as. i am using excel 2007 (pc)
    please help although it is not a design concern (basically we become some sort of programmers in indesign…)
    thanks very much fro the podcasts

  7. Amaltra — As a die-hard Mac guy (and one who is still using Office 2004), I can’t exactly pinpoint why you’re not seeing this option, but when I do a “Save As…” the pull-down menu I see looks like this:

    If you have no luck finding it that way, keep this in mind: when you copy and paste from Excel into a text file, you’ve automatically converted it to tab-delimited text. The columns are swapped out for tabs that way. So if you run out of other options, cut and paste what’s in your excel file into a plain text file, then save it as a text file. There’s really nothing special about the “tab-delimited” format itself. It’s just a plain text file.

  8. Linda Gill-Jones says:

    Michael,

    I found your tutorial very helpful. I have been working learning the data merge in CS3 and I am having a problem with the photos linking. I have followed your tutorial but everytime I click on the preview button I get a message that says “the data source references one or more missing images. The missing images cannot be previewed or merged…” I have set up my data source file as you showed in your tutorial. I have the files named all the same, including checking the upper and lower cases of letters. I must tell you I am setting up my spread sheet on a PC, then saving it as a tab delimited file, then I work with Indesign on a Mac. Could this be the issue? This is how I have listed the info for pictures in the spreadsheet.

    Desktop:YTD testing:postcard mailer:graphics:white water rafting male.jpg

    Do you have any suggestions as what the problem might be?

  9. Linda –

    I don’t think that starting your spreadsheet off on the PC is the culprit here, but if the images you’re referencing are on that PC, you’re going to have an issue with the file paths, because PCs require backslashes between directory levels, not colons. But if these images are all on your Mac, and you definitely have the “at” symbol as the first character in the header for the column that lists your images, I suggest trying to do a truly “absolute” path for the files, rather than just starting at “Desktop.” Since each user has his or her own “Desktop” folder, I find it’s safer to make my file paths bullet-proof, as follows:

    Macintosh HD:Users:myusername:Desktop: and so on…

    Give that a shot and let me know if it works.

  10. Linda Gill-Jones says:

    Michael,

    That was it. Once I changed to the “absolute” path the pictures came in fine. You have been a great help. Thank you so much.

  11. Nora Collins says:

    Hi Michael
    we have been trying to produce name cards for a customer event. Have followed all the steps in your Data Merge video, which was fantastic. BUT, while we can preview the 3 pages with names in correct order etc, so preview is perfect; when InDesign actually creates the document it only has one name repeated on everycard.
    Please help!!! We can not figure out why this happens.

    Thanks
    Nora

  12. Nora Collins says:

    A whole day of frustration!!! The Answer was Do NOT use an Avery InDesign Template!

  13. Nora –

    Sorry for the delay. I’ve been traveling.
    Just out of curiosity, do those templates include other elements on the page?
    I’ve had experiences with Data Merge where the preview looks great but another object on the page causes the merged file to appear entirely differently. You may still be able to use that template, but with some modifications.

  14. ahamed meeran says:

    hi

    data merge is very great tutorials….same time i need one more thing… as per your tutorial it will come single text frame..in every data.

    example:

    COMPANY NAME:
    PO BOX, CITY, TEL, FAX
    EMAIL
    WEBSITE

    WE HAVE 500 CLIENTS… SO GIVE ME EASY WAY FOR all data contain one text frame.

    lot of thanks..
    -ahamed

  15. Ahamed –

    The solution for what you need is not Data Merge, unfortunately. To get repetitive data to flow continuously through one frame (or a series of threaded frames), you’ll want to work with XML instead. Using tagged XML data, and by putting formatted placeholder tags in an InDesign file, you can flow those 500 listings one after another, as well as keep them linked to the source data file.

    Michael

  16. Hi!

    I was making some contact-sheets. And I was playing around with the @foto path. I discoverd that if you place your .txt or .csv file in a project folder and then create an image folder inside the project folder – Then you don’t have to type the long path for your image files.

    Just type /photos/yourphoto.jpg (I hope this makes sence?)

    I have made a screenshot of the project folder and the notepad – http://www.madsbo.dk/sider/datamerge.html – perhaps this is easier to understand?

    I found that it is much easier to type in the images path, and if my project folder is moved to another computer, the path to my images won’t get “broken”

    I guess the slash / would be replaced with a colon : on a Mac?

    - Mads

  17. Excellent tutorial!

    What I’m trying to do is Group the output. For example, like a Company Telephone Directory where people are listed by Department.

    Department Name
    Name1 – Email
    Name2 – Email
    etc

    Next Department Name
    Name1 – Email
    Name2 – Email
    etc

    Can this be done with ID? thanks

    zu

  18. The thing to remember with Data Merge is that new objects (text frames, tables, image frames, etc.) are created for each “record” in your data source. So, if you want to fill a page with text like you show above, you certainly can, but that text will exist in individual text frames for each item. You might want them to run continuously in a single frame, of flow through multiple frames in a threaded story. To accomplish that, you’ll need to use an XML approach, rather than a Data Merge approach.

  19. Rheal Poirier says:

    I know it’s kinda late to respond to this thread but I’m curious to know if it’s possible to create a template that is different on each side of a spread (eg images away from spine and text towards spine)

    I know it’s been said before but your tutorials are absolutely amazing. I love the in-depth knowledge. Keep up the good work.

  20. If you have the educational version of inDesign you can not have multiple records per page. That function is greyed out.

  21. I doubt that this is the case, Karen. More often than not, the graying out of the Multiple Records option is due to some error in the setup of the file that acts as the merge source. If you troubleshoot your layout for potential errors and eliminate them, I think you’ll find the Multiple Records option will be available to you. There’d be very little use to Data Merge without that feature.

  22. Hi Michael!

    Thank you for a great tutorial.
    I have the same question as Rheal Poirier, is it possible to make Idesign render the pages as spreads? I only get single pages?

    Thank you!

    Charli

  23. Linda Gill-Jones says:

    Michael,

    I have a question that you may have already answered so I apolgize if this is repeat info. I am using data merge with a .csv file and I have the follwing layout

    Name
    Address 1
    Address 2
    city, state zip

    In some cases I do not have a Address 2. Is there a way to automatically move up the city, state zip line if there is not address 2, rather than having a blank space in the address field?

    Thank you for your help.

    Linda

  24. Michael,

    This tutorial was fantastic! Thank you.

    I’ve designed a 20 page booklet in ID to be mailed to 1,000 addresses. I’d like to merge address information, but in using the tips in your tutorial, a massive document is created. On top of that issue, I’d like to use the “Print Booklet” function . . . do you have any advice? I’ve used Print Shop Mail in the past, but I’d like to migrate towards printing within InDesign.

    Happy Halloween and congrats on the newborn!

    Best,
    Christian

    P.S. If you reply in this forum, can you forward a copy to my e-mail? I’ll check back here as often as possible.

  25. Christian –

    In all honesty, my best recommendation to you is to find a variable-data printing service that can handle this job for you. It would probably cost you a little bit of money, but it would save you a ton of time and eliminate the need to produce a 20,000 page document. That, in my opinion, is too large for Data Merge’s intended use.

  26. Michael,

    First, let me say thanks for all that you do with your podcast… second, I miss them!

    Now to the issue: I’ve used this podcast to create a yearbook like playbook for one of the departments in our company. I’ve been asked by another group to make a similar book, however, they would like a few things that I can’t seem to do. They would like for the Name, title, dept, year, photo, and a small paragraph or two from each individual to flow throughout the book, not have a specific amount on each page, and I can’t seem to get it to work. Depending on size of ppg., there may be one person or three people… Any suggestions? Help… Thanks in advance! <

  27. Karen –

    If you need your data-driven content to flow through the document, rather than be contained in multiple stand-alone frames, you’re going to need to use XML instead of Data Merge. I highly recommend Jim Maivald & Cathy Palmer’s A Designer’s Guide to Adobe InDesign and XML as a reference for this kind of workflow.

    Michael

  28. Thanks for your speedy reply… I will go purchase the book today.

    No chance on a podcast on this anytime is there? :o)

  29. An XML podcast? Ouch! I don’t think I’m taking that on for quite some time. There are so many tangents to go off on to cover the topic properly that it’s really a “sit with a book and some trial-and-error” process unique to each project.

  30. Hi Michael

    I am trying to data merge some business cards which I have planned 12 up on a page. When I import the data it just makes a page full of the same names on each business card as opposed to what I want, which is a different name on each card.

    Is t possible to do this, or will I need to do it manually (hep!!!!)

    Thanks
    Dani

  31. Hi Michael
    your podcast on data merge with InDesign was extremely helpful. However, I’m still stuck with 1 issue. I’m trying to print 8-up postcards which have variable data on BOTH sides. after I print the fronts, I cannot get the backs to match up. It seems the data for the backs needs to flow in a different manner (down the columns, but from right to left, instead of left to right) in order to back up correctly. Any ideas????

    Thanks so much!
    Kyle DeMartyn

  32. Kyle –

    Data Merge can’t handle a front/back setup like this. Every record has to either exist on it’s own page or fit with many other records on a page. You can’t take part of a record and merge if into one page and the merge the rest into another page. The only thing that might help you is an XML-based approach, which is far too involved and specific to describe here.

  33. @Dani –

    From what you describe, there’s no reason Data Merge can’t handle your business card project. It sounds like an error in your setup might be to blame. Do you have the page set up with 12 cards already on it and the placeholder for the name in each one? Remember…Data Merge duplicates everything on the page, so for your 12-up business cards, you only need one business card layout on the page. It needs to be positioned so that when you do the merge, there’s enough room remaining on the page for 11 duplicates (arranged in rows and columns). You’ll be able to preview your results using the Preview Multiple Record Layout option before you do the merge. That should help you see if it’s set up right.

    If I’ve mis-diagnosed the problem, let me know.

  34. Hi Michael,
    Made the Excellent tutorial!

    I have a question: I am using data merge with a .csv file and I have the follwing layout

    Company Name
    Address 1
    Address 2
    city, state zip

    Have followed all the steps in your Data Merge video, which was fantastic. BUT, while we can “Multiple page preview” shows the single address on each page. when InDesign actually creates the document it only address on each page up to the no. of addresses we have on the .csv file.

    Please help!!! We can not figure out why this happens.

    Thank you.

    Anil

  35. Hi Michael,

    After reading your tutorial, I prepared 1000 students certificates in minutes. Thanks for such a wonderful tutorial.

    I am preparing Yearly Book for graduates. I want to show their Photo and Name. When I put !@Photo it automatically become an Email not a header.

    What should I do?

    Regards

  36. Mahmood –

    Glad to have saved you some time in your previous project The problem you’re having with !@Photo being treated as an e-mail is because you need to use an apostrophe, not an exclamation point, to start off the header. It should be '@Photo.

  37. Hi Michael,

    I see in the thread here that in order to get multiple records into one data frame you say that XML is the way to go as opposed to data merge.

    I’ve got a MySQL database I’m exporting data from (film times, titles & dates to lay out a schedule for a film festival). I can pull the data I need out of the database as an XML file but then I have no clue what to do with that in InDesign. I somehow need to be able to sort the exported info by date with each film that shows on that date appearing in order of screening time with the film title. Do you know how or know where I might find some good information on how to achieve this. Looking for something easy to understand, since my past experience with XML in InDesign made me, as you said, run from my computer.

    Thanks!

    Liz

  38. Liz –

    As I’ve suggested to others earlier in these comments, I think the definitive InDesign and XML reference is A Designer’s Guide to Adobe InDesign and XML by Jim Maivald and Cathy Palmer. Every XML-based project is unique, so there’s no cookie-cutter answer or step-by-step I can offer here. That book will arm you with all the knowledge you need to establish XML templates and a workflow that suits your specific needs.

  39. I have a problem with multiple records.
    I created a document with 12 labels. In the preview everything looks good, every label has a different record, but when I merge the document, then all the records get mixed up. Some records are duplicated, some records are missing. Do you knwo what can go wrong and how to solve this?

  40. I have a project challenge and was wondering if InDesign CS4 will be able to help me. It is a data merge issue. I have a client with a catalogue – who creates a separate price list publication. The information is sorted by page number then item description, code, colour, price – since this is an annual change – instead of manually editing – ideally if I could have my client update a spreadsheet with the price/page number/description updates so I can then merge to InDesign. Is this possible?

  41. Janet Lowry says:

    Hi, great tutorial, though I’ve had to tear my hair out over this thing. First the filenames were very difficult. Then I’m having the same problem Karen has. I can get only the single image, and the multiple record is disabled. I also have the educational version. I can’t understand why “Records to Merge” shows 4 records in the range, but when I click either All Records or Range, the “Multiple Record Layout is disabled because Single Record is selected per document page”. But that’s not true.
    Since I can do the merge with any one of the single records, I think there may well be something to what Karen said. How would I find this out officially?

  42. @ Kyle:

    I am facing the same issue. Since InDesign wont do it (at least any way that I can find), why not re-order the actual data? In my case, I am printing 4-up response cards with dynamic data on both sides, so while my “front” side is ordered 1-2-3-4 going from right to left on two rows, my other side simply has to have record 1 become 2, 2->1, 3->4, 4->3. The formula here ends up being add one to the unique ID of odd records, and subtract one from the same value of even numbered records, and reorder.

    Just remember to backup an original copy of the data in case of mistakes, the front side data must have an autonumbered sequential ID field (so you can alter it in the “back” side data file). If you get creative with copy and pasting Excel formulas, this becomes a much less daunting task than it seems and improves the functionality of InDesign exponentially (in my opinion).

    Just an idea, and I hope it works for you!

  43. A significant issue with InDesign CS4′s Data Merge feature has been fixed in the 6.0.4 update. See the release notes for item #2335625.

    ——————————————————

    An important update to the InCopy and InDesign products has been released today!
    To install the update, choose Help > Updates from any Adobe CS4 application, or navigate to the Adobe Updater and launch it:
    Mac: /Applications/Utilities/Adobe Utilities/Updater6/Adobe Updater.app
    Win: C:\Program Files\Common Files\Adobe\Updater6\Adobe_Updater.exe

    You can also download the updates from Adobe.com at the following locations:
    InCopy Mac: http://www.adobe.com/support/downloads/product.jsp?product=30&platform=Macintosh
    InCopy Win: http://www.adobe.com/support/downloads/product.jsp?product=30&platform=WIndows
    InDesign Mac: http://www.adobe.com/support/downloads/product.jsp?product=31&platform=Macintosh
    InDesign Win: http://www.adobe.com/support/downloads/product.jsp?product=31&platform=WIndows

    Release notes are here: http://www.adobe.com/go/id6_readme_en

  44. This is a really good tutorial. Thanks!

    I have a question regarding collecting data in an excel doc. I have a bunch of images (nested in a folder structure) that I want to get into the excel doc so I can do the data merge. Is there an easy way to capture all the image file paths all at once to paste into excel? I’m using OSX and guess this could be done in terminal but not sure just how. Any thoughts?

  45. Great tutorial! I have one question. (Don’t we always!)

    Is there anyway to use data merging on a double sided document? I’m creating 300 business cards and need to have “variables” on each side of the card. I’m using the Multiple record layout for the fronts and it’s working perfectly, but, I need the backs to match up on a separate page in the same document.

    Thanks for all your help!

  46. Double-sided is not a Data Merge option. You’d have to do one merge in one document for the front, and one merge in another document for the back, which opens you up to errors when you try to marry them up. You may need to move to an XML-based workflow (something I’m a little weak on), or try to find a printer who can do variable-data printing for you, to whom you’d only need to provide the template(s) and the data set.

  47. @bldg13 — First, my apologies for such a slow reply to your question.

    What I typically do in Excel is create two additional columns, then paste the common path for my image files into the first cell at the top of one of those columns. Then I click and drag on the lower-right corner of that cell and drag down the column to populate all cells in that column with the file path.

    In the other column, I take advantage of Excel’s concatenation feature to combine the file path with the unique file names. So, if for example, your file path is in column C and your file names are in column D (assuming that each starts in row 2 because you need a header row for the data merge), what you’d put in that blank column to combine the two is: =CONCATENATE(C2,D2)

    You should see the result once you hit enter. The cell should now contain the contents of cell C2, immediately followed by the contents of cell D2. If you click and hold on the lower right corner of that cell, then drag down the column, it will combine C3 and D3, C4 and D4, C5 and D5, and so on all the way down. That’s the column you should put the @photos header on in Excel so InDesign recognizes it as image references.

    Don’t worry about the other two columns being in the Excel file you eventually use in InDesign. They’ll be available in InDesign’s Data Merge panel, but you don’t need to use them in your document.

  48. Hi Michael.

    Thanks for a very instructive video.

    I have one specific problem regarding Datamerge – in this case in InDesign CS3.

    I’m setting up a manual with different technical symbols, which i insert in an imageframe. The other information from the spreadsheet is some text (symbol no., description etc.) which i put in textframes. So it’s put up in a row like this (symbol ID, symbol/image, description, keywords. The data is then repeated downwards when i do the merge (like rows in a table)

    The datamerge comes out perfect, except for one thing. I want the imageframes containing the symbols, to automaticly fit the height of the inserted images – but only the height. The width of the imageframes should be fixed so that they all have the same width.

    I don’t have that option in Content placement options.
    Is there any way to fix this one little issue?

    Thanks in regard :-)

    /Søren

    PS:(I hope my english is understandable)

  49. Søren -

    It sounds like the think you need to do is create an Object Style for those image frames that has Frame Fitting Options applied (probably Fill Frame Proportionally, aligning from the top left), and apply that to the frames that Data Merge will use to place your merged images.

    In my experience, however, Data Merge isn’t smart enough to both place the image and apply that fitting option. You’ll have to do that on a second pass using Find/Change’s Object-based search-and-replace capabilities. Even though it’s an extra step, having assigned that Object Style to the placeholder frames will help you out a lot, because you can limit your search to only frames using that style. This will avoid changing frames you don’t want changed.

    After doing your merge, open Find/Change, click the Object Tab and, in the Find Object Format settings, choose the Object Style you created for those placeholders to limit your search to those frames only. In the Change Object Format settings, choose that same Object Style again (just to be safe), and also go to the Frame Fitting Options settings and re-establish the options that were originally built into the style. This will force the fitting to be re-applied when you execute the Find/Change operation. Test it out on one or two frames first before hitting Change All…just in case. :)

    Good luck! I hope this solves the problem for you.

  50. Thanks so much Michael!
    I’ll try that out right away

  51. Elliot —

    Sorry for the late reply. Have you resolved this problem already? If not, would you be able to send me your CSV and INDD files so I can look at the set-up?

  52. I’ve done my document EXACTLY like the video says, and when I preview there is still only one record per page. I even tried putting .0625 between columns & rows on the Multiple Record Layout tab. I’ve tried everything and no matter what I do I cannot make this work in CS5. SO FRUSTRATED!!!!

  53. Joey,

    Did you find a solution? We’re experiencing what sounds like the same problem.

  54. Bobbie Willis says:

    Hello! I am a high school yearbook adviser, and four years ago we left our Big Name Yearbook Publisher to print for a third of the cost with a local printer. Rather than using the yearbook company’s software, we have discovered that all of those tool already exist in InDesign, and it is just a matter of learning them.

    This tutorial on data merging was key in helping us to lay out the 1,400+ student mug shots for our class pages.

    One question I had was in placing the name text to accompany each photo: We built the data template as a photo frame and a text box right below it for the student’s name. However, traditionally, we have run a row of six photos across the page, with a vertical list of the six accompanying names to the left or right (depending on left or right page of the spread) of the row.

    We have gone through and linked each of six names in a row across, then selected them all and pasted them into a text box to the left or right of the row. However, you can imagine how tedious this is, and how many opportunities there are for errors in this scenario.

    Do you have any thoughts or recommendations on how we can automate this task? Many thanks!

  55. Bobbie –

    Can you e-mail me a snapshot of a sample spread so I have a clear idea of what you’re describing? I’d you could take the snapshot with the InDesign frames selected on the page, that would be ideal. You can e-mail it to info ((at)) theindesigner ((dot)) com.

    Michael

  56. Paul Tallbush says:

    It is garbage that Indesign Data Merge CANNOT do multi-up duplexing. Pure garbage.

Leave a Reply