Hi, this is the first time I have done a data merge in InDesign. I am trying to insert an address list that often has blank fields in it. The fields are blank since some fields are not relevent to some people (i.e. some of the people don't have cell phones others don't have email addresses) Therefore these people would obviously leave those field blank. The trouble is when I go to do a data merge I am told that the CSV file won't import because some fields are blank. I don't know why InDesign is telling me this, or course some fields are blank they are not relevent to that person. Can someone please tell me how I can get around this problem?
Great, it allowed me to import that CSV file. The problem now is that it will leave a blank gap in InDesign if the field was left blank. For an example I had one line dedicated for the phone number and then used a paragraph return to put in the cell phone number below that. If someone doesn't have a cell phone number the line below phone will just be blank. I could put cell phone on the same line as phone but then there is a weird paragraph break in the cell phone number. Do you have any ideas for how I can fix that?
Great, it allowed me to import that CSV file. The problem now is that it will leave a blank gap in InDesign if the field was left blank. For an example I had one line dedicated for the phone number and then used a paragraph return to put in the cell phone number below that.
You CANNOT have any sort of line breaks in your cells in the Excel file. It's just not supported in the .csv and it will cause you to have extra records with the wrong lengths and totally screw up everything. You should have separate columns for the land line and cell numbers, and as long as there is nothing else (including whitespace or punctuation) on the line that contains those placeholders you can use the "remove blank lines" feature to remove a line for a missing phone number. This might mean you need to add the text prefix that identifies the type of phone number in Excel. You can do that by adding a few more columns, one for each prefix, and one with a formual for each type of number. The formula should check to see if there is actually a number in the cell and if there is it should concatenate the prefix and number, if not it should leave the cell blank. You then use those columns insted of the actual number columns.
And if that's too complex for you, you can always set up a find/change query that looks for lines that don't end in a digit (you should assign a differnet paragraph style to phone numbers to make it easy to control the scope) and remove them.
@Michael No, the landline and cell were in separate cells. What was happening was there would be a blank line in the InDesign file. Oddly enough I opened up the InDesign doc today and this problem just went away on it's own. This leads me to believe that it might have just been a bug maybe?
@Peter I looked through my spreadsheet and saw some paragraph returns accidentally put in some of the cells that explained some of the problems I was having (this was different then the phone number issue I was talking about earlier). The one remaining problem I am having is that the “created merged” dialog box is not allowing me to post more then one record per page. When I go to the "multiple record layout" section it tells me that "These options are disabled because Single Record is selected for Records per Document Page". But....
(scroll down below this image)
.... when I go to the "records section" it indicates that I have all records selected. Notice that half way down the dialog box the option to change it to anything except single record has been greyed out. Anyone know why this might be happening? I want to have several listings per page but it doesn't seem to be letting to do this.
Might be the document setup. Multiple record merges can only be done on a single-page merge template, and you should have no more than one set of placeholders, whcih should be in the upper left postion. Also make sure there is nothing else on the page that would be duplicated and move off the page in a multiple record merge. The merge is going to run as if everything inside the rectangle that encloses all page elements needs to be duplicated, so stray objects or large frames can casue a problem when there is not enough space.
I attached a screen shot of my layout so that you might have a better idea as to what I am doing wrong. I am not sure what a single page merge template is. I have two pages facing each other since that is how my booklet is layed out. I have one place holder on the left. There is nothing else on the layout (I did a select all on the page before taking the screen shot.)
I also tried deleting the facing page (if that was what you wanted me to do) and I am still having the same problem.
I notice that's page 4. Are there pages 1-3?
A multiple record merge template must contain exactly 1 page. If you set up as facing pages, your merged file will use only the pages that ae on the side of the spine that holds the placeholders. You can fix that by going to Document Setup and unchecking facing pages, close the dialog and reopen, then recheck facing pages, or you can convert the merged doc from a non-facing template to facing pages the same way.
Yes there is pages 1-3 in this document. The booklet has a cover and two inner pages before it gets to the data merge section. When you were talking about a single page merge you meant the entire doc could only have one page? I thought by single page you were just meant to make a template page rather as opposed to several template pages. The guides on the right hand page are different as they are the flipped version of the left hand side, doesn't tht mean I would have to let InDesign know that before doing the data merge? Does InDesign know that it should flip the margins with the page on the right hand side?
Multiple record merges can't handle mirrored layouts directly. If you design carefully, though, you can create a symmetrical layout, or you can make sure everything snaps to guides and use Layout Adjustment to shift objects when you apply a two-page master after the merge. You als will have to add your non-merged pages after the merge.
Data Merge is a very dumb (unsophisticated) module, sedigned for doing things like sheets of address labels. As your project gets more complex you have to start thinking about ways to work around that or consider a commercial plugin.
My project might have a little complexity involved as it is going to involve two data merges. Might it be best to put this project in a book file and have one ID doc for the 1st data merge and then a 2nd doc for the 2nd data merge etc.? The first data merge will be for the members of the group I am writing the booklet for and the next data merge will be for vendors that support the group.
Maybe irrelevant, but I just enter a space where I need a blank field - and InD ignores the space entry when using the csv.
Always worth checking the csv in a text editor too - Excel is notorious for changing code based on hidden formatting.
Now data merge is only posting two people's addresses per page. I tried to look for something that would let me control how many addresses I wanted on a page but that option doesn't appear to be available. How would I go about getting six per page? I set up my margins so that the six sections were the same size as my text blocks. It seems really odd that it is only doing two. Any idea why?
Don't know for sure, but I do see that the page should populate rows first before columns according to your settings, but there's only one record per row which makes me think ID doesn't know there is enough space. Try reducing the size of the right and bottom margins.
Also check to be sure there isn't some other extraneous object that is taking up space, and do the merge without previewing (it's OK to preview as long as you UNDO before you hit the merge button itself).
I tried doing another data merge and I can't seem to get the "remove blank lines" feature to work anymore.
Here is an image that has all the fields entered in:
Yet if a field is left blank in the InDesign doc I will get this:
Or if the blank field comes before another field on the same line I will get this:
This seems odd to me since I thought these problems were supposed to go away when I clicked this:
Do you know what might be going wrong? Here is what my data merge place holders look like if it is any help:
"Looks to me like there are spaces typed between your fields."
Yes, there are. I would like to have spaces between the fields so they don't run together. The problem I have is what if one or two of those fields are blank and on the same line like in the second screen shot? That particular company doesn't have a website but the space still shows up anyways since I typed it in before doing the merge. Is there a way to add the space in the spreadsheet so that it doesn't show up in ID if the field has been left blank? When I experimented with adding a space after entries in the spreadsheet I don't think it worked. Is there some trick to adding spaces at the end of a field in the spreadsheet so I don't have to enter a space in the ID doc so I can get aorund having spaces when then are blank fields on the same line?
"Those are characters, and the lines are not blank."
I am actually having both issues the spaces I added in before the data merge but also blank lines whenever there is a blank field.
Here is the example of a an entry with all the fields entered in:
.....and here is an entry with fields left out (no address, webpage, or email):
It was my understanding that those blank entries should have been ignored when I clicked the "remove blank lines for empty fields" check box. Am I correct to believe that?
This is a bit confusing to users (and it bit me, too, the first time I used Data Merge). Whitespace, or punctuation, you type into a line with a field placeholder renders that line non-blank no matter if all the fields for that record are null or not. Depending on the use case, sometimes you can build the punctuation into the spreadsheet, and sometimes you can create a new field by doing a concatenation of other fields in the spreadsheet to combine |FirstName|LastName| into "FirstName LastName" or "LastName, FirstName" so you only have a placeholder on the line in ID, but that isn't always practical or even possible.
I'm doing a directory right now that suffers from this issue, and I've saved a group of find/change queries that I run after data merge to remove things like lines with an "email: " tag but no merged address or lines that have nothing but spaces or punctuation in them.
I don't see a line in your sample that has punctuation, but you have two, or perhaps three, cases for white space that needs to go.
Find ^\s+\r and replace with nothing will remove the "blank" lines
Find ^\s+ and replace with nothing will remove whitespace at the beginning of a paragraph
Find \s+$ and replace with nothing will remove blank space at the end.
I couldn't get the find change operations to work so I don't know if I am doing something wrong. I get the message "search is complete. 0 replacement(s) made."
I might just go with changing the my spreadsheet so that I have multiple items in a cell. My layout is simple enough and the number of entries are few enough that I don't think that should be a problem. I suppose the main issue you have when doing it that way is that have to consider how you will format the text; if one field needs a special paragraph or character style then I will have to be sure to leave that in another field.
I couldn't get the find change operations to work so I don't know if I am doing something wrong. I get the message "search is complete. 0 replacement(s) made."
When that happens to me it's usally because I entered the GREP code into the plain text search tab.
Oh, I didn't know it was GREP code. I have hardly used grep in the past, maybe once in a book project. The problem with the code is that it also removes the lines that I want in there. Some of the similiar content I would like to group together and seperate by spaces so I don't think the code will work in this instance. I guess I will just stick with redoing the spreadsheet and keeping the entries with different styles in seperate cells.
Jongware might have a clever way of not removing deliberate blank paragraphs, but the real answer, I think is that you shouldn't be using them. If you want space between groups you should be using either separate frames or space before to push the first paragraph in the group down (presuming that one will never be blank) or space after on the last paragraph in a group if it will never be blank.
I've never seen that, so I think, yes, it's cause for concern. Do you see a problem anywhere with the merge? Do you, perhaps, have some sort of master page object in your template that you overrode onto the document page?
I haven't looked closely at what changes might have been made to Data Merge recently, but back in CS4 when it worked fairly well (i.e the preview wasn't broken) when doing a multiple-record-per page merge you would want either to put your merge fields on the document page, and use a blank master, or put them on teh master page and group them with anything else that needed to be duplicated with them. If they were on the master page other master objects would be ignored unless grouped with the field placeholder frames.
Putting the fields on the master page is supposed to allow you to update the merged file if the data source changes. I've not found that to be reliable, especially with photos, nor particularly necessary. Most merges are so fast it's not a big deal to simply run the merge again if the data changes, so I mostly use the document page for setup and apply the None master to the template, then apply the real master to the pages inthe merged document.
Peter, it sounds like you do data merges quite often. I was wondering do you generally use use the built in data merge tools or do you rely on a 3rd party plug-in? Do you know of a plug in that will give me more control over my layout? I have found that I will do a lot of editing after I do a datamerge that just ends up getting overwritten the next time I do the next merge.
I have two projects that I merge each year, and I use the built-in feature. I've never used any of the third-party catalog plugins, which I presume would give you more flexibility. XML might be another option, but I know nothing at all about XML workflows
What sort of editing are you doing? My larger project is a directory of around 200 names, and all of the formatting is built into the template for that. I need to run a couple of find/change operations to remove lines that are nothing but punctuation or white space as described above, and I manually stitch together all of the records into a single story, but that can be handled by the Text Stitch script from Rorohiko.com if you prefer. I rerun the the merge each year rather than trying to update.
My project is currently very small, fortantly, but I don't know how long it will stay that way or if future projects will be more legthy. After doing the two data merges I would copy the info over to a new indesign doc because I found that master page didn't work in the data merge file. I also would cut and paste the text from one of the data merges into a single text block so that I could have more control over leading of the text. I realize that the data merge offers control for spacing between text blocks but since each text block has a different number of lines in it (some fields in the spreadsheet are blank for certain entires) the spacing between each text block comes out different so I found it's better to move it all in one text block. What this means, of course, is when I do the next data merge I have to do this work all over again. Currently this is managable but it might not be in the future.
That's waht I meant about threading the the records (I work from the back to the front and click the inport of the last record, then inside the next to last, and so forth). I think the threading is faster than cut/paste, and as I mentiuoned Rorohiko has a script that will do it for you. Once it's threaded into one story, I find you can do pretty much anything you want -- export as text (RTF or Tagged Text) or copy/paste. I do the latter for my directory to get a running thread from a single-record per page merge that I use to make an alphabetical TOC showing the listing numbers (my names are styled as a numbered list, so the numbers remain the same after they flow together, but there's no easy way to generate the TOC unless I have one record per page. I copy that story into my new directory file, too).