Thanks for coming to InDesignSecrets.com, the world's #1 resource for all things InDesign!

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.

Steve Werner

Steve Werner

Steve Werner is a trainer, consultant, and co-author (with David Blatner and Christopher Smith) of InDesign for QuarkXPress Users and Moving to InDesign. He has worked in the graphic arts industry for more than 20 years and was the training manager for ten years at Rapid Lasergraphics. He has taught computer graphics classes since 1988.
Steve Werner

Latest posts by Steve Werner (see all)

  • - November 30, -0001
Related Articles
Comments

76 Comments on “Automating Data Handling

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

  2. 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?

  3. 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!!

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

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

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

  7. 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 http://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~])s which 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.)

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

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

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

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

  11. 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?

  12. 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,

  13. How do I package the data merged pdf and datasource(text delimited) to deliver to the client. The client does not have Indesign software.

Leave a Reply

Your email address will not be published. Required fields are marked *