InStep: Excel to InDesign Via XML
Excel is a great application for capturing and managing data. But for maximum flexibility, XML is often a more robust choice for InDesign workflows.
Most InDesign users know that Excel spreadsheets can be placed into an InDesign document, and rendered as native InDesign tables. This is an excellent solution when you want a table-based structure in your final design. But what if you don’t want to mimic a spreadsheet?
Recently I was creating a directory of information based on data from a spreadsheet, and the client wanted the information to “flow” in a layout, instead of being listed as a table. In the past, I’ve saved Excel files as .csv or tab-delimited files, and then run some creative GREP searches to achieve the desired result. But that still left me with a lot of manual formatting to do. I love nested styles in InDesign, but they require very consistent content, with “triggers” that allow me to determine how content within a paragraph is formatted. If you don’t have consistent content from one paragraph to the next, nested styles just don’t work.
Now you might say that Data Merge would be a logical choice, and it works well for some projects. But Data Merge alone doesn’t allow you to create flowing text, and it requires each field to be a separate text frame. Loic Aigon wrote a handy script called InlineMerge to overcome this limitation, but I still wanted a bit more control.
XML gives me the ability to import content into InDesign in a story format, and provides the control that I need to style content based on tags in the XML file, instead of specific characters in the content.
Editor’s note: See issues 23 and 24 for a full rundown of XML workflows.
. . . .This article is for members only. To continue reading, please sign in, or sign up for a membership today. Thanks for supporting CreativePro!
BECOME A MEMBER
CreativePro membership keeps you up-to-date with the technology, solutions, and resources to strengthen your professional development.
For just $6.50/month (billed annually), you’ll get access to valuable benefits, including:
- 12 monthly issues of CreativePro Magazine, filled with practical, real-world tutorials written by experts
- Downloadable resources including templates, fonts, scripts, design assets, cheat sheets, and more
- Hundreds of members-only tutorial and tip articles
- Top Tips for InDesign, Photoshop, and Illustrator ebook collection
- Discounts on events and books
- and more...
Hi Chad! Just for fun!
https://www.youtube.com/watch?v=VEqcgKEr6lk
;-)
That’s very cool Obi-wan! Can you explain what the GREP find/change and the AppleScripts are doing? You have to come to the Creative Developers Summit at PePcon next year and geek out with other automation experts!
Hi David, as the client provides each year the same four Excel files with his updates and as his files are “truly” bad written with lots of errors in data, the idea was to play the game in this way: export the files from Excel in .csv, convert these new files directly from ID in .xml, correct their errors (data, xml syntax) in an xml/text editor as TextWrangler playing with lots of Grep queries placed in [AS] files called in the soft using amazing grep codes not really possible in ID; finally import the .xml files in ID and finally adjust the layout with some other simple grep queries in batch with Multi/Find-Change. As you can see, no xml plugin and no too “hard” xml treatments in ID. It’s truly like a simple game to play! ;-)
Of course, I’ll be very glad to see you at Pepcon (Mike, Anne-Marie, Chat and others too! ;-) ) but I’m living in France! Maybe one day! =D
Thanks for sharing that with us Obi-wan! I don’t know if you recorded it without sound on purpose but I think it was genius. A picture er… video is worth a thousand words! Great example of an elegant solution to a time consuming and labor-intensive process!
Thanks Chad! ;-) Maybe a new Oscar after “The Artist”! Aha! … =D [ … Between us, I preferred without sound: I’ve the same voice as Daffy Duck! =D=D https://www.youtube.com/watch?v=7qh7qu9hVfo ]
Hi Kenobi,
I’ve seen your video tutorial is really cool stuff! Thanks for sharing this link.
:)
;-)
Great write-up, Chad. One question, how easy is it to update the InDesign document if a change is made in the XML/Excel file? I do some work with pricing, and am wondering if this technique be usable for paginating price-list tables from a spreadsheet file.
I didn’t use this method when I wrote this tutorial Brandon, but when you place the XML file, there’s an option to create a link to the XML file. This becomes a link like any other so in theory if you were to get a new excel file and perform the steps to tag it and export to a new XML file overwriting the old one, InDesign should pull in the new data and update the content with that new data.
**Mind**Blown**
Glad you enjoyed it Eugene! ;-)