A Data Merge Trick to Remove Unwanted Lines
InDesign has a fantastic feature called Data Merge. It allows you to use data from Excel (saved as a tab-delimited, CSV, UTF-16, or other text format) to fill placeholders in InDesign and auto-generate finished documents. How cool! But occasionally your database entries won’t be consistent and you’ll have to find a workaround to get things formatted properly.
In his article, I’m going to show how to get around a problem with the handling of blank lines (empty cells in the spreadsheet).
First, set up a spreadsheet and populate it with the data you need. The spreadsheet needs to be as clean as possible, and everything needs to be separated into cells.
Set up your document to merge (if you haven’t done so already). For a detailed introduction to Data Merge, see the series of posts by Steve Werner, Automating Data Handling.
With the Data Merge panel open, you can insert the Type tool into the text frame, then click on the item you want to appear in the space you’ve provided. Then you can preview your output by clicking Preview, and cycling through each item using the arrows next to the Preview button.
Lastly, you can go to the menu in the top-right corner and create your merged documents. You can either Export to PDF directly (my favourite method), or you can Create a Merged Document.
A Potential Problem: Handling Blank Lines
Now imagine you have been given a database with 500 names in it. And not all of them had mobile (cell) numbers. Maybe some don’t even have an email address… this is what happens in real life when you create the merge.
Database has holes:
Previewed in InDesign:
In the Data Merge panel menu, you can choose Content Placement Options, and select Remove Blank Lines for Empty Fields.
But since the word “Mobile” is on the line, InDesign does not recognize it as a blank line.
This would be OK if you have 10 or 20 or even 50 business cards, you could sort them manually and fix them up. But what if it was thousands of entries in a database? It would be a nightmare to get through all those pages of data to find a random person who does not want their phone number, or mobile (cell), or email on their card!
The Solution: Use Numbering in your Paragraph Styles to Create Headings
What I haven’t shown you so far is that I’ve threaded my text frames. This is the first key point for solving the blank lines problem.
The second key point is to use automatic numbering in the paragraph styles to insert words like “Office: ” instead of real live text.
In the screenshot below, I’ve used the character style “Blue” for the “Office:” “Mobile” and “Email” address.
Open the Office Number paragraph style and go to the Bullets and Numbering Section. In the Numbering options, set the Format to “None”, and type in “Office: “.
- Do the same for the Mobile and the Email address styles. In the Number field for the Bullets and Numbering, insert “Mobile: ” and insert email for the other Paragraph Styles.
- You will probably notice now that you have duplicate text.
This is because the paragraph style is inserting “Office: ” at the start of the line. Now you can remove the text you manually typed, and those lines will truly be blank.
Now for the magic: Turn on the Preview in the Data Merge panel. Mysteriously and magically, the Mobile Line of text has disappeared!
Complete the merge
Turn the preview off. It will look like something has gone horribly wrong. It now says Office twice!!!
But don’t panic, we can fix this. On the second Office line, insert your text cursor, and reset the paragraph style to Mobile.
Unfortunately, at this point you can’t preview before completing your merge. That’s why I said at the start to ensure you have a clean database (well, as clean as possible). Don’t select the Preview option. Just continue on with the merge. You cannot preview without having to reset everything.
Export directly to PDF from the Data Merge panel.
See also: Datamerge direct to PDF
Then create a merged document. Now you can check all the entries to make sure things went smoothly.
You can download all the files for this trick here: DataMergeMagic
If you need to supply your file to a client it’s best to remove all the numbered paragraphs where you’ve inserted text. Luckily, this is a one line script that can be applied to your Merged Document: ConvertBulletsNumbers2Text
See also: How to install scripts in InDesign