Automating Adobe InDesign 2019 - AppleScript


Excel to InDesign

Monday, September 30, 2019

Excel As Data File

There has been an explosion of ways that Excel is being used for more than just a spreadsheet. One of the ways is to use it as a data resource for automating document creation. This is, in part, due to InDesign's Data Merge feature. The power of this feature makes this use understandable. The problem with the Data Merge feature is that it relies on a comma-return separated file (.csv). And, csv does not honor styling. The styling that is achieved is due to InDesign mapping the styling applied to data placeholders with column field names in the csv file.

To preserve character level styling, a data source other than a csv file needs to be used. This leads to the question: What if the Excel table itself were to be used? Let's explore. First we create a new document and then import the Excel table. (Must be .xls not .xlsx format.) The data imports with each item as a separate row in a single table. And, yes, the character styling is preserved.

Excel table as placed in InDesign...Excel table placed in selected text frame

Our first task will be to convert the table to text.

Table converted to text...Table converted to text

Delete the Table head, and the column heads below. (The text frame should now start with the first item of the data.)

Next, do a find/change to change tabs to returns.

Settings for finding and changing text...Find/Change settings

Text after changing tabs to returns...After changing tabs to returns

Now to style the paragraphs. Hopefully you have set up your document using paragraph styles. At this point we will just style the paragraphs manually.

Proof of Concept

Now that we have worked through the process manually, we can write a Proof of Concept script to make sure the script will work when we replace the manual steps above with code. We will have the user define the text frame for importing the table:

set fileRef to choose file
tell application "Adobe InDesign CC 2019"
	set selList to selection
	set itemRef to item 1 of selList
	tell itemRef to place fileRef
	set tableRef to table 1 of itemRef
	tell tableRef to convert to text column separator tab row separator return
	set storyRef to story -1 of document 1
	tell storyRef to delete (paragraphs 1 thru 2)
end tell

Running this script with a sample Excel file gives us enough information that we can go ahead and write our script. But, even with this we will start simple. For testing, our data file will have only one item to start with. Our document will only have one page with a Primary Text Frame. We will have the user select the text frame for placing the file. The script will test to make sure a text frame is selected as part of a handler routine.

set itemRef to getFrameSelection()

(*Returns text frame reference if first item of selection*)
on getFrameSelection()
	tell application "Adobe InDesign CC 2019"
		set selList to selection
		if selList is {} or class of item 1 of selList is not text frame then
			error "Requires text frame to be selected"
		else
			set itemRef to item 1 of selList
		end if
	end tell
end getFrameSelection

With the text frame selection verified, the script will have the user select the file. Again, a handler is used with the text for a user prompt and file extension passed to it as part of its call:

--Get Excel file
set promptStr to "Select Excel file to import"
set fileExt to "xls"
set fileRef to getFileRef(promptStr, fileExt)

(*Returns alias reference to file if file extension for file chosen is as specified*)
on getFileRef(promptStr, fileExt)
	set fileChoice to choose file with prompt promptStr
	set fileInfo to info for fileChoice
	if name extension of fileInfo is not fileExt then
		error "Requires Excel file with file extension of " & fileExt
	end if
	return fileChoice
end getFileRef

Putting the Script Together

With the code for getting the text frame and file verified, we can combine the two to place the file and convert the table to text. To add to this we will develop a handler to place the file. Adding a call to a handler named placeTableToText to the top section, the script will now read as follows:

set promptStr to "Select Excel file to import"
set fileExt to "xls"
set itemRef to getFrameSelection()
set fileRef to getFileRef(promptStr, fileExt)
set storyRef to placeTableToText(itemRef, fileRef)
theResult

The handler (added to the script):

on placeTableToText(itemRef, fileRef)
	tell application "Adobe InDesign CC 2019"
		tell itemRef to place fileRef
	end tell
	set storyRef to story -1 of document 1
	return storyRef
end placeTableToText

Make sure you also have the getFrameSelection and getFileRef handlers from above at the bottom of the script. With your document open and the Primary Text Frame selected, you should be able to run the script to have the table placed in your document. If you get an "Import Failed" error, try the script with a new document. We have received this error with some of our tests but have yet to define exactly what causes the problem.

Once satisfied that the script is ok at this point, we can flesh out the placeTableToText handler to delete the first two lines of text placed (table head and column heads) and change tabs to returns. This requires another handler for finding and changing text.

(*Place Excel file, convert to text, and change tabs to returns*)
on placeTableToText(itemRef, fileRef)
   tell application "Adobe InDesign CC 2019"
       tell itemRef to place fileRef
       set tableRef to table 1 of itemRef
       tell tableRef
	   convert to text column separator tab row separator return
       end tell
       set storyRef to story -1 of document 1
       --delete first two paragraphs
       tell storyRef to delete (paragraphs 1 thru 2)
       set findText to tab
       set changeText to return
       set foundSet to my findChangeText(storyRef, findText, changeText)
    end tell
    return storyRef
end placeTableToText

(*Returns reference list of text changed*)
on findChangeText(objRef, findText, changeText)
   --clear find text preferences
   tell application "Adobe InDesign CC 2019"
       set find text preferences to nothing
       set change text preferences to nothing
       --set properties of find change text 
       set find what of find text preferences to findText
       set change to of change text preferences to changeText
       tell objRef
	   set textChanged to change text
       end tell
       set find text preferences to nothing
       set change text preferences to nothing
   end tell
   return textChanged
end findChangeText

For this test, we have added items to our Excel file. Because we created the document with a Primary Text Frame, pages are added to accommodate the number of items. (We only have 16 items in our test data file, but the process should scale up with any number of items--there must be a limit.)

All that is left at this point is to style the paragraphs.

Styling Paragraphs Using Next Style

If you use Next Style to set up paragraph styling, the StyleText handler can use a looping structure to style the paragraphs. Note: This code only works if each paragraph style in the group assigns the next one as its next style. This includes the last style which now must use the first style as its next style--can't be Same Style or No Style.

We now add a call to a handler to style the text with a reference to the story passed together with the name of the first paragraph style in the looping group.

set firstName to "name"--name for first paragraph style of group
styleText (storyRef, firstName)

(*Styles text using paragraph styles having Next Style defined; last style has the first style as its next style*)
on styleText (storyRef, firstName)
   tell application "Adobe InDesign CC 2019"
       set paraCount to number of paragraphs in storyRef
       set theCount to 1
       set firstStyle to paragraph style firstName of document 1
       copy firstStyle to thisStyle
       repeat with i from 1 to paraCount
	   tell paragraph theCount of storyRef to apply paragraph style using thisStyle without clearing overrides
	   set nextStyle to next style of thisStyle
	   copy nextStyle to thisStyle
	   set theCount to theCount + 1
	end repeat
   end tell
end styleText

The Final Script

Putting all of the pieces together the top portion for the final script now reads as follows. You will need to make sure all the handlers are included at the bottom of the script (getFrameRef, getFileRef, placeTableToText, findChangeText, and styleText)

set promptStr to "Select Excel file to import"
set fileExt to "xls"
set firstName to "name" --name of first paragraph style 
set itemRef to getFrameSelection()
set fileRef to getFileRef(promptStr, fileExt)
set storyRef to placeTableToText(itemRef, fileRef)
styleText (storyRef, firstName)

shows part of finished page...After running the script

Onward and Upward

Now that the script is doing what you want, create a dummy Excel file to include a satisfactory number of items for texting (we settled on 16). If you want more than one entry per page, will also want to prepare your document with threaded text frames on the master page to  autoflow the story. For this, you change the size of the master text frame on page 1 of the master spread. Then, you thread it to the next frame. Each frame in succession is threaded to the previous frame until you have the number of frames needed for the page. Return to page 1 (the only page at this point) and save. Import or create text styles as needed being sure to set Next Paragraph for each to the appropriate style. Be sure to set Next Style for the last paragraph style to the first paragraph style. Save the document and test with your script. If you set Keep Options for the first style to Start Paragraph: in Next Frame each entry will occupy a new frame.

The script should work satisfactorily, but it is not quite complete. If Excel import preferences and the settings for find change text options are set correctly--as most likely they are by default-- the script will perform as expected. You will want to look up these two preferences in InDesign's scripting dictionary for further information.

Disclaimer:
Scripts provided are for demonstration and educational purposes. No representation is made as to their accuracy or completeness. Readers are advised to use the code at their own risk.

Trackback Link
http://www.yourscriptdoctor.com/BlogRetrieve.aspx?BlogID=18424&PostID=1529719&A=Trackback
Trackbacks
Post has no trackbacks.

Recent Posts


Tags

reference to Character Styles Find Text place point Styling Text Document Presets Choose from list Check for Styles XML tags Next Style measurements StandardAdditions file paths Text Style Mapping live bounds Custom Dialog automated workflow giving up after Templates draw from center Adjust Layout Choose file selection Script Preferences rest Keyboard Shortcuts active spread script templates Export to PDF threaded text frames Adding Captions coordinates Image Placement do script Change Text Enabling Groups tabs auto tag Multi-state objects lists Clearing Overrides Read text file as list Find by Attribute Placing images Chaining Paragraph Styles Excel InDesign template Combobox Checkbox Control Document stylesheets radiobutton control page items random number measurement editboxes intent tab list Editbox Autoflow script folder XML bookmarks import XML Previous text frame import styles Image Events convert to text try/on error find change options grid paragraph styles Script panel Create Document next text frame image metadata text import preferences object styles sort list lists of lists info for Script Library Create Text Frame file info Buttons Place text static alignment repeat loops date objects user interaction level Document from Template map tags to styles Repeat With Radiobutton Group GREP choose file name primary text frame text item delimiters Border Panel table styles Write to File dialog CALS tables start paragraph Apply Object Style import text fit stacking order handlers dropdowns Library Folder time entire path master spread XML structure rest of list move file

Archive