Automating Data Handling
InDesign CS2 has a powerful automation capability that very few people use: its Data Merge feature. This feature originated in Adobe PageMaker, and was enhanced and brought into InDesign CS if you bought the PageMaker Plug-in Pack. It arrived as a stealth feature in InDesign CS2 with little fanfare.
Data Merge can create documents which have personalized, or variable, information — such as business cards, mailing labels, form letters, even simple catalogs. The data for the personalization comes from the structured information in a database. But you can use a wide range of sources: Any data source that can create comma- or tab-delimited text is fair game (including Excel spreadsheets, database files, or Microsoft Word documents with tabbed text). You can, of course, do a mail merge in Word, but in InDesign, you have access to all its typographic refinements.
Data Merge can’t be described adequately in a single blog entry. Instead I’ll spread this description over several weeks. Today, I’ll describe what I’ll be creating in this series, and introduce you to preparing data for Data Merge. In subsequent weeks (I’m hoping to post weekly), I’ll cover creating frame placeholders in InDesign, importing the data and previewing it. We’ll also cover including images in the data, and laying it out with multiple records per page. (David also posted a blog entry in December 2006, answering a question about Data Merge. It pointed to a free article on Data Merge from InDesign Magazine by Rufus Deuchler.)
We’ll be creating two documents for an imaginary veterinary hospital: The first is a card to remind its clients that their pet needs to be brought in for its annual checkup. The reminder card will have variable data for the name of the pet, the date of the last visit, and so on. And there will be three different images used, depending on if the pet is a cat, a dog or a bird.
ReminderCard1
ReminderCard2
ReminderCard3
These cards display the variable data all on one page. But frequently we want to print multiple instances of the data on a single sheet. For our example, we’ll imagine that after the exam, the veterinary hospital sends out an examination certificate which is printed 4-up on a page. It will use the same data as the reminder card.
Certificate
If you’re not familiar with data sources, it’s helpful to understand their language: A data-source file (which contains the structured information of a database or spreadsheet) is made up of fields and records. A field is a category of information — for example, an address or a zip code. A record is the complete set of fields that extend across a row of the data: Each field is separated with a tab or comma character, and each record is ended with a return character. Here are two more bits of terminology: The InDesign document that contains data-field placeholders is called the target document. It may also contain other boilerplate information — in this case the reminder letter copy — that remains the same in each iteration of the data merge. The merged document is the InDesign document that contains the boilerplate information, repeated as many times as it takes to accommodate each record selected from the data source.
Open up your data-source file (for example, the Excel document shown below), and save it, if necessary into a tab-delimited text (.txt) or into comma-delimited text (.csv) file. Notice that the first line of the data must list the names of each field; you’ll use these in your InDesign layout to designate where the variable data will go.
Excel data
We’ll cover how to set up your InDesign document in next week’s blog.
hi Steve,
I came across with the data merge option when investigating an XML job.
Although the data merge wasn’t to handle this automated layout, it was nice to see to import data from a database (or spreadsheet) without using a plugin.
Marcel Verschuren
As a designer of database-driven web sites, I’ve always been interested in InDesign’s ability to import data. Ultimately, I’ve have never been able to use it for any of my projects because I have not found a method to adjust the layout to automatically accommodate fields where the data size can change by large amounts. Hopefully, you will cover some methods for automatically adjusting layouts to accommodate the data in your blog. Thanks.
I have no guarantees that the Data Merge feature can do what you want. But can you be more specific about what you’re trying to do? Can you be more specific about what you mean by, “the data size can change by large amounts”?
I’m looking forward to reading this series as I am about to lay out a catalog based on data exported from an actively updated Access catalog. Like Peter mentioned, it would be helpful if each text box will grow or shrink in dimension to accomodate the varying lengths of data, in cases when data within the same field is 10 characters in one record and 100 characters in the next. Thanks very much for writing on this topic!
I just did an experiment. If you place your field placeholders (which we’ll talk about in the next blog) into text frames, the frames won’t grow.
But if you place them in table cells, when the height of the table rows are set to At Least (rather than Exactly) the rows will expand to accomodate long entries. You can make a one-column wide by x-columns deep table for all the records of one instance of the data.
You will be limited in that when you’re placing multiple records on a page, each set of data must take up the same vertical space in order to duplicate on the page and over multiple pages.
Hi i’ve used the data merge feature doing our schools yearbook. Yes the yearkbook. It took me quite a while to undestand it but it really helped me a lot. 2005 yearbook contains 3,000 graduates. I just entered all the necessary infos on a excell spreadsheet and did a template on indesign and it did wonders. If only i can show you the finish product. This is a wonder feature. Now i can do big jobs like these in no sweat.
Great topic, Steve. This is something I talked about (literally) in one of my early audio-only podcast episodes called “Urge to Merge” which you can find right here on InDesign Secrets at: http://indesignsecrets.com/episode-7-the-urge-to-merge.php
Just click the text in the description that says “this week’s episode” to either download the MP3 file or listen to it through your browser.
I’m looking forward to the rest of your posts!
Thanks for mentioning Rufus Deuchler’s Data Merge article in a previous issue of InDesign Magazine. He’s also written another article that talks about automation solutions beyond Data Merge. That new article will be in the next issue of the magazine, which comes out February 14.
This topic is of great interest to me, thanks for delving into it! In my case, I’m very new to InDesign (with about 10 days left on my trial copy). I fall into that situation as Peter Hertzman (above) does, whereby a ‘memo’ type data field may contain a simple 5 sentence paragraph worth of text, or a 5 PAGE series of paragraphs. I saw the response to Peter’s post, and will look into that as well. Thanks again for covering this topic.
“You will be limited in that when you’re placing multiple records on a page, each set of data must take up the same vertical space in order to duplicate on the page and over multiple pages.” Steve: that’s the rub. Even something as simple as a database driven bibliography needs to be able to vary the amount of vertical space for each entry and to auto-flow to the next column or page when necessary. I have a database-driven bibliography that changes a couple of times per month. I would have liked to use InDesign, but wound up writing some PHP code to output a PDF file instead.
Troy and Peter,
It sounds like Data Merge ISN’T the right choice for your applications. It definitely couldn’t handle five pages worth of data in one record. It definitely can’t vary the vertical space for each set of data. That’s just the limitation of the built-in feature.
Have you looked at third-party data handling solutions? As Terri mentioned, there will be a review of these solutions in the next issue of InDesign Magazine.
Steve,
As I mentioned, I’ve been building PDF files directly from the database. The programing takes a little time, but the generation is quite fast—which is helpful when the file has to be rebuilt on a regular basis.
Hi Peter,
A thirt party plugin is a option, or maybe the XML workflow. Although you have already figured it out to generate a PDF from de database.
I am currently working on a XML project which involves illustrations and text. Both in variable numbers and length. I have the data in a database and generates an xml file which ID uses. Indesign is then making the PDF. This way i can use the ID features, which where tricky to do in the database.
I do need to do some fine tuning, but that is due to my xml experience …. ;o) … i guess.
We print our business cards in small batches in house and I just created a page with the data merge function. It works but the thing I thought it could do was if you created the item on the page once you could tell it to duplicate it self over the rest of the page. So far I have not been able to do this. If I create 10 cards on the page with the data merge fields it will print 10 cards but if only one instance of the data fields is on the card it only creates one card. Either way it is a time saver for me.
Rich,
To use that feature, choose Create Merged Document from the Data Merge palette menu. Then on the Records panel of the dialog, choose Records Per Document: Multiple Records. Then click on the Multiple Record Layout panel to set the options.
Marcel,
I may be mistaken but doesn’t the XML formatted data still need to be imported into placeholder frames? It has been a while since I investigated using XML for my application, but I think it is the inability of placeholder frames to grow or shrink with the data that led me away from that approach. Maybe, I look at XML again.
I think I also looked at using tagged text, but I couldn’t figure how to generate stories based on the size and amount of the data.
Peter,
Your right, the XML is loaded into an text frame. I have to see if there is a way to adjust the frame size according to the length of the text inside the frame. I know i saw something at the indesign exchange, but cannot remember the details or options of the script/plugin.
The job i am working has a fixed length it had to fit onto one sheet of paper.
You probably know this already, but..
When import an xml file, the data can be filled into one frame or in several individual frames (not connected to each other). It isn’t necessary to have it al in one connecting frame.
i just visit your site…
if one is not hungry he will be.. smiles…. do you publish also as a PDF-file ?
Marcel,
I keep a database of my French cookbooks on a local server that I access and update through a web page. From the page, I can output a formatted bibliography as a PDF file or a PML file (which I load on my PDA for when I’m out shopping).
[...] Last week I began a series on using InDesign’s powerful Data Merge feature. After describing what Data Merge can do, I described the data-source files it can use. In Part 2, I’ll show how to create the target document—the InDesign document into which the data will flow—and how to link the data to the document, and preview the results. [...]
I use this feature most often just to number invoices, work orders and that type of stuff on our copier. I look forward to seeing what else it can do for me.
I have just found this blog and wounder if there is another bit Ihave missed
William, this is the first of a multi-part series, as I explained in the blog. The second part was published last Sunday. You’ll find at the top of the Home page for http://www.indesignsecrets.com.
I am building a PDF version of a members directory and have had no problems getting the data merge to work but I am experiencing difficulties getting the hyperlinks to work when the indesign file is converted to a pdf.
I specify that the text is a hyperlink, that it is a “url” and still can not get the darn things to come across into the PDF version as “live” links.
Any help would be very much appreciated.
Thank you
Andrea
Andrea,
Make sure you’re exporting PDF rather than using Distiller. Also, make sure you’ve checked Bookmarks and Hyperlinks on the General panel of the Export PDF dialog box.
Man, you saved me!
I just wanted to chip in and say a big thanks for your help.
I’d read about this being doable but I hadn’t quite got the hang of it. What dawned on me was to try dragging-and-dropping on the master page. It wouldn’t allow d-n-d on spreads.
Thanks a lot!
Steve,
I use IDCS2 and I create technical booklets that usually have 100+ pages. My delima is that within the pages of these booklets we reference page numbers for differing pages. Frequently we add or delete pages and I am put to task to manually update internal page references. My question is this. Is there an available plug-in, extension or code that would allow me to accomplish this automatically? Thank you.
Gordon
Gordon,
I think what you’re looking for is a feature usually called cross-referencing. It is found in an application like Framemaker, but not InDesign. However, I have heard that there is a script for doing cross-referencing. I’d suggest posting your request on the InDesign User Forum if someone here doesn’t know the name of it. I think the person who created it frequents the Windows User Forum, as a recall.
Steve,
Thank you for the heads up. I also left a comment in your second issue of the data merge. I am not able to preview my images. I have tried different extension, paths, locations. Nadda. Also noticed that in my data merge pallet the images field has a “T” preceeding the filed name indicating that it might be a text field?
Gordon
[...] It’s been much longer than I expected finishing this three-part tutorial on InDesign’s Data Merge feature, but it’s been a very busy period for me. You’ll find part 1 here, and part 2 here. When I left you, I had created a reminder card layout in InDesign CS2, and created text and graphic frame placeholders. I selected the data source and linked the data fields with the placeholder frames. Finally, I previewed the result. [...]
Hi There.
I can use the merge function but am running into one problem I hope you can help with. I have data in an access database which includes carriage returns. I’d like these carriage returns to appear in the merge but they don’t. Can anyone help? Please…
When I insert some letter in my txt files like ï or ç it won’t work when I do a preview in InDesing….
While I recognize that Data Merge is a useful tool for basic mail-merge jobs, users should aware of it’s limitations — it’s certainly not a replacement for third-party InDesign VDP plug-ins (Darwin, uDirect, InData and others). I’ve written a post which explains the pros and cons of personalization features in Creative Suite (InDesign, Photoshop, Illustrator and Dreamweaver) which may be of interest to readers. Check out: http://www.veedeepee.com/2007/10/free_vdp_1.html
Thanks so much for posting these tutorials! A question: In the catalog I’m trying to setup I get data from several sources (its not all in one file) and its not efficient for me to combine them because they are different fields/groupings, etc. Can I specify more than 1 txt or csv file when I am setting up a Data Merge?
I have used this key in Indesign but now I need help. I need to merge in the same doc all data. Because I have data in a table: Jan, Feb…how can I do that. In Word data merge I use next record.
I came back to this article for a refresher. I wish there was a way to print these neatly and keep them in a notebook!
has anyone ever worked with the inData plug in, use to bring in database files into indesign.
i’m doing a directory and wanted to get this to help??
THANKS!
kim
@Kim: InData is a wonderful tool for importing lots of data files (in tab- or comma-delimited format) into InDesign, formatting it automatically. You can find it at emsoftware.com. It’s more powerful than Data Merge. Another good tool is DataLinker from TeacupSoftware.com.
So I use data merge practically every day. I create season ticket books, so i design the first page, and use data merge to create my other pages. My excel sheets can get rather confusing (day, date, time, opponent, game #) for each ticket, and sometimes we have about 6 tickets per page and up to 40 or so games.
I’m trying to use the automation to variably place opponent logos onto each ticket. I can not figure out why my indesign tells me that some of my images are missing and that when i do the data merge it will give me a list of them all. when i look at the list, it’s ever single one. I believe my pathnames are not correct. I’m on a mac and pulling my logos from a file on the server. I know it’s case sensitive. and i know i need the @ command at the top of my column. so my column says “@ logos” and my pathname kinda goes like :Volumes:artwork:Customers:Seawolves:2008:SEA WOLVES TICKET BOOKS 08-09:LOGOS:charlottecheckers.eps
Is this not how it is done? Am i missing something?
I go into indesign, create an image box, set the frame options to center and porportionally fit and all. then i take my database field, and drag it into my image box. And nothing happens when i try to preview it!!!!
any ideas?
@Rachel, it does sound like your path names are wrong. To be honest, I have never really figured out path names on mac or windows — it’s always so frustrating. So when it comes to datamerge, I just always try to put the txt (or csv) file in the same folder as the images. Then I don’t worry about file paths at all. I just use the name of the image, and data merge finds it every time.