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.
Save your file as a CSV, tab-delimited, or UTF-16, whatever works for you. Check for any characters that have accents on the letters, which can sometimes require special handling.
Import your data to InDesign via the Data Merge panel which can be found under Window > Utilities > Data Merge.
Navigate to where you saved your file from your spreadsheet.
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.
See also: Formatting tips for Bullets and Numbered Lists
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
 |
Absolutely briliiant!
Cool idea :-)
I had encountered this trick before but was hesitant about using it because of the issue that occurs during preview. If users are aware of it, that’s fine, but because it’s an easy to miss (especially if someone else has to take over the artwork and is unfamiliar with this concept) it can go off of the rails easily.
The write-up is here: https://colecandoo.com/2012/08/10/no-fills-grep-styles-part-2-the-glitch-of-the-merge/ – that said, the solution I offered in my article isn’t so great on reflection.
I agree it has pitfalls, which I think I outlined okish – any articles I use for work things I like to leave a link to the workflow or to an article in the folder or on the pasteboard for the next worker.
However, I was working on this alone, and it needed a solution and that’s what I came up with.
I had thought about the next person coming along, and that’s why I said to convert all the Numbered lists back to text.
It’s a great write up there too Colin – more than one way to skin a database, I guess :)
It’s a complex workflow for a complex and unique situation.
> It’s a complex workflow for a complex and unique situation.
Is it, though? Just about every business card merge has this issue. It would be very useful if InDesign had a solution for this that wasn’t so hacky (and fixed the preview bug). Even just a checkbox to “Remove entire line if field is empty” would save a huge amount of time.
Another option (as Dixie Church mentioned) is adding the label to the data. If you’re using Numbers (I’m sure Excel is similar), you can create a new column next to each potentially-blank field and use a formula like IF(LEN(B2),CONCATENATE(“Mobile: “,B2),””) and then fill down to generate a new column that has the label only on cells with content. Then you can use the “Remove blank lines” option to skip blanks.
I wouldn’t say every business card merge has this issue. I had a lot of spreadsheets to merge. It was the way I choose to do it. Yes another approach was to add in Mobile Phone Fax Direct Email Web etc in front of all fields. For all spreadsheets. That works too. But there was a complex situation that I can’t go into. Hence a watered down set of business cards as an example is used here. Let’s just say I didn’t have to open 100 spreadsheets to add in Mobile Direct Fax in front of everyone’s detail. Although that would have been easy. It’s tedious work when doing a lot of databases. And it works.
THANK YOU! I took care of this in the spread sheet – ie, added the word MOBILE: or what ever in the cell. Thankfully it wasn’t a huge database. This looks like more fun, anyway :-)
Nice one !
Nice trick! I’ve used GREP to hide empty lines (given them “size: 0,1 pt + leading: 0 pt + colour: [None]”) with something like “(.+)\t\r” which simply applies the grep and “hides” the whole line if there’s no text after the tab character.