From time to time I receive emails requesting support for some of the scripts that I offer through the site. Since InDesign began to support semicolon separated text files for data merge, one particular issue began to receive more requests than normal.
The emails were consistent in nature – users had downloaded the data merge single or pro script and ran the script on files they had prepared. Instead of users being able to select from the fields to the left, all of the fields appeared in one line.
This behaviour usually occurs when the script runs a data merge database that had a CSV extension, but was actually separated by semicolons rather than commas. I’d explain this back to the user and ask them to try a different CSV export from Excel, or use my preferred file export of UTF-16 text from Excel.
However, many users who had exported from Excel to CSV said that this did not change the issue and the problem persisted. Usually the problem was that – despite choosing CSV from Excel’s export options, the software was still using semicolons as a delimiter rather than commas. Luckily, exporting to UTF-16 text usually resolved the issue.
On that note, I was uncomfortable with this issue and tried to replicate an Excel export from CSV that would use semicolons as delimiters rather than commas, but I couldn’t replicate this behaviour. But then I stumbled across the following article.
In short, the article says that Excel uses the user’s locale to determine what delimiter to use for CSV files. In short, if you use a comma to separate a dollar value from cents rather than a full-stop, then a CSV will likely export with semicolon delimiters rather than commas.
Adjusting this setting is not so simple, especially for Mac users like me – the adjustment is to change a system preference that uses the appropriate currency format, but that changes lots of other related information, so this isn’t an option.
Ultimately, if you are using the data merge to single record script, and are doing so with data exported from Excel, I highly recommend that you do so with a UTF-16 Unicode Text format.
I’ll admit this was a phenomenon I was unfamiliar with, and somewhat frustrated that a file format that itself stands for comma separated variables – isn’t actually separated by commas but is in fact separated by semicolons… depending on what system locale your computer is set to and that Microsoft Excel obeys.
Within the type menu of
Adobe InDesign is the Text Variables feature. This allows users to insert a
special character that will display one of the following items:
A chapter number
A file-related date such
as the creation date, modification date or output date
Filename
Image Name (aka captions
setup)
Last Page Number
Running Header based on
either a used paragraph or character style
Custom static text
Unfortunately, there is an unwanted behaviour of the text variables – InDesign treats them as a single character rather than the actual content within the variable. This has a few unwanted consequences:
Long variables that would
normally break over several lines are squished into one line;
They cannot be formatted
using GREP or Nested styles, nor can specific words be manually selected for
formatting.
It’s an issue that is “in backlog” by the Adobe InDesign team to address, but that was first stated in 2017.
If the issue was resolved, it would have enabled my GREPGraph solution to be applied in the following InDesignSecrets article.
As it turns out, it is also affecting another solution that would make lives easier for anyone who has to create diaries and planners on a regular basis.
The brief:
A planner is created
by making a base template and then creating threaded text for the dates that
are represented only by numbers. This makes populating a diary from week to
week relatively easy…
…until the other material has to be populated, such as what month it is, what term it is, and what week it is. This is best illustrated in the first two minutes of a Youtube video by Rob Cubbon.
Incidentally in Rob’s video, he uses frame breaks between each record, but that isn’t required. Instead, adjust the paragraph style of the numbers by going to the Keep Options and from the Start Paragraph dropdown, select In Next Frame.
The technique:
Instead of populating
the text frames simply with numbers, what if the frames were populated with
more information that can be called upon by running header text variables, such
as the month?
I can make a list in
excel that contains the day of the week and the month in one column. This is
also done without a space intentionally for reasons that will become evident
soon.
I can then copy this text
to my InDesign file. However, note that the text overflows and isn’t correct –
that is because the flowing numbers need to have a character style that will
hide the text that we want to be visible elsewhere. To do this, I’ve created a character
style called hidden, and its properties are:
.1 point high;
1% wide;
No fill or stroke.
That’s fine, but
applying that manually to everything but the numbers will be a nuisance, so the
paragraph style for the numbers has to be modified using Nested Styles.
The nested style will apply a style of [None] to the digits as they need to be visible, but will intentionally hide the month.
So why I am I
intentionally hiding the month? Because I’m only interested in the information
it represents, and this can be called upon by a text variable. I will go to the
master page and insert a text variable for a running header showing me the
first result of hidden on the page.
The issue with the technique
That’s fine until I
get to a spread that contains two months. I’d rather both months be present
rather than the earlier month. For example, I’d like the headline to read
April/May instead of April. I can do this by returning to my master page, apply
my blinking text cursor to the text variable that is already there, type a
slash and then create a new text variable that is a running header looking for
hidden character style in the last instance.
Once I’ve inserted the
variable and returned to that spread, that now looks fine, but all others are
now affected.
I should be able to
make a GREP style that will look for a word, then a backslash, and the same
word it initially found. I’ll create a new text frame with some sample text to
see if it works. The GREP code I’ll use is this:
Apply style: Hidden
GREP: (.+?)\K\/\1
Looks like its working
in my demo, but as I check the document it isn’t… I’ll find a spread where I
know the months split.
But that should work. I saw it work on plain text, why isn’t it working on text variables? That’s because GREP styles and Nested Styles don’t work on text variables. If I want this technique to work, I’ll have to use a workaround.
The workarounds:
Use a script to convert the text variables to plain text. Marijan Tompa used to have such a product via the Adobe Exchange, but it has since been removed, and it is not mine to give away. Other scripts do exist on the Adobe InDesign forums, but they are not as flexible as Marijan’s original script. It also works only one way, and can’t convert the text back to variables.
Don’t attempt to use the GREP style to hide the duplicate month, but instead only add a Running Header for the first instance on a page and add the others manually by overriding the affected master pages;
Create the desired date in Excel using a formula.
The issue with the
first two solutions is that it prevents the solution from remaining live. The
last solution will work but requires in-depth knowledge of Excel and – for many
diaries – requires having an Excel file on stand-by for this purpose.
Ultimately, I’d love to see a fix for this issue as it would open up many possibilities. If you feel that this needs attention now rather than later, please cast your vote here.
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
You must be logged in to post a comment.