Data Merge “Did you know” Part One

Regulars to the site will know that many of my articles relate to InDesign’s Data Merge feature. Given the amount of tutorials already available online elsewhere concerning basic tutorials for Data Merge, the Colecandoo site focuses more on articles about Data Merge in relation to scripts, GREP styles, or advanced techniques.

But there is a middle-ground that hasn’t been covered in many Data Merge tutorials, nor here on Colecandoo, so over the next two articles, I will attempt to bridge that gap and highlight some lesser known issues that can become a problem if users aren’t initially aware of them.

Can’t package the data or links used in the data

When InDesign packages an INDD file, it will save a copy of the file and copy any links used in the document into a Links folder, and any fonts used (within licensing restrictions) to a Document fonts folder.

However, this does not extend to the source data of a Data Merge file, nor any links that the source data may refer to.

PDF made from merge is different to regular PDF

I have written about this before but ultimately when exporting a PDF directly from Data Merge, it makes a variety of PDF that is similar but not the same as a usual PDF, as the following options cannot be chosen.

  • The ability to merge to an interactive PDF
  • The page range (not the record range)
  • Spreads
  • Create Tagged PDF
  • Create Acrobat Layers
  • Hyperlinks

I’ve speculated why this might be the case in this article but until this addressed, it is a consideration to be aware of.

Headers with the same name

If the headers in a database are exactly the same name, InDesign’s Data Merge will add a sequential number after the first instance of the field name to make a distinction between the field names.

Colons can cause weird issues in the header

This featured briefly in my creative pro article “Troubleshooting data merge errors” but in short, colons used in field names can cause one of two dialog boxes when used in particular circumstances. Thankfully, if a colon appears at the start or end (or both) of a field name, the data will import without any issues, but if a colon is within the field, then a dialog box with the words “Generic extended parser error” appears.

If there are two or more colons in the field name (neither at the start or end of a field name), a dialog box that says “not well formed” appears.

UTF-16 is the format built for Data Merge

InDesign’s Data Merge is designed with UTF-16 text in mind. However, CSV and TXT files exported from programs such as Microsoft Excel usually export to UTF-8.

This is usually fine for most circumstance in English, but can cause problems when:

  • using an alphabet other than the Roman alphabet;
  • the data contains punctuation or characters that may not be available via UTF-8

Excel does have an option to export to UTF-16 and it is worth using. The option is here when exporting via Excel:

In part two of this Did You Know series, we will look at other lesser known phenomenon, such as:

  • Data is there even if link is missing
  • Merge fields can be removed via the hyperlinks panel
  • Shift clicking during the import does not show options
  • The benefits of linked images in the same folder as the source text file

If you have any lesser-known Data Merge behaviours that you think would easily make this list, please feel free to mention them in the comments.

Data Merge to Single Records Pro: Now Available

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 script can be purchased for A$15 from the Buy Now button below.


The original Data Merge to Single Records script offered by Colecandoo remains free and can be downloaded from the scripts page.

Of interest: New Data Merge techniques and quote bug

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.

 

Better Infographics for Data Merge with Chartwell Bars

While speaking at the 2016 PEPCON in San Diego along with Co-presenter David Creamer on the topic of Data Publishing, I presented an older tip that allows shapes to change size based on numerical values that appear in Data Merge. The tip requires the Chartwell typeface, particularly the Chartwell bars font. I’d mentioned at the time that while it was a novel tip, I didn’t have a practical purpose for it. I’d also mentioned in my presentation about using knockout groups in InDesign to hide information and had demonstrated it using my “Parkway Drive” demonstration where it is used to hide parts of a sign that changes size, but again felt there should be a better use of this tip.

However, it was on my 15 hour flight from Los Angeles to Melbourne where I thought of a new and much more practical purpose – creating infographics. I also thought about getting some sleep, but that was a fool’s errand!

Once I arrived home, I tested out the theories I had during the flight, and while the results were mixed, I was happy with what had been achieved.

Ultimately, I have created three techniques for anyone making infographics. In all instances, I’ve colored the chartwell bars font as black so that the technique can be demonstrated, but in application the type (and its spacer) would be given the color “none”:

1 – Infographics as scaleable shapes.

01image

This uses the method described in an earlier indesignsecrets.com article that I have written. Rather than rewrite the tip, the link to that article is here. The point of difference is that the shape being transformed into an infographic is what is being scaled.

There are some drawbacks to this method.

First, the shape has to allow the chartwell bars font to expand from the left to the right without getting caught on any part of the shape, so not every shape will work. Bottles that were used in the example were fine because they meet this criteria.

02image

Second, there is a lack of precision, especially concerning low numbers as the graphic scales. This appears to be because there is a minimum size that the graphic can shrink to.

03image

2 – Data that is pasted into a vector

04image

This method works the same way with the exception that the data is in a rectangular shaped textframe that is pasted into the target shape, and also given a 2mm spacer object to allow low figures to be presented. The 2mm spacer is a 2mm square that is an inline object before the figures in the chartwell font.

05image

For anyone wondering why such an odd technique was used to add 2mm to the frame, I had tried using a 2mm left align or a 2mm inset space in the shape itself but these presented issues.

3 – Hiding an image underneath

06image

This works the same way as method 2 with the exception that rather than being pasted into the graphic, it is pasted above the graphic. An additional anchored object that is larger than the infographic is then pasted after the figures in chartwell bars and given very specific values in the anchored object dialog box, along with being given the fill color of paper and a multiply effect of 0% from the effects panel. The frame with the values is then grouped with the infographic that is underneath and the “knockout group” checkbox is ticked.

07image

To make the effect more impressive, an “after” graphic is added that is the same size as the infographic but has different properties to make the difference in the values clear to the reader.

Moving forward

By itself, these techniques aren’t that impressive if creating one-off graphics, but if preparing infographics for variable data (whether for a catalogue or direct mail) I’m sure that readers will find these methods quite useful. These are not the only infographics tricks I have recently discovered, so watch this space.

Next Beta Script: Data Merge Cut and Stack Assistant

As a regular user of Data Merge, I often have to assemble projects that require cut and stack impositions. Most of the time, I prepare my files one-up at the correct size and output to PDF, knowing that the RIP of the digital printer has imposition software that has the ability to prepare cut and stack style impositions.

If cut and stack is an unfamiliar term, it is a style of page imposition where the subsequent pages appear on the sheets below until the end of a stack, and then begin again at the top of the sheet in this continuous pattern.

Unlike bookwork that may have a maximum page count of under 1000 pages, cut and stack impositions can deal with page counts in the hundreds of thousands… enough to make any imposition program buckle.

Another way of handling cut and stack impositions is to prepare the imposed base in Adobe InDesign, and then manipulate the data so that rather than being one long list, the list is split into columns based on the amount of pages-to-view on an imposed sheet. This is a quicker method as there are less pages to process and no imposition software to use, but there is the time taken to split the data appropriately, and will suffer any human error that went into manually making the revised database.

Frustrated with this situation, I decided to create a script that would take a large database and repurpose it for a cut and stack imposition. On that note, I present to you my latest script.

UItoexplainlaststacks
The imposed base is created in Adobe InDesign with text frames in place for the data merge placeholders. The script is then run and prompts the user for the original data. An interface appears asking the user for:

  • The records to process;
  • The amount of records in a set;
  • The amount of sets in a stack;
  • How to process last records (in case the stack sizes are uneven); and
  • Any other identifiers visible in the database.

Once OK is clicked, the script creates a duplicate of the original database and arranges the data appropriately, and launches the Data Merge palette so that the imposed base placeholders can be populated.

basewithfieldcodes

If you would like this script, please go to the Scripts page and look for the Data Merge Cut and Stack Assistant script.

Data Merge from InDesign to unique filenames script to remain FREE

Several months ago, I announced the beta version of a script that had been in development for some time – the ability to prepare uniquely named PDF or InDesign files from a Data Merge.

The Beta release of the script is now complete and the final version is now available from the scripts page. More importantly, it will remain as a FREE script for the InDesign community to enjoy.

To see the script in action, I have also produced a series of Youtube videos on the Colecandoo channel.

A Pro version of the script that contains enhanced file naming features and expanded export abilities such as export to png, interactive PDF etc is also available from the scripts page.

I have also made custom versions of this script for specific requests, so if that is something that interests you, contact me via the requests page.

Data Merge from InDesign to unique filenames – now a reality

Ever since I published the article “Breaking up is hard to do… InDesign files into individual PDFs that is!” on Colecandoo, it has been one of the most searched for and popular stories on this site.

I have also written two articles for InDesignSecrets particularly relating to Data Merge and unique filenames: Data Merging Individual Records to Separate PDFs and Data Merging Individual Records to Separate PDFs Part 2: via Scripting. While these methods work, I felt that there had to be a better way to accomplish the task.

Since returning from PEPCON 2015, I have been busy creating a javascript solution to the problem. The script is now at a stage where I am pleased with the results and would like to begin beta testing the script.

nothingselectedThe script is a user interface that is designed to work when ready to merge the records. The script:

  • Merges to uniquely named PDF or InDesign files;
  • Uses information from csv or txt in the Data Merge to create the filenames;
  • Creates web-safe filenames;
  • Provides warnings about duplicate filenames, missing images or overset text;
  • Can merge one record, a range, or all records in the database

Here are some more screenshots of the script:

resultsbadduplicatefinishedIf you are interested in trying the beta of this script, it can be downloaded from the Downloads or Scripts pages, or from the Adobe Exchange.

Data Merge: Multiple Record Madness

There have been plenty of posts on the Adobe InDesign Forum lately concerning issues that users are having with data merge, particularly Multiple Record Data Merge (MRDM) projects. These projects could be “stepping up” artwork onto a larger sheet for trimming (e.g. imposing many business cards onto one sheet) or preparing catalogues for example.

Before rushing into an FAQ of issues concerning MRDM, here is a step-by-step of preparing a MRDM to ensure the minimum of fuss:

1) In a new document, go to the Master page and create the static items that are to appear on every page.

mrm01

2) Create a new master page BASED ON the master page created in step 2 and in this page, add the items that will be variable, but prepare the set up as if it were for one record only.

(for those familiar with Data Merge, the variable can indeed be on a regular page. The difference is that MRDMs created with variable items on a regular page will not allow the source data to be linked)

mrm02a

mrm02b

3) From the Data Merge palette, choose “Select Data Source” and select the text file that will be used for the merge.

4) Populate the variable placeholders using the fields from the Data Merge Palette, being sure to that the placeholders of the text are visible in their own frames. Once satisfied with its appearance, select “Create Merged Document”

mrm04

5) A new dialog box appears. In the tab “Records”, make sure that the Records per Document Page dropdown says Multiple Records. Then from the tab “Multiple Record Layout”, set the appropriate margins of the artwork. If using the preview, note that the position of the first record may have moved – this is normal. Change the margins to the margins that were in the static layer, and layout the records as appropriate. Click OK once ready to proceed

mrm05a

mrm05b

6) A progress dialog will appear. After a moment the document will be created either with or without an overset text warning.

mrm06a

mrm06b

There are several things that should be noted:

  • The margins in the InDesign file are irrelevant for where the variable data starts – this is determined by the Data Merge Panel;
  • That during the construction of the file, it is only possible to view the data 1-up. To preview multiple records, this can only be done from the  Create Merged Document from the Data Merge panel.

Common complaints when preparing a Multiple Record Data Merges:

Each page has the same record repeated, so page 1 has record 1 many times, page 2 has record 2 many times, etc.

Likely that many variable placeholders were created and populated thereby filling the page, instead of one series of placeholders for one record only. MRDM works by creating one record and then using the MRDM panel to allow the next records to be inserted based upon details here (e.g. distance between the records, direction of flow of the records)

MRDM records aren’t merging where I want them to merge (e.g. off by half a millimetre or more, or off by miles)

Data merge uses the margins within the MRDM dialog box, NOT the margins in the active document. There is an additional glitch that offsets the starting position by fractions of a millimetre. This may seem insignificant but there is no reason that the software should not place the text to precise measurements.

mrm07a

mrm07b

Data merge has a further glitch if the document was initially created at one size but was then resized (regardless of orientation or size). When merging, the starting offset appears to be where the original size page margins would have been. The following example shows the same file but just made landscape.

mrm08a

mrm08b

This glitch persists despite further resizing, adjusting of margins, or saving as IDML and reopening. The only solution is to cut and paste from the defective document into a brand new document.

When MRDM merges to PDF, the first record does not appear, and instead of the records displaying one after the other, only the first record placeholders appear and is repeated through the document.

mrm09a

This only occurs when exporting directly to PDF. In this instance, the variable data portion has to be on a regular page, and any items that are to remain static need to be on the master page.

mrm09b

mrm09c

The MRDM preview is correct, but the resulting InDesign file has only one record per page. Once the view is changed to see the entire pasteboard, it is clear that the other records are on the pasteboard.

mrm10a

mrm10b

Likely because the variable and static data is on the same master page. Instead, the variable data portion has to be on a regular page, and any items that are to remain static need to be on the master page. If linking to the text is essential, the workaround is to put the background on a master, and then make a second master that is BASED ON that background and put the variable data on that layer. Refer to part 2 of the tutorial at the start of this post for more information.

Other commonly asked merging questions:

Upon selecting data to import, a dialog box says “The data source has one or more empty field names. Please fix the file or select another file”.

mrm13

The header row has fields that don’t have names. Can happen if the field names have no headers – the header row has to have names in it. Can also happen if an excel file is saved as a txt/csv file but it saves more columns than required. These excess columns need to be deleted in excel before attempting to import the data again.

The text has picked up the formatting of the line underneath.

This has to do with the “Remove Blank Lines For Empty Fields” options in the Content Placement Options portion of the Data Merge palette. This issue has been discussed elsewhere on Colecandoo.

The records are all in their own frames, instead of one long flowing frame like this:

mrm11

Data Merge works in this fashion, it doesn’t have a “next record” feature that Microsoft Word has in its “Mail Merge” feature. There is a workaround in the form of a script created by Loic Aigon that will do this; or by merging to a new file and then using a script such as Ajar production’s merge textframes that will thread the text so it run into one text frame, but if the data is likely to change then this procedure will have to run all over again. XML workflow is better for this kind of project.

How do I prepare a multiple record merge where records specific to one field are on the same sheet?

mrm12

This can’t be done from InDesign “Off of the shelf”. Data Merge, whether single or multiple records, can only handle one to one database relationships. It can’t merge items such as itemised invoicing for a client database where the database contains varying record lengths for each client. This is an example of “one to many” database relationship. InDesign can only handle “one to one” database relationships.

How to I preserve carriage returns from my Excel database into Data Merge?

Can’t be done. Data Merge only works with txt or csv files, so ultimately the Excel file has to be saved as one of these formats. A return (whether soft or hard return) in the txt/csv file indicates the end of the record and the start of the next, whether intentional or not. One trick is to substitute the returns with a character that is unlikely to use in normal type (e.g. the “pipe” symbol = | ) and once merged into the file, use find/change to replace the | with a carriage return.

The drawback is that this method cannot be used if merging directly to a PDF as the intermediate step of removing the pipe symbols cannot be done. Another drawback is if the data file for the merge is replaced, the merge will have to be done again. XML is a better choice for this task as carriage returns, non-breaking spaces and other special characters can be preserved.

How do I remove unintentional line breaks (whether soft or hard returns) from my Excel database before using Data Merge?

Dozens of answers exist on the many Microsoft Excel forums that may/may not work within the Windows OS, but on the Mac OS there is no reliable answer that works within Excel itself. Anyone who can answer to this question should feel free to post it in the comments. Best answer so far was again from Loic Aigon’s Blog.

My data is not importing properly (e.g. Records are starting where they are not supposed to, characters are corrupting, etc)

Could be a variety of reasons depending on how the data was prepared, how the fields are separated (whether comma or tab). The data should be checked by finding the corrupted record in InDesign, then comparing the data file in a text editor such as UltraEdit or TextWrangler to see what is happening.

Variable QR codes? Sort of possible…

splash

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:

  1. 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.1qr
  2. 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.txt2qr
  3. 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 this3qr
  4. 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 this4qr
  5. From the Data Merge panel, select “Create Merged Document” and merge to a new single page document.5qr
  6. 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.6qr
  7. 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 = "";
         };
    };
};

7qr

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

  1. Run the script PDFStyleExporter.jsxbin (called PDFExportCropper.jsxbin on my machine)8qr
  2. 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.9qr

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:

  1. 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”)10qr
  2. 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
  3. 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.11qr
  4. 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”.12qr
  5. 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.13qr
  6. 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”

14qr

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

Another use for FF Chartwell: Plotting X and Y coordinates

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.

montageTo 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.

trickrevealedIf 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.

Data Merging Charts and Graphs with FF Chartwell

lumbergh

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.

  1. 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.
    mergepic1
  2. In the paragraph palette, select paragraph rules and then select above line to the size of the text, using whatever size is felt necessary.
    mergepic2
  3. Now that the chart displays as rectangular start/finish bars, change the rotation of the textbox that contains the chart to 90 degrees rotation.
    mergepic3

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.

mergepic3a

To make a piechart have a true donut hole rather than a solid circular fill (as shown in the picture below) follow these steps:

mergepic4

  1. 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%
  2. Select both the drawn circle and textbox that contains the pie chart and group them
  3. From the effects palette, check the checkbox that says “Knockout Group”

 

 

Centering “fit frame to images” using InDesign CS6

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:

  1. 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.
pic1
  1. Draw a new graphic frame and assign the image field of the data merge to the frame.
  2. Cut and paste it into the textbox. If done correctly the box should be centered within the textbox.
pic3 merged
  1. Make sure that Image Placement dropdown of the Content Placement Options from the Data Merge panel is set to “fit frame to images”
pic2

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.

pic4 merged

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.

pic5

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.

pic6 merged

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.

pic7

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.

pic8 merged

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.

pic9

When merged to single page merges, this trick works fine… however when I merge to multiple records per page… it goes haywire.

pic10

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.

%d bloggers like this: