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
%d bloggers like this: