A routine task of any prepress operator is the typesetting or formatting of business cards. If there are a lot of cards, a usual request will be made to the client to supply the data in a spreadsheet or table, in order to take advantage of InDesign’s Data Merge feature.

In a perfect world, this is what we would expect to see:

The data has a header row that is descriptive and in a logical order. All data follows the same formatting (e.g. the phone numbers have the same structure) and is ready to be exported as either a txt or csv for use with InDesign’s Data Merge feature.

Unfortunately, this is what we’re more likely to end up with:

The data is in a word file. While the client has supplied the data in tables, they haven’t really understood the assignment – we wanted decent structure so that the data could be exported to a format we can use with Data Merge. Instead, the data is in a table, but each table cell has the content of what needs to be on each business card. The formatting is also inconsistent in terms of line returns for each record.

Usual way of handling this

So, depending on the amount of cards that require setting and formatting, there are a couple of ways that prepress operators usually deal with this:

• If there’s not many cards, just cut each record out of the word file, open the indesign template, paste the record in, and use the cut and paste without formatting feature to paste the new records over an existing card. Save the file with a new name, and repeat the process.
• If we still want to use InDesign’s Data Merge, we can make a new excel file with the appropriate header row, and then cut and paste the data from the word file into the excel file. This is fine if there aren’t many cards, but…
• If there are lots of cards and we want to take advantage of InDesign’s Data Merge, it may be a case of going back to the client and informing them of what we want… perhaps even supply them an excel file with a few sample records to get them started… provided they don’t get annoyed OR call your sales rep who will get annoyed instead.

Existing scripts to the rescue… the long way

Having faced this same situation repeatedly, it was about time to use scripts to fix this annoying issue.

Unfortunately, there isn’t just one script that can massage this data into a format that’s required. There are lots of solutions that – when combined – can get us there… but it gets to a point where cutting and pasting feels like a faster option.

To illustrate this point, I’ll demonstrate how to massage this data using many scripts… but if you’re reading this just wanting the solution and not the back-story, scroll further down until you see the heading “one script solution“.

First thing to do is place the document into InDesign and get rid of the text at the top so that we’re just left with the table.

The odd line breaks can be fixed with a script that ships with InDesign called Findandreplacebylist.jsx. Let’s run that script.

OK, so most random returns are gone with the exception of the returns at the fist line… but that’s OK so long as they’re all consistent. Next thing to do is to convert all tables in this document back to text. Luckily there is a one-line command that will do this that can be saved as a jsx file and run as a script. The code is here:

app.documents[0].stories.everyItem().tables.everyItem().convertToText("\t","\r") ;

That has now put the data into one long text column.

We can then run the findchangebylist.jsx script again to get rid of more blank returns.

We can then highlight what will be the first row of data and then go to the table menu, and go convert text to table.

In the new dropdown, let’s just use the defaults of tab for column separators and paragraph for row separators.

If we click OK, we get a table, but this isn’t what  we want.

What we want is for the data to be going across into columns. The technique to doing this is to put a tab where we want to end one row and begin another.

So I undo the last commands so I’m back to when I ran the last findchangebylist. I then go to each phone number and add a tab after it to indicate that this is the end of the records for this card and to start the data on the next line.

From here, I can highlight the entire selection this time and to the table menu, and go convert text to table once again.

This time, I’m going to make the column separators paragraphs, and for row separators I’m going to use tabs.

I then click OK and we now have a table… kind of.

No worries, scripting to the rescue again. I can use a script from Luis Corullon to handle this situation – delete empty columns and delete empty rows.

From here, I now have a table that I can select, copy, and paste and match formatting into Excel where I can then add the header row.

That didn’t really save any time though

While the data that was parsed was a lot cleaner, that was a lot of effort! Not only that, remembering all of these steps will take a lot of practice, and only worth it for data supplied that has dozens of records.

It would be a lot better if it was all in once script.

The whole thing again, BUT a one script solution

I completely agree, so I have cobbled together all of the solutions into one script that you can download here so that we can go from this:

To this:

Can I change other things along the way?

Yes. This script can be opened in a text editor so that custom changes can be made.

For example, the data that has been presented in the add2 field really should be 3 fields – suburb, state and postcode. I’ll revert what I’ve done to make this fix.

No worries, by right clicking on the script in the scripts panel and selecting ‘Edit Script’ I can then edit this script in Visual Studio Code, BBedit, or text editor of my choice.

I’ve put an indicator on line 130 to indicate where to put find/change GREPs. From here, I can add this block of code:

app.findGrepPreferences=app.changeGrepPreferences=null;

app.findGrepPreferences.findWhat="";

app.changeGrepPreferences.changeTo="";

app.activeDocument.changeGrep();

For non scripters, here is a crash-course of what’s happening. Let’s look at the first line:

app.findGrepPreferences=app.changeGrepPreferences=null;

This command tells the GREP search to clear any previous searches.

app.findGrepPreferences.findWhat="";

Within the inverted commas is where we put the GREP pattern of what we want to change.

app.changeGrepPreferences.changeTo="";

Within the inverted commas is where we put the GREP pattern of what we want to change the text to.

app.activeDocument.changeGrep();

This runs the GREP search

In this particular instance, I want to replace a space, the letters SA and another space with a tab, the letters SA and another tab. Here is the code I use:

app.findGrepPreferences.findWhat="\\p{Zs}SA\\p{Zs}";

app.changeGrepPreferences.changeTo="\\tSA\\t";

I then save this as a new script so that I’m not overwriting the old script. Back in InDesign, I then run the new script and here is the new result:

Thoughts?

So this is now a new tool in my arsenal to quickly massage text intended for data merge when the client didn’t quite do what I needed.

That said, if you have any methods of your own you’d like to share, please put them in the comments below.