This article appears in Issue 124 of InDesign Magazine.
You can export data directly from Excel or a database, ready to flow as preformatted text onto your page.
When you have a lot of data in a spreadsheet (such as Excel) or a database, you have many options for placing it into InDesign, but I want to offer you one that you probably have not considered before: tagged text. This method lets you apply tags to your data so that you can flow it into InDesign completely formatted with paragraph styles, character styles, special characters, and more.
In this article, I’m going to use the example of creating a directory. However, this technique could be used for many different types of documents, including signage or catalogs. I probably would not use this technique if I wanted to:
- Import the data as a table (I could just import an .xlsx file for that);
- Create a catalog or ads with lots of images (you’d probably want to use a plug-in such as EasyCatalog or InData for that);
- Use a basic template to create many versions of the same thing, such as name tags or a flyer (I’d use InDesign’s built-in Data Merge for that).
However, if you have a ton of data that you need to format quickly into a long story, there are few techniques as fast and powerful as tagged text.
What Is Tagged Text?
Tagged text is a way to describe text formatting using codes, sort of like how HTML describes text for a web browser. I say “sort of” because tagged text is very different—it has its own weird and very strict rules. The easiest way to get a sense for InDesign tagged text is to format a small bit of text in a text frame,
choose File > Export, and then choose InDesign Tagged Text from the Format pop-up menu of the Export dialog box. InDesign asks you if you want Verbose or Abbreviated tags (Figure 1), and you almost always want to choose Verbose, which is much easier for humans to read. In most cases you will also want to choose ASCII encoding, unless your text contains glyphs that require Unicode.
FIGURE 1. Exporting InDesign tagged text
Once you export your tagged text file, you can open it in a text editor, such as BBedit, TextWrangler, or NotePad. (You want to be sure to use a text editor rather than a normal word processor, which might break the code by doing things like changing straight quotes into typographer’s quotes.) If you only applied basic formatting to the text in InDesign, the tagged text should be relatively easy to read (Figure 2).
FIGURE 2. Simple tagged text
Of course, InDesign can read tagged text as well as write it. Our task is to make Excel (or whatever spreadsheet or database you’re using) write tagged text that InDesign can then read. It’s not hard, but it does require some geeking out and an obsessive attention to details.
Make the Template
The first step is to make a template in InDesign that looks like what you want your spreadsheet or database to spit out. It doesn’t need to be long, but you should make all the paragraph styles and character styles; ensure everything is formatted exactly the way you want it to end up (Figure 3).
FIGURE 3. This is the sample template for our directory.
In our example, we’re making a directory that contains all the data from an Excel spreadsheet (Figure 4).
FIGURE 4. This is the data we’ll be using for the directory.
Next, export your sample story as tagged text, and open it in the text editor. It will probably have a bunch of extra tagged text that you won’t need, such as tags that begin with “FeatureSet,” “ColorTable,” or “DefineParaStyle.” You can ignore those.
The tags you care about are the ones directly affecting text, such as the tags that start with “ParaStyle,” which is (obviously) the tag that applies a style to that paragraph of text (Figure 5). For example, in the parlance of InDesign’s tagged text, “<ParaStyle:Name>” will apply a paragraph style called “Name” to the paragraph.
FIGURE 5. For our purposes, we’ll ignore the text highlighted in blue and focus on the tags highlighted in yellow.
After exporting your “sample template” and analyzing the exported tagged text for relevant tags, the next step is to build those tags into your spreadsheet or database.
More About Tagged Text
When you’re tagging paragraphs with styles in tagged text, you do not need to “close” the paragraph style tag—that is, the tag is only at the beginning of the paragraph. Some other tags do require closing tags. Unfortunately, InDesign Tagged Text is not well documented, and to figure these things out you often just have to export sample text to test it. However, if you find yourself curious to learn more, you can find some good insights in this PDF file.
Calculating Fields
In general, the way to combine your data with formatting tags is to use calculation fields. In a database, these might be invisible fields that grab data and add the tags. In a spreadsheet, these are fields that include a function that concatenates the data and the tag. These functions can live on the same worksheet as your data or on a different worksheet.
In my example spreadsheet, I’ve created two fields with formulas that help me export the tagged text. First, I made a formula that builds my “alpha” letters—that is, a letter of the alphabet that appears before each new section of the directory. I did this by comparing the first letter of the person’s last name with the one in the row above it (Figure 6).
FIGURE 6. This Excel formula compares the first letters in two cells and—if they’re different—adds an “alpha” character.
Next, in a new worksheet (in the same file), I added a cell that contains this formula:
=”<ASCII-MAC>”&CHAR(13)&”<Version:14>”
This is the beginning of the InDesign Tagged Text, and types two lines of text. The ampersands (&) mean add the text together, and the “CHAR(13)” means add a carriage return. Note: If you are using a Windows version of Excel, you would use CHAR(10) instead.
The next cell down is where all the rest of the tags come together—using that same basic structure of ampersands to bring bits of text together, including data in other cells, special characters, and tags (Figure 7). I admit it appears scary at first, but it’s actually relatively straightforward if you take it piece by piece.
FIGURE 7.
The formulas on the second worksheet combine data from the first worksheet with tags and special characters.
Again, I’m using Excel, but lots of spreadsheets and databases let you do similar calculation fields that bring data together with text. Of course, the particular formulas to use may be different in those other apps.
Understanding the Codes
Let’s look at each piece of what we see in cell A2 on the previous page:
|
= |
Formulas always begin with = |
|
IF(Directory_Names!J2<>””,” <ParaStyle:alpha> “&Directory_Names!J2&CHAR(13),””) |
I’m using several IF functions, which always include three parts inside parentheses, separated by commas. This first one says if the “alpha” cell (J2) is not empty, then include that letter along with some tags and a carriage return. |
|
&”<ParaStyle:Name>” |
Add a tag to apply the paragraph style “Name” |
|
&IF(Directory_Names!C2=”yes”, “<0x2611>”,”<0x2610>”) |
If cell C2 equals “yes,” then include the Unicode character for a ticked checkbox; otherwise, include the empty checkbox character. |
|
&CHAR(9)&Directory_Names!A2&” “&Directory_Names!B2&CHAR(13) |
Add a tab, followed by the first name, then a space, then the last name. End it with a carriage return. |
|
&”<ParaStyle:Street> “&Directory_Names!D2 |
On the next line, set the paragraph style to “Street,” and add the data from D2. |
|
&IF(Directory_Names!E2<>””, CHAR(44)&” “&Directory_Names!E2,””)&CHAR(13) |
If E2 is not blank, then type a comma, a space, and then the address from E2. |
|
&”<ParaStyle:CityStateZip>”& Directory_Names!F2&CHAR(44)&” “&Directory_Names!G2&”<0x2002>” &Directory_Names!H2 |
On the next line, set the paragraph style; then add the data from F2, followed by a comma and space, then G2, followed by an en dash, followed by H2. |
|
&IF(Directory_Names!I2<>””,” “&Directory_Names!I2,””) |
Finally, if I2 is not blank, then add a space followed by I2. |
Exporting to Text
Once your cells are all set up properly, you need to export a plain text file, which InDesign can import. There are various ways to do this, but the method I usually use with Excel is:
1. Select and copy the tagged text calculation cells.
2. Create a new document, and choose Edit > Paste Special.
3. Choose Values, and click OK (Figure 8).
FIGURE 8. Paste Special lets you convert the cell data into actual values.
4. Choose File > Save As, and choose Tab Delimited Text (.txt) for the format.
5. Open the file in a text editor, and use Find/Change to get rid of the quote marks. (I’m not sure why Excel places quotation marks at the start and end of each paragraph; it’s really annoying!)
6. Save the file.
Again, if you’re using a database or some other tool, the process will be different. The goal, though, is the same: a plain-text file equivalent of the data.
Now you’re ready to place your text file into InDesign. You can do that with the normal File > Place feature. InDesign automatically sees that the file is tagged text and “interprets” it, converting the tags into formatting (Figure 9).
FIGURE 9. When you place the tagged text file, InDesign converts the codes into styles.
Tagged Text with Super Powers
InDesign Tagged Text is a powerful way to convert plain text with codes into formatted text… but it’s limited. For example, you cannot include anchored frames or images. Fortunately, the folks at Em Software developed a tool called xTags, which is like tagged text on steroids! With xTags, you can not only automatically generate inline or anchored objects and import images, but even change text based on customizable macros, or apply master pages to each page. (For example, you could automatically apply a “chapter opening” master page to any page that has a “chapter title” paragraph style on it.)
From Data to Delight
It’s important to test your files carefully, because even a tiny error in the calculations or tags can cause major headaches—either the whole import will fail, or the text will be imported incorrectly.
However, when all the bugs in the formulas have been worked out, and you get the process perfected, it’s a joy to export plain text data from your spreadsheet or database and have it (magically!) turn into beautifully-formatted text in InDesign.
Commenting is easier and faster when you're logged in!
Leave a Reply
Recommended for you
Illustrator Downloadable: Tropical Shadow Overlay Kit
Downloadables are an exclusive benefit for CreativePro members! (Not a member ye...
Illustrator Downloadable: Summer Citrus Pattern Set
Downloadables are an exclusive benefit for CreativePro members! (Not a member ye...
InDesign Downloadable: Tables SuperGuide
Downloadables are an exclusive benefit for CreativePro members! (Not a member ye...
Is there a COMMENT tag syntax for tagged text? We’d like to include some file and date and internal information that is not to be printed, but kept as //comments in the file? There seems to be no tag dictionary/documentation that we can find. THANK YOU!
It’s a great question, but unfortunately I don’t know of any way to put comments in. Maybe someone else here does.
Shoud add that he uses a text editor (probably BBEdit) to run the GREP searches. On the Windows side, EditPad Pro would do well.
Thanks Lindsey, I will definitely check that out.
We have another series of publications with many small, repeated tables For this we have a contractor who imports the tables from Excel to ID, exports the ID pub to IDML and uses GREP to apply the styles and other fixes before reopening in ID. Once the GREP searches are formulated, production is very quick–about half a day to first proofs.
Hi David,
I read that article hoping it would provide some shortcuts to a very laborious job I repeat regularly. Let me describe it if you don’t mind and you might suggest some other approach to streamline the process, if you could please.
In InDesign, I produce product catalogues which I import as tables from Excel. I retain them as tables to make use of the cell border rules which outline columns when printed. I give a cell style and paragraph style to the entire doc, but then I have to laboriously go through it line by line adding additional paragraph and cell styles to specific rows to highlight them. There can be up to 5,000 rows in one file so it takes a lot of time to process. Is there a quicker approach (my mind is melting with the tedious nature of the job!!).
@Denis: If you’re trying to set particular cell styles for individual rows in a table, another option would be to mark those rows in Excel, and then use this script: https://indesignsecrets.com/setting-table-subhead-rows-cell-style-free-script.php
David thanks for that suggestion, it sounds like a definitive fix for my problem. I’m doing a system upgrade and will get to try that in the next week.
I hope to report back positive results then!
Denis, Have a look at SmartStyles from Woodwing. I have used it for years to format large, complex, repetitive tables. SS will apply the styles and the formatting at the same time and will also apply geometry.
It was premium David and thank you I will try that now.
Ok, so I signed up for InDesignSecrets. Finally got some time to look at it. Logged in and am looking for this article…
Automate Text Formatting with Tagged Text
By David Blatner August 1, 2019
I just cannot see on the webpage how to view the actual article, links to Magazine and Tagged Text just go round in circles – Frustrated and unimpressed!
Denis, did you sign up for a free membership or a Premium membership? Magazine articles are available for premium members. If you did, please try logging out and back in (sometimes the site doesn’t register your premium membership at first). If that still doesn’t work, please write to su*****@*************ts.com and they’ll fix you up!
Thanks for this article. Tagged text has been the basis of my book publishing workflow for the past decade or so, as a sort of lazy alternative to a full XML process. I wasn’t aware of Xtags; it can do far more than my process in terms of composition, but the things I work on are primarily running text — i.e., no complicated layout required.
I add paragraph-level tags to manuscript in Word, and my InDesign templates/layouts have paragraph styles corresponding to the tags (and character styles corresponding to inline formatting in Word). From Word, I save as tagged text via a custom macro, do QA and some buffing in BBEdit, then import completely formatted text into InDesign. In InDesign, more or less all I have to do is fine-tune. (Tables imported via tagged text are an exception: they require hand-building in InDesign.) And when a publication is finished, I can export tagged text and then process it, generally back into Word but also into XML-like text.
The hardest part of the process is the QA in BBEdit, but InDesign’s tagged text import filter helpfully points out problems, by line number, in imported files, so no great knowledge is needed. The production team that I led at a small educational publisher had little difficulty with the process.
Someone who’s really good with Word macros and grep might find my macros and scripts rudimentary and kludgy, but generally no one looks under the hood — they just follow the process and spend very little time formatting in InDesign.
So Geekie, But SOOOOOOoooo Cool!
I have an unrelated question, but I wonder if somebody has some insight. I’ve recently received “heat” graphs generated in Excel for incorporation in InDesign layouts. In the past, I did the math and figured out the percentages and recreated the graphics in Illustrator, but I’m wondering if there is an easier way to actually use and manipulate the Excel information. Any thoughts?
Nita: Well… check out the crazy/amazing technique that Colin Flashman came up with: https://indesignsecrets.com/making-heat-maps-with-grep-and-data-merge.php
See also
https://forums.adobe.com/message/1304548
Thanks, Chris. I remember that from years ago. And this has been a problem for years for myself as well.
To get around it we exported as RTF file which the designer would import with no problems. But after CS5.5 came out, the RTF files did wacky stuff in InDesign. For some reason, when importing the RTF file, Indesign was keeping the RTF’s Word H&Js (or whatever Word calls them) instead of using InDesign’s. That was never fixed.
Dwayne – donkey’s years ago (CS3 days), I regularly did imports of large InDesign Tagged Text files, which were very sensitive to the line breaks being a particular combination of carriage return/line feed (CR, LF or CR+LF, corresponding to the then-standard Mac, Unix, Windows line breaks).
Could that explain the ‘other people’ problem that you’re seeing?
Chris–no problems so far as the line breaks.It’s basically text and heads and the only returns are after the element (i.e., after a head, at the end of the paragraph).
InDesign on some people’s computers don’t recognize the in the head of the file. I can do a file and it works properly on my machine and two other machines in the shop. But there will be one or two machines in the office that it won’t work on. Then there will be files that won’t work on mine, but will work on the others.
It’s baffling.
Yes, that’s a great point: tagged text can be very finicky about Mac vs. Windows! Something about the encoding. It’s a little unclear to me what’s going on technically, but it has to do with the encoding.
What I do is flow it into via xTags InDesign with my Quark tagging (which is easier). I then export the InDesign file as a tagged ID file (not an xTags export file).
Mainly because the designers don’t have xTags and need a tagged file to work with, so I give them a tagged ID file (export from ID as a tagged file).
Checked with xTags and they said no problem on their end. Once it’s flowed into InDesign and exported as a tagged ID file, it’s a problem with either InDesign not recognizing the tag.
Again–xTags is only used to import into InDesign originally. Then the ID file is exported as a tagged InDesign file.
It’s weird how some think it’s a regular text file and not a tagged file and don’t recognize the
David–I do a lot of tagging, and come across an issue that someone else can’t import the tagged text file, even with the tag. When they import it, it comes in with all the tags. Sometimes I can resave the .txt file, but then someone else can’t import it.
Is there some reason why sometimes the tagged file can be imported and it works, and other times it comes through with all the tags?
As a note, I import tagged Word file into InDesign via XTags then export as a tagged .txt file (tagged for InDesign) for the designer.
@Dwayne: xTags has its own tags exporter, so if you’re exporting with that then I’m not sure other people (who don’t have Xtags) will be able to read them. If you’re having troubles with xTags tagged text files, you’ll probably need to contact Em Software.