In my earlier blog “merge merge merge…” I made mention that Data Merge just populates a layout with data from a database: doesn’t understand the data; nor does Data Merge adjust the casing of the text.

Having said that, no software will actually understand what the data is, besides knowing the data as an integer, string, set of characters, or a file reference.

But, I did find workarounds to this to make plain old data become a bit more dynamic, and in this post, I’ll reveal how.

Some software such as Microsoft Excel will allow us to do more with the data than InDesign will, such as:

• sort the data (e.g. by postcode, or postcode AND name, etc)
• format numerical data (e.g. turn 48596 into $48,596.00)
• format date data (e.g. 12-10-11 into Wednesday, 12 October 2011)
• format character data (e.g. turn BACON & EGGS into Bacon & Eggs or bacon & eggs)
• add if-else statements (e.g. if data = value, then A, otherwise B,C,D,E,F,G,H [there is a limit of 7 in Excel])

I did touch upon using GREP to change specific text and to fix surnames, but did not elaborate on how this was done. In this post, I will explain how this was done. I’ve prepared a pdf with the working InDesign files, links and databases attached within the PDF so that all can be revealed.

Changing specific phrases:

In the InDesign file which made the PDF, GREP styles were assigned to the heading on the test invoice. A paragraph style has been assigned to the text which uses two GREP styles – one turns the type Green in Myriad (called ok); while the other turns the type Red in Rockwell with a custom underline (called exceeded).

Changing surnames:

In the Excel file, there are three specific names which need to change:

• Mcavaney to McAvaney;
• O’driscoll to O’Driscoll;
• Dixon-bulls to Dixon-Bulls

Excel will allow the changing of type to uppercase, title case or lower case. InDesign will allow the dynamic changing of case to uppercase only. Yes, InDesign will allow the changing of text to upper, lower, title and sentence case when selected, but not part of a character style – only all-caps (which acts as uppercase).

However, GREP styles allow specific letters to change case, which allow the three changes to happen. Two styles have to be created:

The first character setting only has the All Caps setting changed in the options (called uppercase in the example).

The second character setting only has the Normal setting changed in the options (called NOT uppercase in the example)

From here, make a Paragraph style with the GREP styles as illustrated in this picture; or copy the text below

(?<=Mc)\l

Performs a positive lookahead in that paragraph style from any word with the first two letters Mc and then uppercase the third lowercase letter. It changes Mcavaney to McAvaney.

(?<=\u')\l

Performs a positive lookahead in that paragraph style from any uppercase letter followed by an apostrophe and then uppercase a lowercase letter which follows it. It changes O’driscoll to O’Driscoll or D’onofrio to D’Onofrio.

The last fix was the hardest and I owe a debt of gratitude to regular poster to both Adobe Forums and indesignsecrets.com poster, Jongware, for his help resolving the issue. To fix the hyphenated names, first a GREP which assigns All Caps style to words which start with a capital letter and then have lowercase letters and a hyphen. An additional GREP is then run afterward which strips the All Caps style from what was just applied except for the letter after the hyphen.

This fix did come with a warning of potential side effects so I have not implemented it professionally, but felt that it was worth demonstrating to show the power of GREP.

Adding bar graphs:

This is the one I’m most proud of, but is also the hardest to explain. I’ve said many times that InDesign doesn’t understand the data its placing, meaning that InDesign wouldn’t be able to just see numbers and then work out how big a bar graph would be based on that number.

One way would be to make the value of the bar in data merge a picture… but if the numbers vary by hundreds or thousands, this means making thousands of links… not an option.

However, if there was a way to make building blocks which could be sewn together using inline graphics which would add up to the target number, then there would only need to be a few links made for the bars in the bar graph… and that’s how the answer was achieved: Binary.

The Binary numbering system (1 and 0) can represent bigger numbers quite simply. For example, 00000001 is the number 1, but 11011001 is the number 217. A binary number 16 digits long e.g. 1010101010101010 (43690) can represent numbers up to 65535. There are many binary to decimal calculators such as this one:

Knowing this, I used a formula to convert the Excel value to Binary. Normally, Excel can handle binary numbers up to 10 digits long, but a clever workaround by Foggylog can extend this to 16, 24 or 32 places.

The Binary is then broken apart and if-else statements are used so that if a value appears in a certain field, then it was to yield either the relevant part of the bar graph or a fraction of a bar graph – this will become apparent later. See the Excel file for more information. Because the values in the graphs are so huge, i’ve only included portions of the total value in the graph, so the bar graphs are to the nearest 16 increments of value.

The trick to merging it all in InDesign was to make a textbox the graphs made by using inline graphics which are then replaced with the picture refs from data merge and making sure that the “fit images to frames” is turned on (it won’t work without it). This is where the fractions of the bar graph come into play as there can’t be a truly blank .ai file to link to, but if it is almost blank, that will work.