Since 2016, Colecandoo has provided the free version of the Data Merge to Single Records script for Adobe InDesign – a script that allows single records to be exported from Data Merge with unique filenames available from the Data Merge database itself. This improves Adobe InDesign’s default – naming each file Untitled-N and is only available for InDesign files, not PDFs.
On that note, the PRO version of this script is now available!
This script improves upon the free original by:
Exporting to various additional file formats, such as interactive PDF, EPS, PNG, JPG, direct to print, or PDF via InDesign first;
Add a primary key to either the start or the end of a filename;
When exporting to certain file formats – the ability to run a user-selected additional script before the export.
The original Data Merge to Single Records script offered by Colecandoo remains free and can be downloaded from the scripts page.
In the latest Colecandoo Youtube episode, four Data Merge specific features are covered, namely:
Adding faux-returns to a data merge field to split over lines, and subsequent limitations of this technique;
Using GREP styles to swap a character for a glyph during a Data Merge;
Highlighting field codes so that they are easier to see when not showing live data; and
A bug that occurs when a double-quote is at the start of any field in a Data Merge text file.
Faux returns within a field
The faux-returns technique is written about elsewhere, so rather than spoil their presentations, please read the articles directly from the appropriate sources:
I’m a fan of this trick, but emphasise that this is a workaround rather than a long-term solution, given that formatting is limited and there are more appropriate ways of accomplishing this task such as dedicated plug-ins or an XML workflow.
Swap characters for glyphs
Daniel Solis also features a clever trick to swap phrases with glyphs during a Data Merge that uses both GREP styles and ligatures. Again, rather than simply repeat the technique, please see his original video here.
A similar method can be employed using Indiscripts’ Indyfont script, but rather than swapping phrases with glyphs, will swap single characters.
Highlight Field Codes
The video also shows a method for highlighting field-codes when Data Merge is not in the preview mode. It relies on the [Basic Paragraph] style using a GREP style that contains a large highlight, and that any other styles in the document are based off of the [Basic Paragraph] style. It also means the document has to be styled correctly.
I’d demonstrated this technique following a real-world example of a live file where fields were very hard to see, and the file had to have an offset shell printed with variable data printed afterwards, so making sure the shell had no variable data on it was crucial. Using this technique would make finding the field codes much easier to see.
Double Quote bug
Also featured in the video was an issue that once again arose from a real-world example where a customer had provided a database that had double-quotes at the beginning of fields, but no closing quotes, resulting in rather unusual results.
UPDATE 2014-07-22: Since the release of Adobe InDesign CC 2014, variable QR codes via Data Merge is now possible. A post will be written about this feature once it has been thoroughly tested, but in the meantime this article has been edited to reflect the update.
Since the invention of QR codes, a burning question has been “how to incorporate these barcodes into a Data Merge?” There are lots of ways to generate one-off QR codes such as:
QR code generating websites;
An InDesign Javascript written by Jongware that is suitable for CS4-6;
Built into InDesign CC and above; and
Third party plug-ins that offer one-off creation as a “taster”.
But what if there are 30, 300, 3,000 or 30,000+ codes that need to be made as a direct mail campaign? Creating 30,000 QR codes is not a task that anyone would want to do individually.
So can Adobe InDesign, fresh from the shelf, create a Data Merge with variable QR codes? Apart from the latest release of Adobe InDesign CC 2014, no – not without the use of a third party plug-in. However, this workaround does the next best thing: Creates lots of QR codes all at once, export them as uniquely named PDFs for reference in a Data Merge, and then use the built-in Data Merge feature from InDesign.
The following example is a business card for fictional clothing manufacturer “Mean Jeans”. The client would like QRcodes that feature the staff member’s email address, and if no email address appears then no QR code needs to appear. The client has supplied the database in Microsoft Excel.
There is a way to automate this task thanks to three scripts
QRcode.jsxbin by Jongware (as previously mentioned). When this script is used on its own, a user interface appears asking the user for the text to be coded and then a level of error correction. Jongware did allow other scripts to call upon the QRcode script, and that leads into the next script;
A slight modification of a script supplied by an Adobe Forums user by the handle of sergemca. The original script (found in the same Adobe Forum link as Jongware’s QRcode.jsxbin) by sergemca searched an open InDesign document for any textboxes that contained the starting words MECARD: and would then convert the contents of the complete textbox into a QR code and then apply formatting such as scaling and rotation. As this example is creating QRcodes from email addresses, the modification in this example searches an open InDesign document for any textboxes that contain the starting word mailto: . Modifications to this script are best dealt with by the scripting forum of the Adobe InDesign forum.
PDFStyleExporter.jsxbin by Loic Aigon. This script has featured on Colecandoo before and it is used to split a large InDesign file into single page PDFs with unique names. While researching this story I have noticed that Loic will update this script in due course, so stay tuned.
To create variable QR codes:
Open the Excel file and create two fields in the database in addition to the other fields that need to appear: order, and ‘@QRcode (the ‘ will disappear in Excel after it is typed, this is intentional). In the order column, use the autofill function of Excel to add sequential numbers to this column. Leave the contents of the @QRcode field for now.
Save the Excel file but also save it as a “Windows Formatted Text .txt” file, and give it a name that reflects that this database is purely for the QRcoding only e.g. forcodingonly.txt
In Adobe InDesign, create a new file that will be used to create the QR codes first. In this example, a file that is business card size has been created (90x55mm). Once open, go to the Data Merge panel and select the data source as the recently created txt file. Once the data is available in the Data Merge panel, create two text boxes. One with the word mailto: followed by the <email> field, and another text box with the <order> field. The document should look like this
The order text will provide the filename in a later script, but it also must appear in the file but not get in the way of the QRcode graphic that will appear later. To make sure that this text will be live but not output in the resulting QRcode, select the textbox containing the <order> field, give the text a paragraph style called “order” – the only change is that it has no fill and stroke. Also, align the text box to be in the centre of the page. The document should now look like this
From the Data Merge panel, select “Create Merged Document” and merge to a new single page document.
This will create a large document that will contain one mailto: address per page. It is important at this stage that any mailto: addresses that don’t have email addresses in the text boxes be deleted (or else the QRcode script will make QRcodes that contain the word “mailto:” only and clearly won’t work). To delete these addresses open the Find/Change dialog box and in the GREP panel type in the word mailto:$ , leave the change field blank (as well as the formatting fields) and click Change All.
The document is ready to have the QR codes applied. To do this, the altered version of sergemca’s script needs to be run.
var _d = app.documents[0];
var _allStories = _d.stories;
for(var nx=_allStories.length-1;nx>=0;nx--){
var _storyAllTextFrames = _allStories[nx].textContainers;
for(var mx=_storyAllTextFrames.length-1;mx>=0;mx--){
_storyAllTextFrames[mx].select(); // change page
if(_storyAllTextFrames[mx].contents.indexOf('mailto:')==0){
var obj = app.doScript(new File(app.activeScript.path+'/qrcode.jsxbin'), ScriptLanguage.JAVASCRIPT, [_storyAllTextFrames[mx].contents, 3], UndoModes.ENTIRE_SCRIPT);
_storyAllTextFrames[mx].contents = "";
};
};
};
If done successfully, the InDesign file should now contain QRcodes in place of the text that contained the email addresses.
To export these QRcodes with their unique names
Run the script PDFStyleExporter.jsxbin (called PDFExportCropper.jsxbin on my machine)
In the user interface that appears, select the paragraph style “order” and leave the rest of the dialog box as it is. In the PDF options dropdown field, select the destination to save the resulting PDFs. Once done, click the Export button.
Again, if done successfully, the resulting PDF QR codes should now save to the nominated directory.
There are other ways to export single page PDFs but Loic’s script is used in the example in case a reference other than a sequential number is to be used, such as a person’s name, phone number, etc.
To incorporate these QR codes into the data merge:
Return to the Excel file and in the @QRcode field, use the CONCATENATE function to take the order number and apply the .pdf suffix to it. The formula to use is: =CONCATENATE(A2,”.pdf”)
Save the Excel file and again, also save it as a “Windows Formatted Text .txt” file, and give it a name that reflects that this database has been qrcoded QRcoding only e.g. qrcoded.txt
In Adobe InDesign, create (or open) the card that needs the QRcodes applied. Once ready for the data, go to the Data Merge panel and select the data source as the newly created txt file. Place the name, address references etc as necessary, and create a frame for the QR codes to appear.
Apply the qrcode field to this frame and go to the Data Merge panel and select Content Placement Options. In the dropdown field “Image Placement” select the fitting “Fill frames proportionally”.
Instead of using the “preview” function, go to the Data Merge panel and select the Export to PDF function and export one record only. In the example, page 7 was chosen at random.
Once satisfied that the merge looks like it will work, again use the Export to PDF function to export the entire merge to a PDF.
For the barcodes to fit the image, the PDFs need to import PDFs based on the bounding box. If the images are not fitting the frame properly, an additional script originally by Dave Saunders (but improved upon by Marc Autret) will allow the import option to change. The script is available from this forum and once loaded and run into InDesign, the option to select is “Content All Layers”
So there it is. It is worth noting that this is a workaround and not a direct live Data Merge solution. There are limitations to this solution:
Resulting codes can’t be colorised on-the-fly;
If the database changes, this will mean repeating the entire process, rather than simply updating the data in the data merge file once, and removing all QRcodes created previously.
Because the codes are not human readable without a decoder, there is added emphasis to check, recheck and check again to make sure the merge is behaving properly.
While this is a workaround, there is no doubt that a turnkey solution is preferable. There are enough third party providers making variable barcode solutions for Adobe InDesign. The full list is available here. From memory here is a list of third party providers that provide variable QR codes as part of a complete VDP package:
Rorohiko’s Tada QR;
XMPie’s uDirect;
Meadows Publishing Software
Cacidi LiveMerge;
Teacup Software’s BarcodeMaker;
DirectSmile;
Objectif Lune’s Printshop Mail;
Several edits since this was first published:
Added a list of third party providers who provide variable QR codes and moved a reference to an individual one earlier on in the article into that list;
Fixed type within field chevrons that did not appear when the article was published;
Acknowledged that variable QR codes via Data Merge are now possible in Adobe InDesign CC 2014
The chartwell font is a unique font that uses ligatures and stylistic sets to create both percentile graphs (such as pie graphs) as well as bar graphs. More information on the font can be found here.
Using this font solves an ongoing issue for users of Data Merge within InDesign: how to create variable graphs or charts using the data in the linked text file only. First suggested by David Blatner’s InDesignSecrets piece, the last Colecandoo blogpost examined some “hacks” to further improve on the font in creating variable graphs or charts
There is a particular font in this family called Chartwell Bars that has a distinct advantage over the others in the family and that is that, when utilising the stylistic set, the font can represent whole values between 1-1000, whereas all other fonts in the family will represent only whole values between 1-100.
However, an unintended and alternative use for Chartwell Bars makes it perfect for plotting X and Y coordinates for producing variable results. To demonstrate how, take the following example of a blood pressure chart that is part of a data merge that contains two fields – the X and Y axis of the chart.
Using a combination of Data Merge, Anchored Objects and the “no fill, no stroke” trick (demonstrated on this blog before) to the Chartwell Bars font, the “your result” callout is able to move in two dimensions according to the result of the Data Merge.
To show how this works, it is perhaps best demonstrated by showing the textframe that contains the “your result” callout that is an anchored object. The type that has no fill or stroke has been coloured black so it is clear how the trick works.
If preparing a one-off chart, obviously placing the “your result” callout would be far easier and faster than the trick demonstrated, but if preparing hundreds or thousands of charts as part of a data merge, this trick is certainly worthwhile.
A PDF demonstrating the proof of principle can be found here. Unlike other PDFs available for download from Colecandoo, this does not contain working files within the PDF due to licencing restrictions of the font.
The Data Merge feature of InDesign is great for merging text, but cannot take the text and parse it into a graph or a chart. This feature may be available through plug-ins purchased separately to Creative Suite/Cloud, but having the ability to create data merge projects that feature variable graphs or charts using only InDesign would be welcomed by many users.
In 2011, this site provided a proof of principle that pie charts and bar graphs could in fact be created via InDesign, Excel and Illustrator. Those interested can see those articles here and here.
Despite proving a point, the technique had several flaws:
The Excel files contained many formulas that were very complicated for the average user and likely to cause problems if the original database was replaced at any time;
The appearance of the graphs were limited to the graphics created in Illustrator, meaning any changes to the appearance of the graphs were complicated;
With the setup of the placed images used to make the charts, only one kind of graph could be merged at once.
It would be almost a year until InDesignSecrets co-host David Blatner wrote this piece concerning a solution using the FF Chartwell font created by the FontFont foundry:
Prior to this post, the FF Chartwell font had been considered as a solution but after reading David’s article, the issue was revisited. Reading the instructions for the FF Chartwell font looked promising, and the decision was made to bite the bullet and purchase the font.
Using the font (full instructions are available with the purchase of the font but can also be found here and this video here) has several advantages over my previous solutions:
Easy to set up;
The same data can presented using different charts in the same data merge record;
The appearance of the chart can take advantage of GREP styles, Nested styles, and the effects dialog box;
PDF processing time is faster with smaller size PDFs as a result.
There are some limitations that should be spelled out prior to a purchase of this font:
Most charts represent figures from 0-100. This is fine for percentile charts such as pie, rose, ring or radar charts, but limits the use of bar and line graphs;
While the font allows a pie graph to turn into a donut, be aware that the hole is made using a fill colour rather than being transparent;
The data in the charts must be integers (e.g. whole numbers, no fractions) and this means rounding results up or down accordingly. For percentile charts it is also important to make sure the total of figures in the data adds up to 100.
Tweaks or hacks to further improve the charts
There is a bar graph that represents figures from 0-1,000; but the graph appears from left to right and starts with a diamond shape. For those wanting a usual bar chart, here is the workaround.
Use the Chartwell bars font to represent the number between 0-1000, and format according to the Chartwell instructions; but change the fill/stroke of the font to none.
In the paragraph palette, select paragraph rules and then select above line to the size of the text, using whatever size is felt necessary.
Now that the chart displays as rectangular start/finish bars, change the rotation of the textbox that contains the chart to 90 degrees rotation.
A similar solution can be used when using a segmented bar graph, but instead of using the paragraph palette, use the character palette to create individual underlines for each segment. This can be further improved upon using GREP styles.
The illustration below was a bar graph using a paragraph style that had both above and below strokes, and the bottom has been clipped by putting the text box into its own frame.
To make a piechart have a true donut hole rather than a solid circular fill (as shown in the picture below) follow these steps:
Draw a circle the size of the desired hole and place it where the hole has to appear in the pie chart. Give this hole a paper fill and using the effects palette, set the opacity to 0%
Select both the drawn circle and textbox that contains the pie chart and group them
From the effects palette, check the checkbox that says “Knockout Group”
UPDATE 2020/01/02 – Since the release of InDesign 2020, new methods have been added to InDesign’s Data Merge content placement options. However, this article will remain for posterity.
In mid February, a post on the Adobe Forums asked the question:
I’m using Data Merge to place a single image on each page. I have the frame centered on the page and its reference point set to the center. I have the Data Merge options set to “Fit frame to content”, but when I run the merge all the images align to the top left corner of the frame. The frame expands or contracts as directed, but I would like for it to expand from the center. What am I failing to do?
The question was answered:
It doesn’t work for “fit frame to images” – instead the frame fitting options take the measurement from the top left.
Image fitting options in Data Merge are explored in greater depth in the “Merge Right” article in issue 52 of InDesign Magazine. Ultimately, there is a checkbox in the Content Placement Options dialog box of Data Merge that is misleading – it is the “Center Content” button. This button centres the content within its frame, but not the frame to its relative position on the page. For most of the fitting methods, this is fine, but for the “fit frame to images” option, the frame size will change whenever the image size changes, and when it does, the image resizes from the top left of its position on the page, rather than from the centre point of the image.
A solution was provided to the poster along these lines:
Create a text box larger than the largest image that is part of the merge. Align the text to be centered not only left and right, but top and bottom (from the text frame options). If done correctly, the flashing cursor should be in the center of the textbox.
Draw a new graphic frame and assign the image field of the data merge to the frame.
Cut and paste it into the textbox. If done correctly the box should be centered within the textbox.
Make sure that Image Placement dropdown of the Content Placement Options from the Data Merge panel is set to “fit frame to images”
The solution is also flexible in that the image can be aligned to left, center or right horizontally, and/or top, center or bottom vertically.
The solution works… provided the text box is big enough for the images within the data merge… otherwise the item becomes overset and the image disappears.
It is also a somewhat clunky solution as it means having a large textbox in the document simply to position an image.
An alternate solution would be to draw a larger than normal frame with the “Center Content” turned on using the “preserve frame and image size”, but if the content was to have an outline on it then this solution would not be appropriate.
However, CS6 introduces a new text frame feature called auto size. This is handy for not only making text boxes fit the type only, but for creating minimum sizes and allowing the box to “grow” when more text is added, based on the auto-size instructions in the options.
Using this new feature, we can take the earlier solution and apply the auto-size to the text box that is housing the now inline graphic. I can now also control where the image “grows” from using the auto-size instructions, rather than using the text alignment features of the text box.
The other great feature is that I can use anchored objects with this image too… something that I could not normally do with an image frame as anchored objects can only anchor to text frames. In this case I’ve added a fancy caption bar that uses anchored objects of its own to grow/shrink the caption as the data comes in.
So scrolling from record to record, I can see that the images are indeed starting from their reference point in the auto-size dialog box, and that the caption anchored object is always staying in its position at the top right of the picture.
When merged to single page merges, this trick works fine… however when I merge to multiple records per page… it goes haywire.
Despite this bug, this trick is also handy even if data merge isn’t being used. As a snippet or a library item, as not only does caption stay in the same relative position to the image regardless of where the image is moved, but there is more flexibility as to how the caption can be positioned or designed.
The file used for this article can be found here. To keep the file size down, the original links have been removed so that substitute the links and merge file can be used to try this out.
Variable Data Printing (VDP) campaigns can be a great way of not only targeting a particular audience, but also getting their undivided attention. Sadly, much of the marketing utilising the latest and greatest VDP technology to target their audience are still relying on the old-fashioned greeting “Dear your name”.
While this blog isn’t a guide to marketing and nor does it profess to be, I just think that with the technology available in Adobe InDesign’s Data Merge, do VDP campaigns have to be stuck in the 50s style “Mad Men” campaigns?
In the past, the blog has demonstrated how to make contemporary campaigns with salutations displayed as:
Pencil-case letters
Scrabble letters
As part of a cartoon
Name badges
Street signs
Some of the samples use complex Excel files to break up names into individual letters to create the merge, but others use nothing more than live type to spice up the delivery of the reader’s name. On that note, if type can have an effect applied to it, then any VDP campaign can be taken to the next level.
For this post, I’ve just created a simple example using two live effects: One to look like writing in beach sand, and the other to look like clouds.
Admittedly applying both techniques to one campaign is overkill but the point is to illustrate that so long as effects can be applied to the live type, then creative effects like these can present the reader’s name in a more attention-grabbing fashion. The PDF (that contains all assets that made the PDF) can be downloaded here.
Effects that can be applied to live type are limited in InDesign. Effects such as distortions are limited to type on paths, the gravity effect (as applied on the sample) and the “square peg round hole” trick that can be found elsewhere on this blog. Other effects such as bevel/emboss, outer glows, drop shadows, feathers and satins are still available, as well as how they are applied to the layout such as the multiply effect, screen etc.
One individual who seems to make all text come to life is Mike Rankin who has a regular blog on Lynda.com known as InDesign FX and has other material on the indesignsecrets.com website. Also bringing these campaigns to life is the use of fonts that suit the effect being applied… that is for the beach sand example I decided to use a font that would look like a child’s handwriting as opposed to a serif or sans-serif font that is more appropriate for a letter or book.
I have recently found inspiration from the youtube channel Vsauce that has a regular episode titled DONG: an acronym for Do Online Now Guys. In a recent DONG, host Michael Stevens recommended the site says-it.com, a site for creating one-to-one web to jpg pictures. The site provides ideas – not always original – for VDP campaigns… but sometimes inspiration comes from the strangest places.
The bottom line: targeting a market is one thing, getting their attention is another. InDesign’s Data Merge can be a powerful tool for VDP campaigns, don’t limit communicating with clients with the old and tired “Dear your name” greeting.
You must be logged in to post a comment.