Uithuizen-InDesign-Data-Merge-1000-Cards

At the start of the 2020 CoronaVirus, the business group for the village of Uithuizen in the Netherlands, planned a discount card scheme to support local retailers.

The plan was to support 34 local businesses, 10 of which had been severely impacted by the Corona disruption.

1000 cards were produced and all business names were to be in a complete random order and position on the reverse.

The reverse of each set showcased 10 local businesses, each with five of the affected ten companies always shown on the reverse. Five others were chosen randomly from the remaining 29 businesses on the page.

To showcase all the 39 companies (10 more than others), it was decided to break the 1000 cards into two sets of 500, so each 500 set can always showcase five of the majorly affected ten companies.

In summary

1000 cards – 2 sets of 500.

Each card is to have 34 business names on the reverse.

Each card is to highlight 10 companies in a reversed block of text

Each card to include 5 static highlighted company names (reversed text block).

Each card is to include 5 random company names highlighted from the remaining 29.

Each card is to have all company names in a different order and random position.

So, how was all this controlled randomness achieved?
This may get a bit geeky. But I love a challenge, and in the end, the result was superb.

In total, ten different businesses were to be promoted more, so it was decided to split the project into two sets of 500 cards. Each set would have five static named businesses, always highlighted along with a further five taken from the remaining 29.

The project was put together with a mix of Excel, LibreOffice and InDesign; using data merge, character styles, anchored text boxes and a three-column balanced master frame.

The Excel

I created an Excel document that took a list of 29 companies, and randomized the order of these companies. The Excel document also Added an ! (exclamation mark) in front of five of the company names. The Excel was also built to added a – (hyphen) in front of the remaining 24.
The Five static company names were added to the single list, each prefixed with an ! (exclamation mark). All these ! (exclamation mark) prefixed firms were to be the highlighted businesses.
Excel itself has a sheet size limit, so to keep processing time down and stay within the Excel limit, we split each set of 500 cards into 4 documents of 125 sheets of 34 randomised companies. Each sheet would equal one InDesign page.
Eight files of randomised names were produced in total. These were saved as CSV for export into InDesign using data merge.

Example of random and static names – the bottom five will be static (highlighted) on each sheet.

29 Randomised names. This was a test Excel, so it has notes.

29 randomised names with five added static names. This was a test Excel, so it has notes

Excel document with company names, randomised to 125 data sheets. Look at all those sheets!

Single Excel sheet calling data from 125 sheets, ready to export to InDesign for Data merge. Yes, I’m a bit of an Excel geek – you need to be when dealing with Data merge!

The indesign

The data merge was the simpler part of this project.

A master InDesign single-page document was created for the data merge master. It contained a three-column text frame on the only page. This text frame was set to “Balance columns”, meaning the items placed inside would automatically balance visually.

Inside the master three-column text frame were placed 34 individual Anchored text frames, each with a minimum 5mm height to keep size consistency and auto height applied, to allow the frame to resize, for two-line company names. The anchored text frames were designed to flow through the three columns. The final Anchored text frame (bottom-right corner) was allowed to automatically resize its height, so to buffer and fill any space left over at the bottom of the column.

Fixed-width anchored text frames. Yes, missing fonts!

The final bottom fixed panel was placed as an anchored object with Bottom text frame with auto height applied, allowing the panel to stretch to fit if needed.

The justification of the bottom frame was set to vertical justify. This will force the text lines to fill the frame, should it need to stretch.

Bottom text frame with text vertical justification applied.

A data merge was applied to the InDesign file, and text was imported into each anchored frame. Text from the Excel CSV started with either an ! (exclamation mark) or a – (hyphen).

Finally, paragraph styles were applied and the front character removed.

A Grep find/change was used to find Paragraphs beginning with the ! (exclamation mark) and they were given a chosen style. The same procedure was carried out for paragraphs beginning with – (hyphen). Yes, you can hide these front characters in a style, but I wanted the InDesign cleaner to make sure there were no problems later on the digital press. So I just removed them.

The turquoise and grey backgrounds filling the anchored text frames were applied in the text paragraph styles, clip to frames was applied in the style to fill the text box.

Grep was used once again to remove the first character (exclamation mark or hyphen) of each paragraph.

Design, once styles are applied and front characters removed.

The text in the final text frame was given a paragraph style on the data merge, resulting in eight documents of 125 pages each with randomised names and positions. A video is below.

Video of the final pages.

Final pages video.

Because the cards were printed on a digital press, the artwork (PDFs) could be ripped and imaged on the fly, enabling each card to be produced individually with a different reverse design.

The cards were designed by local Uithuizen designer, Dick Stoppels https://www.dixtoppels.nl/