Data Merge creating blank pages for empty spreadsheet rows

[amyd78996727]

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!

[vinny38]

amyd78996727  wrote

I have been told to remove the blank in Excel before merging by 'Go To / Special / Blank cells' to remove the blank cells

What 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,Country
A Brown,1 Two Street,London,,UK
B Brown,1 Three Street,Brisbane,Queensland,Australia
C Brown,1 Four Street,New York,New York,USA

Question: 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.

[amyd78996727]

Thanks for your reply Vinny! I will attach screenshots below. You will see in the Indd docs that the blank rows are creating a blank page.

This is the CSV file I'm using…

This is the pre-merge INDD document…

This is page one of the merged INDD document…

This is page two of the merged INDD document…

Any suggestions on how I can avoid the creation of the blank pages. Like I said in my original post, i know selecting all blank cells and deleting them is an option however in the case that the addressee doesn't have a suburb that cell will be deleted moving everything under it up, therefore the rows are being confused. Does that make sense?

[MW Design]

Looks like you have blank rows, and some rows have blank cells. True?

Do one of the columns always have entries in the cells? Just do a sort, it'll move the rows in their entirety without the drawbaks of the Blanks command.

Mike

[amyd78996727]

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!

[MW Design · Accepted solution]

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? - ...

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

[amyd78996727]

Amazing! Thank you Mike. That will do the job just fine!

[Laubender]

Hi Mike,

thank you for providing the tips on condensing Excel files with empty rows.

FWIW the remants one can see in the screenshot of page two are special characters.

Unicode FEFF

They are "substitutes" for the empty cells in the data source. In my opinion a bug with InDesign. Since the day datamerge was introduced. One FEFF character for every placeholder that links to an empty data cell.

One could search for FEFF with TEXT Find/Replace like that:

<FEFF>

You cannot do it with GREP's \x{FEFF} . That would find nothing.

Be aware, that FEFF is not exclusive to datamerge.

So do not use TEXT find/replace globally if you do not want to touch:

XML Markers
Index Markers
Notes Markers

See also this here where the OP observed FEFF characters but thought they would be text anchors (they were not):

Re: Trying to remove mass text anchors

Regards,
Uwe

[Jeffrey_Smith]

There are commas in your data, using a csv file, the content following the comma will shift to the next column. Use a tab delimited .txt instead.

[innovative_adventurer5D56]

Hi.

I have the same issu but i am sure that my excel does not have an empty rows..

The meged document gives me a page with merged information and the next is empty.. Between 2 merged information an empty page!

[Laubender]

Hi,

did you check, if this page is really empty?
No text frame at all?

And if a text frame is there did you check that perhaps some special characters are in it?

Turn on "Show Invisible Text" and look for FEFF special characters like Amy is shwoing in the third screenshot of reply #2.

Regards,
Uwe

[innovative_adventurer5D56]

i am using NUMBERS not excel

[innovative_adventurer5D56]

Screenshot from the CSV file opened on textEdit, notice the highlighted rows, " with space after, i can't find them on the excel sheet, or numbers! how can i remove them from numbers or excel??

[Laubender]

Hi Manal,

hm…

All in all I think it would be best to do a text file with tab separated entries saved to Unicode (UTF-16).

I don't think that the marked lines are showing "empty lines".

Don't know why, but it seems that the file path for the psd-files should be surrounded by quotes. But this did not work as intended.

If I read this data source from Right to Left I cannot see that the order of field titles is ok. Or the order of the data columns is not ok, if the order of the field titles is right.

Regards,
Uwe

[innovative_adventurer5D56]

the file from right to left because it has arabic text.

again here is screenshot for a dummy text in english, csv file exported from numbers, Unicode (UTF-16).

i have " after every entry.

and here is the original sheet

the merged document are correct with merged data but i have after every page, an empty page, when i remove that " from Csv file, the empty pages doesn't appear in the merged document.

[innovative_adventurer5D56]

i tried the image to be JPG, and it surrounded by quote also,

when return the " to be at the end of the file path, the empty page in the merged document doesn't appear again.

but i have too many entries in the sheet that we want to make the data merge, we don't need that quotes!

[Laubender]

Hi,

you are right. You will not need that quotes at all.

Isn't that a perfect job for a TEXT or GREP Find/Replace action with a text editor?

You could load the text with e.g. TextWrangler app and replace the quotes with nothing there. It would even work with the Find/Replace function of TextEdit. I'm not sure about the : in the file Mac OS X file paths, maybe you have to replace them as well with slashes.

Regards,
Uwe

[innovative_adventurer5D56]

Yes i did replace the " with nothing and its okay, the issue that i do a training for an organization and we need to know why that happened, from where the quote come from ?

thank you Laubender

[Laubender]

https://forums.adobe.com/people/manal+shanableh  wrote

…we need to know why that happened, from where the quote come from ?

I don't know. Who is doing the data files? They should know.

Regards,
Uwe