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.
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.
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.
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: https://creativepro.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: https://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.
hi
help
I have problems with the Excel document and associated images.
When saving the file to termination. Csv league lost images, and add the “@” and ” ‘” before the @, but not found in the images if you put the text. and sends me a message that I extend.
The data source file refers to one or more images are missing. Unable to display a preview of the images are missing or combined. when creating a merged document, you can choose to generate a list of missing images.
I would appreciate if you tell me how it should excel in this image if league or another method
Thank you
Atte: Fernando Uribe
Hi,
Its about DataMerge functionality in Indesign cs4.
I want to implement dataMerge and Export using a plugin.
I have a tab separated datasource and an Indesign document having 300 pages.It has an image in the background.
I want to merge the source and Indesign document and export. If we do it manually the pdf size is more.
So I want to implement datamerge and export functionality through the code.
Can anybody help me Because I am not getting any information in the Indesign API.
Regards,
Ravi Sharma.
hi there
hopefully someone can help me out.
I’m using indesgn CS3. Acrobat Professional 8.1.2 and Microsoft Entourage 12.1.5 and Microsoft Excel 12.1.5 for mac.
I’ve been asked to look into sending out a individually named .pdf (from a database) document by email.
We have approx 1,000 names on the database.
I’ve worked out how to data merge with indesign using the following tutorial
https://www.adobe.com/designcenter/indesign/articles/indcs2at_datamerge_02.html
I can then create the .pdf document and extract each page as an individual .pdf document. All numbered.. Is this the correct method ?
Where I am stuck is how can I easily match these numbered .pdf documents to match the numbered excel list and then batch the .pdf to send the .pdf file to the matched person. It would take days to email each pdf document individually.
I hope the above makes sense.
thanks in advance.
Stephen
Maybe I’m missing something, but here is my problem:
I have a 12 page newsletter that I mail out to 400 households. I’ve used the Data Merge to set up the address block on the last page of the newsletter. After we print, we fold the newsletter in half and mail it.
The data merge is working perfectly… except that when I “Create Merged Document” I end up with a 4800 page document to print!!
Other programs I’ve used (MS Publisher, for instance) allows me to “Print Merge” which simply does the merge as it prints, rather than creating the document first.
Am I missing something? Is there a way to “Print Merge” in InDesign CS2? Or is creating the “Merged Document” the only way?
Even when I select a smaller number of records to merge (say, 50), that’s still creating a 600 page document and it takes my computer *forever* to merge it and then print it.
Is there a better way? Thanks.
@Chadwick: Unfortunately, there is no “print merge” in Data Merge. In CS3 (or was it CS4?) adobe added the ability to export directly to a PDF, skipping the step of making a document. But you’d still end up with a 4800 page PDF.
@David: Thanks for the info, David. That really stinks. Maybe I don’t know enough about professional desktop publishing, but it seems ridiculous to leave that feature out. Obviously they’ve thought about it and figured it wasn’t necessary. Maybe I’m too old-school anymore.
Hi Chadwick, i too am having the same problem. the newsletter that we now (painfully) print are 8 pages, saddlestitched. InDesign could also use a feature that would limit the booklets pages. I am desperately searching for a plugin that will handle this automatically. as it is, we have to merge each booklet into its own document and print each one. depending on the size of the doc, ripping takes forever. 2 days to print 225 copies AAAAAARRRRRRGGGGGGGHHHHHH (ripping my shirt, ready to smash my computer)
John
PrintShop Mail is a software solution that allows users to create dynamic, database-driven documents quickly and easily. PrintShop Mail allows you to personalize your promotional communications by varying text, graphics,barcodes, or entire layouts based on information found in your database.
I am creating a listings magazine, where all the information is supplied to me as a CSV file, with columns for venue, description, price etc. I quite happily told the customer that I could set up a template and then populate the booklet easily using the data merge function. After a couple of hours of trying to do this I don’t think I can after all! Can anyone help me out?
Im using Data Merge to Print personolized adressed enveloppes.
What to do if some cells are empty in Excel
An error will say some cells are blank, repair???
I am working with an Excel file and it is not allowing me to put the @ symbol before text, saying that the “form” is not valid. How do i fix this?
Oh sorry i meant the “function” is not valid.
You need to put and single apostrophe at the beginning of that field, it will not let you put in the @ because it uses that for it’s own scripts/functions. After you do that to just the column header followed by the @, you should be fine.
I have an Excel file with links to .rtf files that I would like to Data Merge into an InDesign document. When I save the Excel file to .csv, I get a message that it may contain features that are not compatible with csv, but I save anyway. Then when I try to do the Data Merge, I get a message that says the data source references one or more unsupported files. The files cannot be previewed or merged.
Any suggestions?
close the excel spreadsheet. then attempt the merge. it will error out if you don’t do this first.
Steve,
I have found your site very helpful. I am trying to do something i thought would be so simple. I need to create name badges using an Avery Template 74459. I had planned to use mail merge using an excel spreadsheet with the various information for each name badge. I have successfully created a single name badge using the data merge, but i cannot find the way to make it fill into a template to create over 100 name badges. I have tried to google help using every combination of words possible with no luck.
Thanks in advance for any advice!!
sorry – i did not tell you that i am using Indesign CS4
Hi Steve
I have viewed a couple of the videos on the indesign site and they have been extremely helpful.
I have an excel column issue. I have a column in the spreadsheet that contains a price and two different room type (m) and (m,e) (madeup or madeup/ensuite). I need to colour code these separately in Indesign. But, I need first to clarify which one will be (m) in the column and which one will be an (m,e) in the column. How do I separate this information in the datamerge for indesign to identify without manually doing it in the spreadsheet?
I’m using CS5. Thanks so much for any advice in advance.
Hi, I am a novice user of cs5.5, and am using a trial version. I am attempting a data merge, and I have set up the document I am wanting .csv info to merge into. When I select Window/Utilities/Data Merge a panel shows in grey shade with instructions in 3 steps. No. 1 being to choose Select Data Source from the Panel Menu, no. 2 Drag data fields from the panel to frames on the page etc, No. 3 Choose Create Merged Document from the panel below. But I cannot see where to even select the data source. there is not a panel allowing me to do this. Should I contact Adobe? Thanks
I have a project I need your advice on. We are doing postcards with variable art on both sides. Is it possible for Indesign to merge both the front and the back of the postcard through data merge??
Please help!
Iliana Rodriguez
This is very helpful. I see in your example you have a first name field with the name “Rufus” and inside the copy, the variable data is entered and it turns into “Rufus’s”. Technically it should be Rufus’
I’m having this issue in my files, with the possessive apostrophe. What’s the workaround or trick so that you won’t have to go into each individual page and edit/override a master page element? Thanks!!!!
Kat: Well, there is no official rule for whether the “s” should be there. Strunk and White says that it should be “Rufus’s” (see https://www.bartleby.com/141/strunk.html#1)
However, if your company style guide says to remove it, you’d either have to remove it manually, or perhaps make a grep style that makes it disappear. For example you could make a character style called “disappear” that set the color of text to None and sets the size of the text to 0.1 points (so it essentially disappears). Then apply that character style to the s at the end of just these words by applying it with a GREP style. (Search this site for info about how to do that if you don’t know.) The grep expression would be
(?<=s~])swhich means "an s that immediately follows an s and single right-curly apostrophe.(If you see a space before the = symbol in that code, then remove it. I'm not sure why it's showing up like that.)
Another way to hide characters is to use the font »Adobe Blank« and GREP style. (Causes no preflight alert because the font size is too small.)
https://vimeo.com/85426001
Roland
Thank you so much for the tips with GREP styles. I ended up going in and using Find/Replace and changing any instances with “s’s” to “s'”
Appreciate the insight and I’ll definitely look into the GREP style option.
Hi Steve !
I’m working with data merge to create labels and I’m looking to do something I haven’t seen addressed anywhere…
On my CSV file there’s a column with numbers that indicate how many occurrences (or printed copies in that case) of that label I need.
Some have just one occurrence but some have 2, 3 etc.
Can indesign understand that column as a rule on how many times to repeat that same label?
Without having to go to my csv and repeat that row 2 or 3 times instead?
Thank you !
Chloe
Chloe: Nope, sorry. You have to set that kind of thing up in your spreadsheet first.
Hi David !
Thanks for the reply !
I’ve been taking your class on lynda.com, very happy to streamline some stuff with data merge !
hi, i’m new to indesign and i’m trying to make a document with 40 pages with 6 tickets on a pages. each ticket needs to have a different number, but to be consecutive from 1 to 240. i’ll be gratefull for any help. thanx
rz1: There are many options, including: https://creativepro.com/making-numbered-tickets.php (see comments after that, too)
and
https://creativepro.com/creating-sequential-numbers-data-merge-script.php
Thank you David, it worked just fine.
hi, I need to do 800 diplomas and send it by email, is there a way that all can be done with data marge?
Thanks
Maria: Data Merge could make the PDF, then you could split the PDF into separate PDFs, but I’m not sure how you would email them out.
Can I use PHP coding and google graphs to insert data into the specified fields?
Hi! thank for this wonderful website. I am new in data entry job. Is there any tools to preview pdf file and enter data from pdf file in same frame then export it in excel ? Please help me.
great article and followed your step. I do have one question:
Is there a way to to have a certain type of color in an excel cell and be able InDesign to read the color? Is there a formula i can place in excel that when save in csv and loaded in Indesign the image box turns the desired color?
Love data merge and left with question when previewing before selecting final OK w 800+ records. How can a long name NOT be hyphenated or is this a preview and manually adjust after merge? Thanks,
How do I package the data merged pdf and datasource(text delimited) to deliver to the client. The client does not have Indesign software.