MySQL Database to Adobe InDesign

Question

Not really sure if this is the correct place to posting this, but thought someone here might have a little experience with this.

We have a MySQL Database containing a number of products (well, 3,200 actually). For obvious reasons we don't want to have to retype everything into InDesign to produce our catalogue for the new season. Does anyone have experience with building something similar to the Microsoft 'Mail Merge' functionality in InDesign?

There's also another caveat. The items in the DB have a unique serial number, which is 5 digits in length. We then have three coloured bars which are colour-coded according to the last two digits of the item's serial number. For example, the number 12345 would have the three vertical bars coloured as red, green, red (in our system, 5 represents the colour red, and 4 represents the colour green).

Does anyone know if this kind of functionality is available in InDesign? I was thinking that it might be possible to generate a PHP file that outputs everything in a PDF format, but since we might need to edit a few details, we would ideally like this information to be readily available and editable in Adobe InDesign.

If anyone has experience with this, I would certainly welcome your comments.

Answer 1

Both options above would work to bring your data in but they miss your other question regarding the colors. I am assuming when the data is imported you want to automatically display the colors according to the unique serial number, right?

So the answer is in two parts: 1. Yes, you can import the data as XML. This would be the easiest and most flexible way to do it. Data Merge is OK for short documents, but for lots of data it would be a bear. Data Merge ends up creating one text frame for each data record. That would be a lot of extra work.

XML on the other hand can be imported to flow through the document and fill pages automatically and format itself based on Paragraph and Character styles. The book: A Designer's Guide to Adobe InDesign and XML will teach you how to import the XML.

Question 2: "How do you generate the colored chips" is a bit more involved. You'd have to write an XSLT to load a graphic based on the numbers in the serial number. We did something similar to this with stars in a stock portfolio fact sheet by using an xsl:choose routine to pick the correct number of stars based on the data in the XML. Such as:

<xsl:choose> 
<xsl:when test="name()='stars'">
<!--    <xsl:copy><xsl:apply-templates/></xsl:copy><xsl:text></xsl:text>-->
<xsl:choose> 
<xsl:when test="current()[.=2]"><stars>&#72; &#72;</stars>
</xsl:when>
<xsl:when test="current()[.=3]"><stars>&#72; &#72; &#72;</stars>
</xsl:when>
<xsl:when test="current()[.=4]"><stars>&#72; &#72; &#72; &#72;</stars>
</xsl:when>
<xsl:when test="current()[.=5]"><stars>&#72; &#72; &#72; &#72; &#72;</stars>
</xsl:when>
<xsl:otherwise></xsl:otherwise> 
</xsl:choose> 

You could create a similar routine and even load graphics from the hard drive or server instead. When you import the XML into InDesign you load the XSLT in the XML import options dialog box. This could do the work for loading your color chips based on the serial number.

In addition to being able to build the graphic chips, the XSLT can help you structure the data content too by inserting paragraph returns and other boilerplate text and whitespace as needed. I like to use <xsl:text></xsl:text> to insert the whitespace that I need for InDesign. It works like a champ.

Many times XML files will have no whitespace or too much. The XSLT method will allow you to add, strip and control the whitespace in your resulting layout, as needed.

Hope that helps.

Answer 2

InDesign has very powerful XML features built right in. And, you can make them more powerful using XSLT.

I detail the entire XML workflow in my book "A Designers Guide to Adobe InDesign and XML" available from Adobe Press. Or, you can see my Lynda.com recordings for "Dynamic Workflows using Adobe InDesign and XML"

I wouldn't use the Data Merge function for anything other than a small catalog, it's too hard to edit the resulting file.

Using the flow method of importing XML you can create one sample layout using the proper XML structure, then import the import using the Clone and filtering options.

If you need to rearrange the data elements you can use an XSLT upon import or generate a different XML structure by creating a query in MySQL and exporting the data to XML again.

By using the XSLT upon import, as described above, you can replace the numbers with graphics. We did this with a fact sheet where a number, such as 3, was replaced with three star graphics.

Answer 3

You could also try out InDesign's data merge feature from a csv file.

Answer 4

I know this is an old thread, but for anyone looking for another solution to the color chip part, you can use GREP in the Paragraph Styles plus Character Styles to accomplish this pretty easily.

For instance, use the aforementioned XML workflow to drop the serial number in your location where you'll want the color bars. Apply a Paragraph Style, let's call it 'Color Bars', to the template text here, and in the Paragraph Style's 'GREP' options have it look for the number 3, and apply a Character Style of 'Red' to it, where that character style uses an underline format, scaled and styled to your needs. And apply that same red to the number 3 so that it vanishes into the underline.

That's the basic idea, fiddle with the font (for instance maybe you need to use a fixed-width font), scaling and font size to get the spacing and placement you're looking for.

And maybe use the Paragraph Style's 'Nested Styles' functionality to apply a 'white' character style to the first three letters to get those to vanish into the background.

Answer 5

you can use www.porky.io for JavaScript database access.

And there's no need to use an exchange file format like xml (but of course it is possible), you can query directly via SQL.

For database access there's a sample included via php pdo. mysql should be easy to integrate...

Answer 6

First you must prepare the data in XML format, export to XML and then, import XML into the indesign document(View -> Structure; Import XML ...), which previously prepare in InDesign

InDesign works with the XML data, saving much time

Answer 7

You can try iziDBConnect. This add-on for Adobe Indesign has a trial version. You can download it from Adobe Exchange. It connects directly to MySQL. http://www.izidbconnect.com/ Next, maybe, you should need a little script to transform some text fields into an image.