Getting Rid of Empty Lines in a Data Merge
We’ve talked quite a bit about InDesign’s Data Merge feature over the years, and I’m recording an in-depth title on the subject for lynda.com (should be out by spring). But one problem which often plagues hopeful data-mergers is the situation when sometimes a data field has some content and sometimes it doesn’t.
For example, in this spreadsheet, the Street2 field is often empty, but sometimes it’s present:
So if you take that data file and build a Data Merge template with it, it might look like this:
But when you merge your document, you may see a blank line after the first address field!
That’s horrible. Fortunately, there’s a simple fix: Before you generate the merge, select Content Placement Options from the Data Merge panel menu and turn on the Remove Blank Lines for Empty Fields checkbox.
If you’ve already merged your document, you’ll have to regenerate a new merged document. (In other words, this dialog box doesn’t fix it retroactively.)
 |
Good tip! However it isn’t too hard to remove blank lines in an already-merged document by using find-and-replace: search for two sequential paragraph marks (i.e. ^p^p ) and replace them with a single paragraph mark (ie. ^p ). Keep “changing all” until they’re all gone.
Update: Just checked the find/change and there’s a saved query called “multiple return to single return”.
Instead of using Data Merge we use the Smartstream option. You can modify the text channel to remove blank lines and spaces so it doesnt put in extra commas or periods, etc. It’s an amazing tool for envelope addressing!
We also have SmartStream, but I don’t see the feature that William Hamill is talking about. William, if you happen to see this post, I would love a nudge in the right direction. We have a 6 week mailing that we are working on and we just went and sent the first week with the blank lines, because of time constraints, but I would love to send the other 5 weeks out with nicer looking mailing address blocks.
Smartstream is awesome, but a bit expensive. Too bad HP isn’t promoting it more, they could sell a lot more.
The Data Merge function of Indesign is still very good and much under-appreciated.
Awesome! Exactly what I was looking for. Thanks!
Using the delete empty lines option can cause your paragraphs to have the wrong style applied (with local formatting applied so you might not notice until you try to make a TOC). This happens because InDesign leaves in place the filed markers for the empty lines and moves them into the subsequent paragraphs, taking their paragraph styles with them.
We recently had a user with this problem on the Adobe U2U forum, and thanks to Uwe Laubender we’ve come up with a much better solution. The first step is to NOT use the built in blank line removal so all of the paragraphs are maintained, but you cannot at that point simply remove the empty paragraphs after the merge or you wind up with changed styles.
Instead you need to first remove the field markers. A plain text Find/Change for and replace with nothing will remove them. At this point it is safe to remove the empty paragraphs. Uwe kindly wrote a script to combine the operations into one action:
//Uwe’s remove blank lines without changing styles
//Scope:
var aDoc = app.documents[0];
//Step 1:
app.findTextPreferences = app.changeTextPreferences = null;
app.findTextPreferences.findWhat = “”;
app.changeTextPreferences.changeTo = “”;
aDoc.changeText();
app.findTextPreferences = app.changeTextPreferences = null;
//Step 2:
app.findGrepPreferences = app.changeGrepPreferences = null;
app.findGrepPreferences.findWhat = “^\\s+”;
app.changeGrepPreferences.changeTo = “”;
aDoc.changeGrep();
app.findGrepPreferences = app.changeGrepPreferences = null;
alert(“Done!”);
@Peter – the most important thing did not make it to the shown lines of code. The FEFF marker in brackets in the findWhat expression (maybe due to this blog software editor?!)
So we better link to the code here:
https://forums.adobe.com/message/5883785#5883785
Uwe
Ouch! Good catch there. I didn’t even notice.
Good info on the delete empty line; we do a lot of data merge for postcards. These are often printed 4-up, which brings me to a problem I currently have. There are 4 target spots for addresses on the backs of the post cards, but our data comes as a spreadsheet with one long list of addresses, so I need to cut and paste each section and give it a different name (such as: Name2, Name3, etc.). Is there any shortcut to getting the data merge to recognize the row in the .csv file? That would save me a lot of time!
Checked my merge setup, have it checked, as this has been however the blank line is still there. I checked my Excel merge file to make certain there was no blank line or space in the field by clearing every record and still no success.
I don’t know if I have a formatting issue with my master page however everything works successfully if the field has an entry. Hmmm.
Remove blank line for empty field is checked PRIOR to doing the merge, when the merge is completed …. there are still blank lines.
This fix is still not working for me.
I tried all of this too and it still shows blank lines.
I am running into the same problem… blanks are still there.
Trish,
If you show non-printing characters I suspect you’ll find there’s some sort of white-space inserted into your line like a space between two empty fields, or possibly a punctuation mark you’ve overlooked. I’ve had both of those problems.
That sounds logical… blank character space. I’ll try that.
Is there a way to use this with Tables cells?
I have to data merge in tables, but some of them are sometimes not filled, that way i get blank table cells.. Is there a way that indesign automatically erases the empty tables?
Ruben: Data Merge doesn’t really work well with tables. You can learn more about using data merge and tables here: https://creativepro.com/using-data-merge-to-create-a-table-for-a-directory.php
Is there a way to do this with images as well? For example, I have 3 images each set to different columns. Sometimes, the 2nd or 3rd images are not there. I would like to center the results in a page. Currently, inDesign always reads all 3 image blocks and centers all 3 and not just the ones that are showing.
Isaias, I’ve had the exact same thing that you are talking about, and we were not able to find any way around that one. Maybe someone else here does. We struggled with it for a week or more. No solutions.
For html or php files that I’ll post on the net, I like to get rid of empty lines.
I have the “remove blank lines” field checked before I merge the document, but the spaces are still there. It doesn’t work for me. I checked the csv file and all the blank fields are really blank, there are no characters there, not sure why this doesn’t work for me.
This probably means you have some sort of whitespace or punctuation on the line in addition to the field(s).
Peter Spier, I cleared all the blank fields. (selected the blank fields/edit/clear contents). I just doesn’t work, there must be a bug.
I had to split the csv in two to separate the two types of information. It works fine that way, but it’s a pain, I now have two indesign documents, two csvs, and after merging the data, I merge the two final pdfs to have only one document. So silly and irritating. Waste of time.
It has nothing to do with the fields themselves.
If your line has a typed space, comma, period, etc, before or after one of the fields, the line is not blank and will not be removed. Turn on non-printing characters and look to see if there is any sort of space.
How do you turn on non-printing characters in Excel? I’m not familiar with this feature, I searched everywhere and can’t find an answer.
It’s not the data file that is the problem, it’s your .indd template file. View the non printing characters in InDesign.
Viewing invisible characters revealed I don’t have any spaces or special characters between my fields. So I tried viewing invisible characters in Data Merge preview. It’s showing me that empty variable fields are represented by what looks like a double colon ::
However, when I copy and paste it into Find/Change, it shows up as ^|^| and Find/Change says “could not be found”
When I view invisibles, I have lines that look like this:
>>::>>::>>::¶
>>::>>::>>::¶
I had checked the “Remove Blank Lines for Empty Fields.” It just isn’t taking them out.
When I used to use INDD CS2, I had a workaround, which was to select my return characters, and make them 1pt with 0pt leading. That no longer works, either, as it screws up the leading for the whole preceding line (and yes, I unchecked the “whole line” in preferences).
Robert, you appear to have some sort of character between your fields represented by that >>. Those lines are not blank.
As far as using Find/Change to remove the empty filed markers, as noted above, you must do a plain text search for
Drat, the forum knocked out the search code, again, You need to read the link to Uwe’s code at https://forums.adobe.com/message/5883785#5883785
Robert, I had the same problem & I figured out what the problem was. Soft Returns.
I use soft returns all the time, and I like using the “space after” feature. But you can not use that when data merging. Stick with hard returns only. See example below
Soft Returns (blank space)
<>⁊ Houston
<>⁊ 12230 West Road
<>⁊ ::>
<>¶ Houston, Texas 77065
Hard Return (removed blank space)
<>¶ Houston
<>¶ 12230 West Road
<>¶ Houston, Texas 77065
<>¶
Ok, that makes sense, but how do you separate the fields on Indesign? I need to put date and time separated somehow on the same line. EX: “Friday, September 11” (space dash space) “10:00 AM – 4:00 PM”. Space dash space would be in Indesign, and that might be causing the merge problem. Could be also “space bullet space”, but it needs some separation from the date field. Thanks for bearing with me. :)
If you want the delete blank lines to work, you can’t separate fields that way. You would need to make another column in your Excel file and concatenate the other columns into a single field (you can use an IF statement in your formula in Excel so that it leaves the concatenated column blank if there are no entries in the other columns for that record.
The other way to deal with this is to use Find/Change in ID to find lines that have only the punctuation or spaces.
Hi all,
I the data merged document after selecting the invisibles in InDesign and copying them into the Find/Rep-ace window I get the “cannot find a match” window. I alos tried (i.e. ^p^p ) but no luck either.
This is what I’m pasting in :tab::tab::¶
This is our data merge code for <>tab<>tab<>¶
I’ve tried using glyphs in the find/replace window to no avail. Can you help?
You can’t paste the filed markers into Find/Change. You have to use the code. Read Uwe Laubender’s post at https://forums.adobe.com/message/5883785#5883785
Hey everyone! This is a very late reply but just in case this can help anyone.. I came here because I was having similar problems of the blank line not removing after checking that the excel cell was indeed empty and that there were no special characters in the supposedly blank line. Turns out, when I was setting up the address block, I was doing shift+enter versus enter. So I was not starting a whole new paragraph line but continuing the line above it. I redid the address block hitting ‘enter’ for each line, and voila! Blank lines are removed.
This was the case for me as well — I was wondering why this method didn’t work and found that it does not remove soft breaks. It must be a Paragraph break, and then it will recognize and remove the blank line.
Hey everyone, thank you. One problem has been solved after reading this.
My data has a field for first name and last name. Not all of the fields have a first name.
When I merge it in InDesign it puts :: in place of the first name (where absent).
I cannot figure out what it is in order to search & replace it.
Any ideas?
Read Uwe Laubender’s post at https://forums.adobe.com/message/5883785#5883785
Any one know how to get rid of trailing spaces?
Say in my data I have a field for salutation IR Mr. but some records are plan I don’t want the name to start with a space.
you sir are a genius, for many years i have done this data merge with horrible blank line. Thanks to you i now look a little more profession in my data merging. Thank you
I have a more complex merge issue. Is there a way to tell ID that IF a certain cell on a row is Blank, then DO NOT include that ENTIRE ROW on a merge?
Jerry,
That sort of logic is beyond the capabilities of InDesign’s native Data Merge, but if you have a need for this on a large scale it would be worth looking at one of the commercial catalog plug-ins that are available to see if it would do what you need.
Is there a way to do this exact thing for blank image fields?
This was perfect.
Thank you for posting this.
Hello, I am creating a directory with 1200 records. One of the fields is called “Category” in which there will be anywhere from 1 to 30 records. The “Category” field has a specific paragraph style applied and the following field, “Company”, is on a separate line. I have selected “remove blank lines for empty fields”. When I preview the data, the first record looks great with “Category” styled on one line and then “Company” styled correctly, but then the next record has “Company” styled as “Category”. All records are like that unless the “Category” field has data in it. I hope that makes sense. Any suggestions or advice on how to get the empty “Category” field to not restyle the “Company” field?
I am also using the “inlinemerge” script in order to merge multiple records in one story.
I see this is a very old thread but I’ll try anyway. I am getting unwanted line spaces too, and use the suggestion David has here. However apparently, ID is seeing content in “empty” cells and creating the line spaces. Even if I highlight the offending fields in Excel and clear data. In ID, with invisibles turned on, I can see these characters as “blue colons”. I can highlight the first ¶ then these characters and the last ¶ marker, paste into the find/replace field and it makes the claim that there are no instances in the document. I can’t search for a ¶¶, because these invisible characters exist in the series.This is a real problem and the client doesn’t want the spaces although the problem is obviously originating in his spreadsheet. I’ve done a screen shot but don’t see an image upload in this comment tool.
I discovered in Adobe community to use a GREP search for s+$ . Works!