Automating Adobe InDesign CC 2015


Excel File to inDesign (Not Table)

Monday, July 25, 2016

Excel File to InDesign

A recent inquiry on the InDesign Scripting Forum under the heading "Script or Paragraph Style", asked:

I have an Excel Spreadsheet with Business Name, Phone, and Category...I want to format this info (over 600 entries) into an almost "phone book" like layout. 3 columns, Bold first line, tabs: with dotted Leader, regular typeface font phone number, next line category: italic. Link so: This Business Name ......... (555) 555-5555 Financial Services I also need to adjust spacing between each (space after paragraph), kerning between Business Name and beginning of "..." (and after). Paragraph Style isn't doing it....it's styling the whole entry as the first line. Do I need to build a script?

The response to this inquiry was amazing, ranging from using data merge to grep and almost everything in between.

This is the great thing about working with InDesign. It just gives us so many options.

The support for Excel in InDesign focuses on importing the file as a table. But for this person's need, this will not do. For me, a simple script can do the work without a lot of effort, with the option of using the same process for similar projects in the future.

For the purpose of demonstration, I wrote a sample script in AppleScript. I am hoping the user is on a Macintosh. If not, someone knowing JavaScript (ExtendScript) can put the same functionality into a script that will run on both Windows and Mac. (I might decide to do this at a later date.)

Preparation

First, you need to save the Excel file as tab/return delimited text. (This can be done without the need to add an extension to Excel.) Then set up the InDesign document.

Creating the InDesign Document

Create the InDesign document being sure to include Primary Text Frame .


New Document Dialog in InDesign

Text Styling

The most demanding part of the process is setting up paragraph and character styles for the text.

The document will need a paragraph style that determines the font and basic styling for the entries. The entire entry for this directory will be styled with one paragraph style, using character styles to style each element within the entry.  The paragraph style will set the position for the tab (right aligned) and will set space above (or below) as needed between the individual directory entries. The character styles will inherit the styling of the paragraph style and will add their unique styling attributes. The only entry element that will actually be set in this style (without a character style override) is the telephone number.

Character Style Style Attribute Description
Bold Bold Will apply to the name in each entry
Leader Size Smaller point size than paragraph style to be used for leaders
Description (Italic, Size) To be used for second line of entry; can be set italic and maybe a smaller size.

Now that the individual styles are created, set up the nested paragraph style.

Open the paragraph style dialog for the paragraph style. In the dialog you will see an entry for Drop Caps and Nested Styles. Click on this. Click on the New Nested Style button to create each of the separate stylings. The first Style will set the name element to bold using the Bold character style and will be delimited before the tab (up to). The second style entry will set the styling for the tab element using the Leader character style and will include (through) the tab. The third style entry will set the style for the telephone element and will use the [None] character style (no character style applied) and will be delimited by a forced line return (Forced Line Break). Finally, the fourth style entry will style the second line of text (through 1 Sentences) using the character style Description.

In the document, you will want to create a sample entry to make sure your styles are exactly the way you want them. Make sure you use a forced line return (Shift+Return) between the telephone number and the second line of text. Apply your paragraph style to the text. Make whatever changes to the styles as needed. When you have it looking the way you want, you are ready to place the text. 

Save the InDesign document.

The Process

With the Excel file saved as a tab/return delimited file, and the InDesign document prepared, you are ready to automate.

Open the editor for AppleScript (AppleScript Editor found in the Applications folder, most likely in a subfolder named Utilities). Copy the script below and paste into the editor's text entry window.
Click the Compile button at the top and the text will change from plain text to styled.

Make sure the name of the paragraph style in the script (paraStyleName at the top of the script) is the same as the name you gave your nested paragraph style in the document. (Make sure it is exactly the same, including capitalization.)

In InDesign, make sure you have an insertion point selected inside the text box on the first page of your document. (You have removed the sample text you used for checking styling.) Next click on the Run button. The script will ask you to select the tab/return delimited text file. Sit back and watch the magic.

The Script

Directory Text

with timeout of 300 seconds --5 minutes
   set fileRef to missing value
   set paraStyleName to "NestedStyle"
   try
      --make sure user has an insertion point selected in the document
      set insertRef to getSelected()
      --have user choose file and read it
      set origText to readFile()
      --convert text from one long flow of tab delimited paragraphs to ready for import
      set newText to convertText(origText)
      --place text in document and style; assumes styles needed are in document
      placeText(insertRef, newText, paraStyleName)
      on error errStr
      display alert "Error: " & errStr
   end try
end timeout
--HANDLERS
(*Place text: set contents of insertRef to newText and style
Assumes document has nested paragraph style as defined with paraStyleName*)
on placeText(insertRef, newText, paraStyleName)
   if newText is "" then
      error "Error converting text"
      return
   end if
   tell application "Adobe InDesign CC 2015"
      --make sure we have a paragraph style named as defined with paraStyleName
      tell document 1
         set styleRef to paragraph style paraStyleName
      end tell
      set contents of insertRef to newText
      set storyRef to parent story of insertRef
      tell storyRef
         set applied paragraph style of paragraphs to styleRef
      end tell
   end tell
end placeText
(*Reads the file chosen by the user. Path to file is set to the desktop by default.*)
on readFile()
   set uPrompt to "Select tab/return delimited file"
   set dlocation to path to desktop from user domain
   set theFile to choose file with prompt uPrompt default location dlocation without multiple selections allowed
   set fileRef to open for access theFile
   set fileSize to (get eof fileRef)
   try
      set textStr to read fileRef from 1 to fileSize
      close access fileRef
   on error errStr
      close access fileRef
   end try
   return textStr
end readFile
(*Converts tabbed paragraphs into lists wich are added to a Master List.
Adds each item of the Master List (thisList) back into a string while replacing,
the last tab with a forced line return ("\n").
A regular line return ("\r") is added at the end.
The strings (text) are added to a single story flow.*)
on convertText(textStr)
   set theCount to count of paragraphs of textStr
   if theCount = 0 then error ("No paragraphs read from file")
      set oldDelim to AppleScript's text item delimiters
   try
      set AppleScript's text item delimiters to tab
      set masterList to {}
      repeat with i from 1 to theCount
         if paragraph i of textStr is not "" then
            set end of masterList to (text items of paragraph i of textStr)
         end if
      end repeat
      set AppleScript's text item delimiters to oldDelim
   on error errStr
      set AppleScript's text item delimiters to ""
      display alert "error " & errStr
   end try
   set newText to ""
   if length of masterList = 0 then
      error "masterList not created"
   end if
   repeat with i from 1 to length of masterList
      set thisList to item i of masterList
      display alert "thisList " & length of thisList
      set newText to newText & (item 1 of thisList & space & tab & item 2 of thisList & "\n" & item 3 of thisList) & "\r"
   end repeat
   return newText
end convertText
(*Makes sure there is an insertion point active in the InDesign document*)
on getSelected()
   tell application "Adobe InDesign CC 2015"
      set selList to selection
      if length of selList > 0 then
         set selItem to item 1 of selection
         if class of selItem = insertion point then
            return selItem
         else
            error "Requires selected insertion point in document"
         end if
      else
         error "Requires selected insertion point in document"
   end tell
end getSelected

About the Script

The script begins with a section that sets up each step of the process by calling what is known in AppleScript as a Handler (it handles the functionality for its job requirement).

Each handler begins with a comment inside a paren-asterisk pair (* *) that provides information to the reader as to what the handler is designed to do. The line that calls the handler optionally passes information to the handler inside of a parentheses pair. If information is passed, the handler places the information into variables (parameters) inside its parentheses pair in the order received. The code inside of the handler does the job and optionally returns information as part of a return statement.

Most of the code is self explanatory with the possible exception of the convertText handler.

The convertText Handler

This handler takes advantage of a unique feature of AppleScript: text item delimiters. AppleScript's text item delimiters is by default set up as an empty string (""). By setting the delimiters to a tab, the script splits each paragraph of the tab/return delimited file into list items:

Before
Sample Name [tab]000-000-0000[tab]Description text

After
{Sample Name, 000-000-0000, Description text}

Now that the text is represented as items in a list, it can be combined back into a string as needed. For this purpose, a tab and a space will be placed after the first entry. A forced line return will replace the second tab, and a hard return will be placed at the end of the second line (description).

Before
{Sample Name, 000-000-0000, Description text}

After
Sample Name [tab]000-000-0000\nDescription text\r

If you would want a fixed space to follow the name entry (instead of a normal space) you could use a special character (such as a pipe) and replace this character with the desired fixed space after running the script.

Handling Errors

All of the calls to the handlers in the script are inside of a try/on error block. Any errors raised within the script will be handled by statements following on error errStr.

Also, the code for the script is nested inside a with timeout code block. By default, AppleScript allows two minutes for the user to respond from a modal dialog (such as choose file). Without a with timeout statement, a script will stop with a timeout error if the user takes longer than the two minutes (120 seconds). For this reason, we make sure the script gives the user plenty of time to respond. Five minutes (300 seconds) should be ample, but you can give the user more time if you think it necessary.

Why AppleScript

The person who made the inquiry admittedly is a new user. In my opinion, AppleScript's English-like syntax is much easier for a new user to understand. Further, the entire process, from creating the document to setting up styling, could have been part of the script. The functionality of the script, however, was purposely kept simple. If you were to have a repeated need for this type of document, you could create a full-powered script complete with a dialog box for creating the document from a template and entering in the required information. Another option for Macintosh users would be to combine simple scripts such as this with functionality provided by Automator.

Onward and Upward

You may want to save this script in a folder with a template for use in future documents. Or, better yet, save it in a folder inside one of InDesign's folders set up for scripts, and place the template inside a folder you have set up for Templates.

Disclaimer: Scripts provided are for demonstration and instructional purposes only. Use at your discretion.
Trackback Link
http://www.yourscriptdoctor.com/BlogRetrieve.aspx?BlogID=18153&PostID=1466908&A=Trackback
Trackbacks
Post has no trackbacks.

Recent Posts


Tags

Bullets Video records XML structure paragraph styles measurement editboxes Automating animations folder Create Text Frame Choose from list Export HTML Read text file as list Publish Online Sound with timeout BrdgeTalk ePub for Kindle reframe Library Folder Script Preferences if/then Caption Variable Find Change try/on error HTML Export XML Styling Text lists Radiobutton Group pasteboard metacharacters Preferences Enabling Groups Mail automating InDesign scripting trimText Custom CSS Style Sheets path Metadata Combobox Templates text placement Document Presets Document from Template repeat loops ePub for iPad say dropdowns Create Document text item delimiters resize Placing images import XML Find Text transform again PDF PDF security Index of blogs and scripts for year 2016 Image Placement wild cards AppleScript dialogs Fixed Format ePub AppleScript Editbox object styles Export to PDF AppleScript Templates tutorial Media import text Checkbox Control entire path XML tags automated workflow Fixed Layout ePub new document script folder Autoflow Table cells InDesign CC External Object Scripts text editbox active spread enable redraw Find by Font Excel Nested Styles transformation matrix image metadata handlers Hyperlinks arrays Glossary Repeat With integer editboxes do script Find by Attribute Interactive Publications Adobe InDesign CC Character Styles functions cut and paste directory transform user interaction level find change options massage text fit Embed Document script templates InDesign template coordinates System Events resolve CALS tables stylesheets Text Cleanup Border Panel Apple apps Document stylesheets Numbering watermark parent page Tables Custom Buttons selection Adding Captions parent Choose file automation CSS import HTML table styles text concatenation doScript PDF export preferences file paths save template Change Text InDesign dialogs styles Custom Dialog Find by Color images GPS metadata placeable measurements GPS page items

Archive