29 Replies Latest reply on Nov 19, 2015 8:01 AM by libbys78733934

    Delete entire row if any cell is blank

    StevieJ147

      Hi all

       

      I have found this script and it works perfectly for its purposes (if an entire row has no data/length, then remove it).

       

      var myDocument = app.activeDocument;

      for(var i=myDocument.textFrames.length-1; i>=0; i--){

          for(var j=myDocument.textFrames[i].tables.length-1; j>=0; j--){

              for(var k=myDocument.textFrames[i].tables[j].rows.length-1; k>=0; k--){

                  myContents = 0;

                  for(var l=myDocument.textFrames[i].tables[j].rows[k].cells.length-1; l>=0; l--){

                      if (myDocument.textFrames[i].tables[j].rows[k].cells[l].contents != "") myContents++;

                      }

                  if (myContents == 0) myDocument.textFrames[i].tables[j].rows[k].remove();

                  }

              }

          }

       

      However, in the first column of my table, I have some text, and in the second column, I have no data.

       

      How do I delete an entire row (including the first column containing text) using a script if just one cell of the row is blank?

      I like the above script as I don't have to enter a table to run it - i.e. it will just apply to the whole document as I have many pages...

       

      Thanks!

        • 1. Re: Delete entire row if any cell is blank
          csm_phil Level 4

          Hi steviej147,

           

          Try the below codes, I hope it will helpful for you.

           

          var myDoc = app.activeDocument;
          var myStories = myDoc.stories.everyItem();
          var myTables = myStories.tables.everyItem().getElements();
          
          //--------------Below lines are delete the row, 
          for(i=0; i<myTables.length;i++){
              var myRows = myTables[i].rows;
              for(r=myRows.length-1; r>=0; r--){
                  if(myRows[r].cells.everyItem().texts.everyItem().contents.join("") ==""){
                      myRows[r].remove();
                      }
                  }
              }
          
          //------------------Below lines are delte the column
          for(i=0; i<myTables.length;i++){
              var myRows = myTables[i].columns;
              for(r=myRows.length-1; r>=0; r--){
                  if(myRows[r].cells.everyItem().texts.everyItem().contents.join("") ==""){
                      myRows[r].remove();
                      }
                  }
              }
          
          

           

           

          thx,

          csm_phil

          • 2. Re: Delete entire row if any cell is blank
            Chinnadk Level 4

            Hi,

             

            Try this.

             

            var doc = app.activeDocument,
                _tables = doc.stories.everyItem().tables.everyItem().getElements();
            
            for(var i =0;i<_tables.length;i++)
            {
                    var _rows = _tables[i].rows;
                    for(var j =0;j<_rows.length;j++)
                    {
                            var _cells = _rows[j].cells;
                            for(var k =0;k<_cells.length;k++)
                            {
                                //alert(_cells[k])
                                    if(_cells[k].contents == "")
                                    {
                                            _rows[j].remove();
                                        }
                                }
                        }
                }
            

             

            Regards,

            Chinna

            • 3. Re: Delete entire row if any cell is blank
              StevieJ147 Level 1

              Hi!

               

              Thanks for the fast response.

               

              This works but only seems to delete completely blank rows.

               

              After looking at my question, I think my explanation wasn't clear to begin with. An example would be:

               

              Existing Table:

              Column 1     Column 2

              Edgeyard      Permitted

              Courtyard     Permitted

              Sideyard       Not Permitted

              Readyear   

              No Yard   

               

              Proposed Table:

              Column 1     Column 2

              Edgeyard         Permitted

              Courtyard         Permitted

              Sideyard           Not Permitted

               

               

               

              Thanks again!!

              Steve

              • 4. Re: Delete entire row if any cell is blank
                StevieJ147 Level 1

                Hi Chinna

                 

                This sort of work, but for some reason, the last two rows won't delete? and I get an error message as well

                • 5. Re: Delete entire row if any cell is blank
                  Sajeev Sridharan Level 4

                  Chinna code working fine, just add try-catch in your code,

                   

                  var doc = app.activeDocument, 

                      _tables = doc.stories.everyItem().tables.everyItem().getElements(); 

                   

                  for(var i =0;i<_tables.length;i++) 

                          var _rows = _tables[i].rows; 

                          for(var j =0;j<_rows.length;j++) 

                          { 

                                  var _cells = _rows[j].cells; 

                                  for(var k =0;k<_cells.length;k++) 

                                  { 

                                      //alert(_cells[k]) 

                                      try{

                                          if(_cells[k].contents == "") 

                                          { 

                                                  _rows[j].remove(); 

                                              } 

                                          }catch(e){}

                                      } 

                              } 

                      } 

                   

                  Vandy

                  • 6. Re: Delete entire row if any cell is blank
                    Jump_Over Level 5

                    Hi Chinna,

                     

                    One has to watch if the loop result increases or decreases a loop counter.

                    You need to use backward steps (i--) in this case ==> otherwise a code sometimes works - sometimes ends with error.

                     

                    In current subject this problem shows according to last rows of modified table.

                     

                    Jarek

                    • 7. Re: Re: Delete entire row if any cell is blank
                      Kai Rübsamen Adobe Community Professional

                      Hi,

                       

                      I would think, that this can be done with one loop, instead of three:

                       

                      var doc = app.activeDocument,
                        _cells = doc.stories.everyItem().tables.everyItem().cells.everyItem().getElements();
                      
                      for ( var k = _cells.length-1; k>= 0; k-- ) {
                        if ( _cells[k].contents == "" )  _cells[k].rows[0].remove();
                      }
                      
                      
                      
                      

                       

                      – Kai

                      • 8. Re: Delete entire row if any cell is blank
                        Laubender Adobe Community Professional & MVP

                        @Jarek – even that will not prevent all errors, if we have a situation like that:

                         

                        TwoColumnTable.png

                         

                        Immediately removing the row will prevent the loop to go on, because the cell you want to test next is already removed…

                        Looping back to forth within the index of the rows will help. But immediately removing the rows after detecting an empty cell will not.

                         

                        And another question: should the whole table be removed, if ALL rows in a table qualify for removing?
                        If yes, then looping back to forth on table level is also recommended.

                         

                        Try/Catch on cell level would do the job then.
                        (But I feel a little dirty using this). Better would be detecting all rows to remove first, then decide:

                        1. Should the whole table be removed (all rows qualify for removing)

                        2. If not, remove the individual rows from highest index number to lowest index number

                         

                        Uwe

                        • 9. Re: Delete entire row if any cell is blank
                          Laubender Adobe Community Professional & MVP

                          @Kai – your script will fail with the situation I presented in answer #8.
                          And that would be a scenario you never can rule out…

                           

                          Uwe

                          • 10. Re: Re: Delete entire row if any cell is blank
                            Laubender Adobe Community Professional & MVP

                            @Kai – However a try/catch will do it:

                             

                            var doc = app.activeDocument, 
                            _cells = doc.stories.everyItem().tables.everyItem().cells.everyItem().getElements(); 
                            
                            for ( var k = _cells.length-1; k>= 0; k-- ) {
                                try{
                                if ( _cells[k].contents == "" )  _cells[k].rows[0].remove();
                                }catch(e){};
                               
                                }
                            

                             

                             

                            Uwe

                            • 11. Re: Delete entire row if any cell is blank
                              StevieJ147 Level 1

                              It works!

                               

                              Thank you all for your very prompt assistance - you are all legends!!

                               

                              Hopefully looking forward to helping someone else out one day!

                               

                              All the best

                               

                              Steve

                              • 12. Re: Delete entire row if any cell is blank
                                Laubender Adobe Community Professional & MVP

                                @Steve – I suggest you'll give Kai the 10 points.

                                I made just a minor adjustment to his idea…

                                 

                                And:
                                Kai's code will also remove whole tables, if no row will qualify to stay.

                                 

                                Uwe

                                • 13. Re: Re: Delete entire row if any cell is blank
                                  Jump_Over Level 5

                                  ...will prevent the loop to go on, because the cell you want to test next is already removed…

                                   

                                  @Uwe,

                                   

                                  check it pls.

                                  From my side, in case of consecutive empty cells in the same row, after removing the row ==> next cell is taken from the upper row (so is checked twice in fact).

                                   

                                  To make a code work more clear I suggest to check row.contents instead of cell.contents.

                                  Like this:

                                  var
                                    doc = app.activeDocument,
                                    _rows = doc.stories.everyItem().tables.everyItem().rows.everyItem().getElements();
                                  
                                  for ( var k = _rows.length-1; k>= 0; k-- ) {
                                    curRowCont = "," + _rows[k].contents + ",";
                                    if( curRowCont.search(",,") != -1 ) 
                                    _rows[k].remove();
                                    }
                                  
                                  

                                   

                                  Jarek

                                  • 14. Re: Delete entire row if any cell is blank
                                    StevieJ147 Level 1

                                    Legend!

                                     

                                    Thanks so much!

                                    • 15. Re: Delete entire row if any cell is blank
                                      Laubender Adobe Community Professional & MVP

                                      @Jarek – yes. That does it. Clever…
                                      Uwe

                                      • 16. Re: Delete entire row if any cell is blank
                                        Laubender Adobe Community Professional & MVP

                                        But with one exception: if the separator character "," is used two or more times in a row inside the contents (could be a typo on the user's side), that row will be removed as well.
                                        A different separator character like "§" will not qualify, because the individual cell contents of a whole row will always be separated with a "," if we stringify the row contents by adding a string.


                                        Uwe

                                        • 17. Re: Delete entire row if any cell is blank
                                          Jump_Over Level 5

                                          Hi Uwe,

                                           

                                          Right of course.

                                          That's why I am repeating al the time:

                                          One can be given with a way but exact solution need him to be awake what's going on inside a code.

                                           

                                          This is a help forum!  ( most helpful I met, by the way )

                                           

                                          Jarek

                                          • 18. Re: Delete entire row if any cell is blank
                                            libbys78733934

                                            Hi all,

                                             

                                            I have a similar problem as Stevie, however having tried all the scripts provided here I can't seem to resolve this.
                                            I am using data merge within indesign to create multiple (8,000+) documents as below... However if the right hand table cell has no data (e.g. Shift Payments) I want the whole row to be deleted. Like I said, I have tried running all the scripts provided here and none have worked for me so far... any ideas? I wondered if it was to do with the data merge leaving a hidden character in the cell?

                                             

                                            Thanks,

                                            Libby
                                            Screen Shot 2015-11-18 at 16.40.20.png

                                            • 19. Re: Delete entire row if any cell is blank
                                              Jump_Over Level 5

                                              Hi,

                                               

                                              I am pretty sure there is a hidden character left.

                                              Solution could be to test contents with some RegEp, like:

                                              var 
                                                doc = app.activeDocument, 
                                                _colls = doc.stories.everyItem().tables.everyItem().columns[-1].getElements(), cCol,
                                                _cells, cCell, k,
                                                checkWhiteRegEx = new RegExp ("\\S"); 
                                              
                                              while (cCol = _colls.pop() ) {
                                                _cells = cCol.cells.everyItem().getElements();
                                                while (cCell = _cells.pop()) {
                                                     if ( !checkWhiteRegEx.test(cCell.contents) )
                                                          cCell.parentRow.remove(); 
                                                     }
                                                }
                                              

                                               

                                              Notice:

                                                   RegEx is to test if contents match any of "not white space" character.

                                                   If RegEx.test() fails with last cells contents ==> row is removed

                                               

                                              Jarek

                                              • 20. Re: Delete entire row if any cell is blank
                                                libbys78733934 Level 1

                                                Hi Jarek,

                                                 

                                                Thanks for your reply, unfortunately the script didn't work, It did work on a test row that I created with text in the left hand cell and an empty right hand cell, so I think it is definitely this hidden character left over from the data merge which is causing the problem. Any other suggestions most welcome...

                                                 

                                                Thanks,

                                                Libby

                                                • 21. Re: Delete entire row if any cell is blank
                                                  Jump_Over Level 5

                                                  More info about those cells contents would most welcome as well.

                                                   

                                                  Can you tell any about its length? Unicode?

                                                   

                                                  Jarek

                                                  • 22. Re: Delete entire row if any cell is blank
                                                    Laubender Adobe Community Professional & MVP

                                                    Hi Libby,

                                                    there is a lengthy discussion about special characters left over by datamerge.

                                                    See the details here:

                                                     

                                                    Re: Multiple record data merge into paragraph styles-applies the wrong style

                                                     

                                                    Do a TEXT search/replace to get rid of the special characters.

                                                    Search for (without the quotes): "<FEFF>"

                                                     

                                                    Steps 2 and 3 might apply in your case.

                                                     

                                                    Uwe

                                                    • 23. Re: Delete entire row if any cell is blank
                                                      libbys78733934 Level 1

                                                      Hi Jared,

                                                       

                                                      Turning on hidden characters in Indesign reveals this (in screenshot) if I copy that in to text FIND in InDesign it appears as ^I^I^I^I (I typed this out as copying here didn't show up – don't know if that makes any difference) replacing this to nothing creates 0 replacements and the script still does not work

                                                       

                                                      Hi Uwe, FIND and replace <FEFF> to nothing creates 74 replacements, the script still doesn't work. The next step using a GREP search for ^\s+ reveals nothing. Script still not working.

                                                       

                                                       

                                                      Screen Shot 2015-11-19 at 08.57.34.png

                                                      • 24. Re: Delete entire row if any cell is blank
                                                        libbys78733934 Level 1

                                                        Looking in the info panel I think the unicode is: 0xFEFF 0xFEFF 0xFEFF 0xFEFF

                                                         

                                                        Is this any help?

                                                        • 25. Re: Delete entire row if any cell is blank
                                                          libbys78733934 Level 1

                                                          Another update – If I open the structure pane in Indesign I can manually untag elements from frames (I have to do this twice, once for top table then again for second table) then if I run a FIND search for <FEFF> replacing to nothing (54 replacements) the script works.

                                                           

                                                          Is there any way of running a script that will do all of this? Doing this manually for each 8,000 documents produced would be a nightmare.

                                                           

                                                          Thanks for you help.

                                                          Libby

                                                          • 26. Re: Delete entire row if any cell is blank
                                                            Laubender Adobe Community Professional & MVP

                                                            You will find solutions, if you follow my link in my answer above.
                                                            EDIT: Oops. You mentioned 8,000 documents…

                                                             

                                                            Uwe

                                                            • 27. Re: Delete entire row if any cell is blank
                                                              Laubender Adobe Community Professional & MVP

                                                              Peter Kahrel has a script that can batch process InDesign files.

                                                              And you can run a script along with this batch run.

                                                               

                                                              Uwe

                                                              • 28. Re: Delete entire row if any cell is blank
                                                                libbys78733934 Level 1

                                                                Thanks Uwe – I'll have a look at that.

                                                                • 29. Re: Delete entire row if any cell is blank
                                                                  libbys78733934 Level 1

                                                                  Thanks again Jared and Uwe for all your help. I have ended up with the below script that removes tags, removes hidden characters and then deletes rows from a table which have an empty data cell. I am looking in to batch processing now.

                                                                   

                                                                  app.documents.everyItem().xmlElements.item(0).xmlElements.everyItem().untag();

                                                                  var aDoc = app.documents[0];   

                                                                  var allTables = aDoc.stories.everyItem().tables.everyItem().getElements();  

                                                                   

                                                                  app.findTextPreferences = app.changeTextPreferences = null;  

                                                                   

                                                                  app.findTextPreferences.findWhat = "<FEFF>";  

                                                                  app.changeTextPreferences.changeTo = "";  

                                                                  aDoc.stories.everyItem().tables.everyItem().changeText();  

                                                                  app.findTextPreferences = app.changeTextPreferences = null;  

                                                                  alert("done");

                                                                  var

                                                                    doc = app.activeDocument,

                                                                    _colls = doc.stories.everyItem().tables.everyItem().columns[-1].getElements(), cCol,

                                                                    _cells, cCell, k,

                                                                    checkWhiteRegEx = new RegExp ("\\S");

                                                                   

                                                                   

                                                                  while (cCol = _colls.pop() ) {

                                                                    _cells = cCol.cells.everyItem().getElements();

                                                                    while (cCell = _cells.pop()) {

                                                                         if ( !checkWhiteRegEx.test(cCell.contents) )

                                                                              cCell.parentRow.remove();

                                                                         }

                                                                    }