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

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

Archive