Data Merge creating blank pages for empty spreadsheet rows

Question

I am trying to create envelopes for my stationery business. My client's provide their guests' address details in a spreadsheet which almost always contains blank cells, mainly in the State column where guests live in countries without states such as the UK and Europe. I have been told to remove the blank in Excel before merging by 'Go To / Special / Blank cells' to remove the blank cells however this causes problems as it moves all of the following information up and not keeping rows together. For example… (where / is new column)

Name / Street Address / City / State / Country

A Brown / 1 Two Street / London / BLANK / United Kingdom

B Brown / 1 Three Street / Brisbane / Queensland / United Kingdom

C Brown / 1 Four Street / New York / New York / United Kingdom

Becomes…

Name / Street Address / City / State / Country

A Brown / 1 Two Street / London / Queensland / United Kingdom

B Brown / 1 Three Street / Brisbane / New York / United Kingdom

C Brown / 1 Four Street / New York / BLANK / United Kingdom

How can I get Indesign to ignore those blank rows entirely so that I don't have to mess up the Excel file? I have the 'Remove Blank Lines for empty fields' option ticked however Indesign creates a blank page for every row that is blank in Excel.

Please help! My frustration levels are reaching unknown heights!

MW Design · Accepted Answer

Hi Mike, yes that's right. There are blank rows and sometimes blank cells where addresses don't have suburbs etc. The problem with sorting so that the blank rows are at the bottom of the spreadsheet is it makes the merged envelopes difficult for clients to check as they're the entries are now in a completely different order than what they were supplied. But it is a work around I have done in the past!

Oh, well if the data has to remain in its present order, there are alternatives.

One involves adding a couple columns as first and second columns. Then filling the first column with consecutive numbers. In the second column you test for data to the column on its immediate right using a formula.

Another method is to select all the data, hit the Filter button. Now to the right of the Filter button (depends on version I suppose), there is a remove duplicates. Once that Remove Duplicates button is clicked, there should remain only one blank row and you can select it and delete it. This works fine if there is not data that truly repeats for a reason.

I just searched, and here is one post about using the formula as well:

macos - How do I delete empty rows in excel but not empty cells in rows with information in them? - Ask Different

I have used both methods and the sorting. When I have to keep pre-sorted mailing address, for instance, I generally just use the second method above.

Mike

vinny38 · Answer

amyd78996727 wroteI have been told to remove the blank in Excel before merging by 'Go To / Special / Blank cells' to remove the blank cellsWhat do you mean by removing blank cells? No need to remove anything, just leave them empty.Open your CSV file in a text editor. It should look like that: (or it could be TXT file with tabs instead of commas)Name,Street Address,City,State,CountryA Brown,1 Two Street,London,,UKB Brown,1 Three Street,Brisbane,Queensland,AustraliaC Brown,1 Four Street,New York,New York,USAQuestion: in your INDD pre-merge document, is the State field placed alone on a single line? In this case, no problemo.Or is it placed "inline" with other fields, with a separator. In this case, please provide screenshot of your indd document, with invisible characters ON, and data merge preview OFF.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded