8 Replies Latest reply on Mar 15, 2017 6:43 AM by Jeffrey_Smith

    Data Merge creating blank pages for empty spreadsheet rows

    amyd78996727 Level 1

      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!

        • 1. Re: Data Merge creating blank pages for empty spreadsheet rows
          vinny38 Level 4

          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.

          1 person found this helpful
          • 2. Re: Data Merge creating blank pages for empty spreadsheet rows
            amyd78996727 Level 1

            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…

            Screen Shot 2017-03-15 at 13.10.20.png

             

            This is the pre-merge INDD document…

            Screen Shot 2017-03-15 at 13.05.40.png

             

            This is page one of the merged INDD document…

            Screen Shot 2017-03-15 at 13.07.48.png

             

            This is page two of the merged INDD document…

            Screen Shot 2017-03-15 at 13.09.17.png

             

            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?

            • 3. Re: Data Merge creating blank pages for empty spreadsheet rows
              MW Design Level 4

              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

              1 person found this helpful
              • 4. Re: Data Merge creating blank pages for empty spreadsheet rows
                amyd78996727 Level 1

                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!

                • 5. Re: Data Merge creating blank pages for empty spreadsheet rows
                  MW Design Level 4

                  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

                  2 people found this helpful
                  • 6. Re: Data Merge creating blank pages for empty spreadsheet rows
                    amyd78996727 Level 1

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

                    • 7. Re: Data Merge creating blank pages for empty spreadsheet rows
                      Laubender Adobe Community Professional & MVP

                      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

                      1 person found this helpful
                      • 8. Re: Data Merge creating blank pages for empty spreadsheet rows
                        Jeffrey_Smith Most Valuable Participant

                        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.