Database to InDesign

Update 9/14/2011: This will be the first installment in a series of articles covering my ongoing research on database-to-InDesign solutions. David Blatner (www.dtptools.comwww.63p.com) has pointed out that I left out one notable plugin, DataLinker from Teacup Software, so in the near future I’ll study and test the plugin and then write a second review including all four plugins.

In addition, Chris Ryland of Em Software has commented that they are working hard on updated documentation and new tutorials, and that InCatalog working in tandem with InData (which I did not include in my original review) can provide a powerful solution. Once the new documentation is online I look forward to repeating the review a third time.

My thanks to David and Chris for their input.

—–

Over the years I’ve found that companies often have diverse (and sometimes quite unusual) workflows for getting information from databases into InDesign. Many of these processes are so inefficient that they cost thousands of dollars per year more, in the long run, than either hiring a developer to create a custom XML export script or purchasing a database-to-InDesign plugin. This basic introduction to InDesign/database integration begins with a summary of available data sources and InDesign’s built-in tools for working with them. I’ll then discuss the essential components of a database-to-InDesign automated workflow and review several third party plugins.

Types of Data Sources

CSV. Comma Separated Values. Strictly speaking, this is a text file representing data where each value is separated by a comma and each row ends with a hard return. Since tabs are also commonly used as delimiters between columns, CSV is often the general designation for any text file containing a data set, even though this is technically inaccurate. CSVs can be exported from most databases and spreadsheet applications.

Spreadsheet. Created by applications such as MS Excel primarily for calculations and managing flat (2 dimensional) data sets, as well as generating charts and graphs.

XML. eXtensible Markup Language. A tag-based system for encoding documents. HTML is a subset of XML. Most databases can export XML.

Database. An often complex organization of data usually maintained by a database management system (DBMS) such as Oracle, Access, Filemaker, or MySQL. (wikipedia.org)

Related Built-in InDesign Tools

Convert Text to Table. When placing a CSV you may want to convert the tab or comma separated text to a table. This is easily done by highlighting all the text then going to the table menu and selecting “convert text to table.”

Data Merge. A utility included with InDesign that merges the content of a CSV with a template of your design. Great for static layouts such as forms, certificates, business cards, name tags, mailers, etc.

Please note that Data Merge does not create links to the original data source; it only imports the data.

XML Import. InDesign is capable of mapping XML tags to paragraph and character styles, so that text can be imported into your document fully formatted. Taking full advantage of this option often requires database export scripting knowledge in order to get just the right “flavor” of XML exported from the database so that InDesign will work well with it.

Data Source How it can be used in InDesign
CSV Data Merge
Place as text and convert to table.
Use with 3rd party plugins*
Spreadsheet Place Excel sheet as a table to be able to edit in InDesign.
export Excel charts & graphs as PDF and place to preserve formatting.
Save the spreadsheet as a CSV
XML XML Import
Use with 3rd party plugins*
Database Use with 3rd party plugins*
*see plugin discussion on following pages for more information

Connecting Directly to a Database

If you work with a database and InDesign, why not allow them to interface directly? Chances are you’ve been spending countless hours getting data back and forth “the hard way.” While solutions to transfer data directly may be expensive “up front,” they save money in the long run. Let’s first break this down into the components you will need.

The first component to consider is the database itself. It can be in any location visible to the computer on which InDesign is installed; in other words, it can be on the same computer, on the local network, or on the Internet. You will need the necessary login and server information from your network administrator.

The database must support Open Database Connectivity (ODBC). Most modern DBMSs do, but you need to verify before proceeding.

Now we turn to the computer on which InDesign is installed. The second component is the ODBC Driver, which allows the computer to communicate with the database. This is built into the Windows operating system; unfortunately, Mac users must purchase them. One popular driver provider is actualtech.com;  their drivers cost $35 for a single user.

Once installed, you can use the driver to set up a connection on the computer to the database using the instructions that come with it.

Third comes the 3rd party plugin which allows InDesign to talk to the ODBC driver. The three plugins reviewed here are EasyCatalog, InCatalog, and Smart Catalog.

diagram of required components for database-to-indesign integration

Why Use a Database-to-InDesign Plugin?

So why should you invest in one of these plugins? Because they allow:

  • live-linked data: when data is inserted into your document from the datasource, it is linked. If the database is updated, all it takes is a couple clicks in InDesign to synchronize with the datasource and all values in your layout update.
  • bi-directional data flow: you can also force changes in your InDesign document to update the database—in other words, you can use InDesign as a data entry tool. This requires that your administrator gives you read/write access to the database.

When to Choose XML Import Instead of Plugins

While Database-to-InDesign plugins are great, they’re not always the best solution. In cases where (1) a live link to the data is not beneficial, and (2) the data must be prepared via scripting prior to import into InDesign, or (3) you wish to preserve formatting that may exist within the data as HTML (or some other format), then it may be wise to consider the XML solution. You may already be hiring and working with a developer to write custom server-side scripting to get the data into the right format for InDesign; chances are you don’t want to spend even more for a plugin that you may not need.

For example, consider a database that contains a document archive. The documents in this archive rarely change. Therefore, live linking to the fields in this database is not important. Furthermore, there may be formatting in the documents that you wish to preserve when importing into InDesign; XML is capable of preserving the formatting, while the plugins may not. In this case, XML is clearly the preferred method to choose.

3rd Party Plugin Review

There are a number of plugins on the market that provide dynamic links between a data source and InDesign. In my research I’ve found three competing plugins that, on the surface, all appear to offer similar options at comparable (rather expensive) prices. Searching the net led me to conclude that there was a real shortage of comparative studies on the relative merits of these three plugins, so I decided to embark on my own research. Here is a brief summary of each plugin, in alphabetical order:

EasyCatalog from 65bit Software. The only plugin to offer a truly simple, intuitive drag-and-drop interface. Connect to a datasource, insert placeholders into your document from the EasyCatalog Panel, apply formatting as you usually would to design elements, group related elements together into a “Product Style,” store your Product Styles in an EasyCatalog Library, and then populate copies of your product styles with records from your database. It’s also the most expensive plugin, if you consider the need to get the ODBC and Pagination modules—but in my opinion, it’s worth it.

InCatalog from EmSoftware. Although I make every attempt to be objective, I must point out that while the software may have been updated, the product manual has not been updated since 2004 and it still has instructions for InDesign CS. The only tutorial is written for XCatalog for Quark. The interface is extremely limited when compared with the other two plugins. In short, I do not recommend this plugin.

Smart Catalog. Designed to integrate with Woodwing’s Enterprise publishing system. Uses a rule-based system which is configured using dialog boxes. Connect to a data source using the Smart Catalog Panel, Define Formatting Rules and Rule Sets, then assign the rule sets to text frames in your layout to place the content into your layout. Harder to use than EasyCatalog, but quite powerful.

My Observations. First, a disclaimer. These are my own observations, based on the last several weeks of research and testing of these plugins. I may be guilty of misrepresenting or oversimplifying based on my inexperience, but unfortunately there don’t seem to be many other sources of information available.

EasyCatalog by itself is incapable of pagination or connecting to an ODBC database. You must purchase extra modules to add these capabilities. SmartCatalog is specifically for pagination; that’s really what it excels at. So for importing and formatting many pages of data quickly and easily, you get more bang for your buck with SmartCatalog. However, if you’re willing to pay the extra money, I believe EasyCatalog is the better product.

If you are working with a layout consisting of many non-threaded layout elements, EasyCatalog is what you need. SmartCatalog is intended for working with textflows. EasyCatalog is intended more for working with non-threaded layouts (unless you purchase and use the pagination module).

When using ODBC, EasyCatalog seems to grab a copy of the database once and store it in a local work folder. This is GOOD. It only updates the local copy from the real database when you ask it to. It seems Smart Catalog doesn’t keep a local copy; rather, it goes out to the database every time it needs data, and therefore is more prone to losing connectivity and puts a higher demand on your database/sever/network. I lost my connection to my database repeatedly when using Smart Catalog, while I never had a problem with EasyCatalog.

Updating Smart Catalog’s Rules will not update content already placed using those rules. The content must be deleted and then re-placed.***edit 9/14: this is actually not true. further testing showed that updating rules does infact update the data. My initial tests must have been flawed. Sorry about the incorrect information.***

Data can be easily sorted and rearranged in an EasyCatalog Panel. Also, EasyCatalog allows multiple panel views. Smart Catalog does not seem to allow this amount of control.

Smart Catalog’s installer (Mac) is an enormous download (145MB). It apparently includes all versions of the plugin, as well as a lot of components that are part of Woodwing’s Enterprise system which you may never use.

What’s worse, when I ran the Smart Catalog uninstaller (Mac), it didn’t actually remove anything. I even ran it twice to make sure. I’m left with a lot of extra files on my computer that I must manually remove.

The EasyCatalog installer weighs in at only 5.6MB and includes all the expansion modules. On the Mac, EasyCatalog’s installer does not ask for an install destination; in simply dumps the files in the default install folder for InDesign. If you have InDesign installed in a custom location they’ll land in the wrong place, and you have to move them into the correct folder. Once moved, they work properly.

All three plugins install as fully-functional demo versions that last for a trial period of 30 days. At any time you can purchase the software online which gives you a serial number, which you enter into the demo screen in InDesign to activate the full version.

In conclusion, Woodwing is a reputable software company which makes a powerful Enterprise publishing system used by many major magazine, newspaper, and other publishing companies. When integrated as part of the Enterprise system, I have no doubt that SmartCatalog is truly amazing. But if you’re choosing between EasyCatalog and the Standalone version of Smart Catalog because you wish to connect InDesign to your database in order to automatically update information in your layouts, I recommend EasyCatalog.

EasyCatalog Pro* InCatalog Pro* Smart Catalog Pro*
Developer
& website
65Bit Software
www.65bit.com
EmSoftware
www.emsoftware.com
Woodwing Softare
www.woodwing.com
Price $1299
(+$199 ODBC module)
(+$599 Pagination Module)
=$2097
$1200 $1500
Datasources supported:
CSV Yes Yes Yes
XML Requires XML Module ($499) No Yes
ODBC Requires ODBC Module Yes Yes
Assorted Feature comparison:
Controls format of data on import Yes, using Product Styles
and EasyCatalog Libraries
no Yes, using Formatting Rules
Mass import of data only with Pagination Module no Yes
Bi-Directional Data Flow with ODBC Yes Yes Yes
Additional Online Resources:
User Guide download on 65bit’s download page
(click here for direct link)
on EmSoftware’s site
(click here for direct link)
on Woodwing’s manuals page
(click here for direct link)
Tutorial Videos By Gabriel Powell, listed on
65bit’s home page
(click here for direct link)
none found on Woodwing’s site
(click here for direct link)
*for simplicity, only the Pro versions of these plugins is included in this comparison.  Less expensive lite versions of the plugins are available; for InCatalog and SmartCatalog, lite versions do lot allow ODBC. For EasyCatalog, Lite can still use the ODBC module but has a limited set of features available in the full Easycatalog Pro plugin. More information is available on the developers’ sites.

This post was originally written as a handout for the September Portland IDUG meeting, at which I presented. It is also available in PDF format.

, , ,

15 Responses to Database to InDesign

  1. Lisa Switalla September 9, 2011 at 8:42 pm #

    Paul, here’s a link to the Text Stitch plug-in I told you about at the meeting last night:

    http://www.rorohiko.com/wordpress/indesign-downloads/textstitch/

    Later!

  2. David Blatner September 12, 2011 at 12:52 pm #

    That’s strange about InCatalog; I have heard really good things about it. And its little brother, InData, is awesome for publishing data (though it doesn’t connect directly to a database).

    I was surprised you didn’t include DataLinker from Teacup: http://teacupsoftware.com/products/datalinker.html

    • Paul Erdman September 12, 2011 at 5:33 pm #

      Yes, I was rather disappointed with InCatalog. I did not review InData, and I have not worked with it, as I was looking specifically for plugins that provide database linking. The fact that its user guide had not been updated since 2004 was a real blow. But what finished it off was that both EasyCatalog and Smart Catalog can do so much more, at comparable prices.

      As for DataLinker from Teacup, I didn’t include it because I must admit I had forgotten about it. One reason I undertook this research project is because I didn’t know much about these plugins, and I’ve recently detected a great deal of local interest in data-to-InDesign workflows. Hopefully there aren’t any other significant plugins I forgot to include.

      So to be fair, it looks like I still have some work to do! Over the next couple weeks I’ll get a trial of DataLinker and give it a try. Then I’ll do another post with a rewrite. With a $500 pricetag I’m excited to see what DataLinker can do!

      So once again, David, thank you so much for taking the time to read through my blog posts and write comments. I really value your feedback. You’ve really helped me to improve my content—and in this case, you’ve helped me fill a gaping hole in my research project.

  3. Chris Ryland September 14, 2011 at 6:43 am #

    You should look at InCatalog (our linking/updating tool) + InData (our building tool) to these other products for a more balanced comparison. InData is a very powerful building tool, beyond the capabilities of either EasyCatalog or SmartCatalog in many cases.

    Yes, we’re going to be improving the documentation and tutorials significantly.

    • Paul Erdman September 14, 2011 at 9:35 am #

      Thanks for your comment, Chris. I would be happy to take another look at InCatalog and how it works when used with InData once you’ve made the new documentation available. I look forward to reading it.

    • Daniel February 13, 2014 at 7:04 am #

      Well, trying to find a solution that suits the needs of our company i came across the InCatalog. At first i was very pleased to see how easy a demo installation can be made (unlike EasyCatalog – i am still waiting for the download information email…) and the first impression of documentation seemed fine. But once we instaled it, had FIleMaker Pro running i faced the EXACT problems as described here in this article. I then noticed your reply post and promise of improving the documentation and tutorials “significantly”. This was almost 2 1/2 years ago. The documentation is still the same old one, based on a XPress / XCatalog Workflow. The tutorial files are FM 5 (!!!). We cant even take a look into those file anymore – thats how old these docs are.. Needles to say, we will not purchase this and try something else.

  4. Alfred Mosskin September 15, 2011 at 8:00 am #

    Really interesting to read you article, Paul! Thanks for a great research job.

    As I mentioned on LinkedIn this confirmed my opinion that EC is the best on the market, for what I am after anyway. I find the newly added “custom-field-funtionality” in EasyCatalog with live rules and references to other fields almost as groundbreaking as when GREP-styles popped in to InDesign.

    I will keep a look out for your updated test including DataLinker as well!

  5. Jörg Oyen September 16, 2011 at 8:02 am #

    Hi Paul,

    thanks for the database publishing plug-in update. Some good new perspectives.

    I have done a benchmark about desktop solutions, which supports InDesign and QuarkXpress Database Publishing (pdf) in 2009.

    My experience in actual project situations and as a result of my weekly @publishing #sprechstunde (consultation) service days, shows me that the way between data(information) layout -> output (print/web/mobile), works best, if all needed people, information, text and images, will take the same bus ride. This will save time. And, on a few rides, money. The following five magic questions:

    1. Do we all talking about the same structure for each production and output channel?
    2. Do we know all possible (dynamic) layouts / result pages and product styles and are we able to reproduce them for reconfiguration and enhancement?
    3. Did we get a simple sorting and grouping rule for an easy to handle production “check list” and cross check for used data in the layout?
    4. Are we sure that´s o.k. to transform data via layout only for one (device) specific output channel and that there is no reuse of latest content, used layout needed?
    5. Do we have resources left, to step between an automatic process, to get out handcrafted results for the needed output channel?

    are a good starting point for less pain points during the setup and production.

    map data>layout>output
    consultation topics

    next @publishing consultation hour

    -
    j.oyen

  6. Stefan Brandes September 20, 2011 at 1:33 am #

    Nice research you’ve done there, Paul. Thank you for that piece of work.

    This matches my experiences. And it is why I decided to set a focus on EasyCatalog consulting, training and integration. It is the usability, the possibility to build product presentations and views in seconds and use them as template. And to get a plugin that allows you to use manual pagination by drag and drop as well as automatic pagination by rules.
    By permanently developing new features – like Alfred described the custom field functions – the guys at 65bit do a great job to make EC even more powerfull without the need of coding skills.

    But I will stay open for new aspects and plugins you will test in the future. Do you? ;-)

    • Paul Erdman September 20, 2011 at 7:23 am #

      Yes, I want to be sure to keep options open. Easy Catalog is, after all, quite expensive, and some clients might not need the extra expense. Just yesterday I recommended Smart Catalog Lite to a client at $300 per seat because it will do all they need (import data from CSV files quickly into a prepared template) while with EasyCatalog they would need Pro along with the Pagination Module, which would cost roughly $1,800/seat. If they are certain they will have no other data needs in the future, then I would recommend they save the money and use Smart Catalog.

      I’m glad all of you appreciate the research. I myself still have much to learn and will continue to study and write about the plugins. The next installment will add information I’ve learned about these three, add information about their light versions and what they do and don’t include, and of course add DataLinker from Teacup Software. Unfortunately I don’t have a estimated completion date yet. I’m also hard at work on my regular work projects and preparing for the next IDUG meeting (a “Grepper’s Grab Bag” panel discussion) so it might not be until December, but I’ll see if I can squeeze it in sooner.

  7. Henry Lee October 20, 2011 at 2:42 pm #

    Thanks Paul for your research Actually I am researching to these 3 plugin with my company’s annual report project. Your research supports my decision to recommend my team for using Easy Catalog because what I need is more complex SQL support with store procedure support. Also, bi-direction update is one nice feature for our desktop publishers to update the report. I think it worths the price in my case because it really save my annual report team time.

    Actually, for our reports, we use autoprice for quark 6 now but now we plan to move everything to InDesign. (Since there are too many restriction with this old quark 6 plugin) I know they have database plugin for InDesign too. If you or someone can give a review on that, it will be good.

    For woodwing, I rather use their smart style plugin instead of using their database plugin. But they really have a good web publishing support with plugin for the current favor CMS (we writes our own) like Drupal, wordpress and etc. So, it might be a good for some company but certain not in my case.

    For datalinker from teacup software, we are now using it with our company fund performance monthly report. Probably using it for some newsletter in the coming future. As what I feel, Datalinker is almost like EasyCatalog lite.

    It is a nice and cheap plugin that really handy for our designer to update more than 1700 rows of funds with 20 dynamic values in each column in it. It support the joins sql pretty well for 2 to 3 tables.

    I am quite happy with their data Merge function as it really keep the character and paragraphic style, and even object style and flow through all the link box with one menu click but be careful with text style with superscript and subscript. It won’t keep the style because it won’t keep it.

    Every single SQL is stored in one text file (datalinker.xml) in Mac. So, you can back that up and change them in the file instead go through its interface to change it. Also, use this file to move from one to another machine and InDesign.

    Only a few draw back is that they seems don’t support store procedure. Also, usual database function is not all supported (If I am wrong, please correct me). It will keep the style sometime but most of the time, it won’t, just as what you said. Also, it is a little buggy when the tag cannot found its value. It will not give you what tag or what SQL generate problem. Eventually, you have to debug through SQL log in OBDC log file.

    For field formatting, they support text, number, currency and regexp but I want them to support different dollars sign and number format like French style.

    But it’s ok, the price are good and it works what I have to do.

  8. Creative Agency February 1, 2013 at 2:41 pm #

    Hi,

    Would this work with a PHP (MySQL Database)?
    I would need to link my catalogue to wordpress but also use it in INDESIGN.

    Regards,

    Pierre-André

    • Paul Erdman February 4, 2013 at 4:33 pm #

      MySQL is ODBC compliant, and therefore would work with the 3 plugins I reviewed in my article. I’m not sure how you intend to fit PHP into the picture; if you’re hoping to code some sort of PHP/Wordpress interface, that is beyond the scope of what the plugins do, as far as I know; they provide an interface between the database and InDesign. I’m guessing the component to link your catalog database to your wordpress site (and thus its database) would be a separate product (perhaps a wordpress plugin coded in PHP), not related to the InDesign plugins.

Trackbacks/Pingbacks

  1. This Week in InDesign Articles, Number 67 | InDesignSecrets - September 12, 2011

    [...] of data, this is a good overview of the various options available for getting database (and spreadsheet) data into InDesign. That [...]

  2. Case Study: a Data-to InDesign Workflow in which Smart Catalog Lite Excels » Creative Progression - September 23, 2011

    [...] Part 1 of my research into database-to-InDesign workflows I reviewed three popular third-party plugins. In this case study I’d like to pay a little [...]

Leave a Reply