Fixing up Dozens of Tables after Excel Import
I recently had a job involving importing dozens of Excel tables into InDesign in order to to create a document.
While the Excel files did come into InDesign properly and appeared as formatted tables, there were very many empty rows and colums that needed to be deleted.
And that, of course, called for a script!
So the first question is: How to detect whether a table row or column is empty in InDesign?
Detecting Empty Rows or Columns in an InDesign Table with Scripting
I started like this:
Given a table row r, we can write:
c = r.cells.everyItem().contents.join("");
and then test whether c is empty:
if (c === "");
And of course, we can do the same thing with columns as well.
However, it quickly became clear that this is an inadequate test. Why? Because a table cell in InDesign can be overset. If a cell is overset, its contents property returns an empty string. What this means is that rows and columns that are not really empty will be deleted. Whoops!
Overset Text Frames versus Overset Cells
To fix this, we have to find a different property to test. Initially (not having scripted tables for a while), I though that a cell has a Story property, just like an InDesign text frame.
If a TextFrame is completely overset, that is, there are no visible characters in a text frame because they are all overset, then checking myTextFrame.contents will return an empty string, just like an overset table cell. But checking myTextFrame.parentStory will return the Story object that the TextFrame is part of, and that can be checked to see if its empty.
However, cells do not have a Story property. Instead, the have a Text property. Given myCell being an InDesign table cell, you can check
If that is an empty string, you can be sure that the cell is empty and that there is not even any overset, invisible text attached to it.
The Efficient Solution: Collections
The next question then is how to test quickly whether all the cells in a row or column are empty.
The simple, straightforward solution is to loop through all cells in the row or column (and I’ve seen some scripts floating about on the Interwebs that do this). But this is very slow, and unnecessary.
This will get us an array of all the texts in all the cells in the row or column. We can use join() to join all of this, to make it easier to test whether the array is in fact empty:
myRow.cells.everyItem().texts.everyItem().contents.join("") === ""
If the above line returns True, we know the row is empty. And I think that is a pretty failsafe, and quick, method to test for an empty row or column in an InDesign table.
With this info, you should be able to loop through all rows and/or columns of all or some tables in an ID document, test whether they’re empty, and if so, remove() them!
There are one or two things to be aware of though: If you remove a column or a row, this may throw your reference to subsequent rows or columns off, so, as always, it’s best to work backwards.
Another danger is that by deleting a row or column, you delete the entire table (because some tables have only a single row or column). In such a case, this can throw references to further tables out of sync as well. Work backwards here too to avoid any problems.
I hope that armed with this information, you will find it an easy matter to write a little script to delete all empty rows and columns from your InDesign tables.
However, I am also making available below the final script I wrote, which deletes empty rows and columns for all or some tables in the document, and does a number of other useful things as well. I threw in a dialog to make it easier to check or uncheck which things the script should do (depending on the project). If you’re interested in the final script I came up with, read on.
More Table Cleanup Tasks
As I continued with the project I was working on, there were a number of other things I wanted to do to the tables I had imported from Excel:
- Many of the tables came in with merged cells. I needed to unmerge all of these.
- I wanted all the rows in all the tables to expand in height (auto-grow) to accommodate their contents. For some reason, they came in with their respective heights set to “exact”.
- Finally, I wanted to set a standard width to all the tables.
A Ready-made Script for Instant Download
So, as mentioned, I threw together a script that does all or some these things, including deleting all rows and/or columns – with all options accessible via a simple options dialog.It also lets me choose whether to work on all tables in the document, or only the tables I select before running the script.
Here’s a screenshot of the UI window:
If, rather than trying to write a script to delete rows and columns yourself, you would like to use the one I wrote for myself, it is available for instant download for only $9.95. I found it to be a useful addition to my arsenal of InDesign scripts, and I think you will too!
The script works with all the latest versions of InDesign including CS6 and CC2015. (In fact, there is nothing in the script I’m aware of to prevent it from working in earlier versions of InDesign either.)