Your prescription for increased productivity and profitability
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 placed in selected text frame
Our first task will be to convert the table 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.
…Find/Change settings
…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.
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
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.
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
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)
…After running the script
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.