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

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

Archive