FGDB Source for MS Word Doc Mail Merge

960
9
Jump to solution
03-12-2018 12:03 PM
JamesCrandall
MVP Frequent Contributor

Any examples for using a FGDB feature class as the data source for a MS Word Document mail merge?

I'm able to implement this by simply exporting the feature class as csv but I need to extend this to include a FGDB containing attachments and be able to add the images into the word document as well.

Thanks for any ideas.

0 Kudos
1 Solution

Accepted Solutions
JamesCrandall
MVP Frequent Contributor

The solution is that there is no straight forward way to implement this.  To complicate things, I've been tasked to replicate the reporting capabilities found on the Survey123 site, which allows users to upload a word doc template with merge fields setup to map to the fields in the hosted feature service source data.

Implementing this with python is just messy.  However I was able to coble it all together and just have the source script, word document template and run this on a timed schedule from a batch scripting server we have.  The download feature service is an adaptation of Jake Skinner's script https://community.esri.com/docs/DOC-6496-download-arcgis-online-feature-service-or-arcgis-server-fea...

The rest of the implementation simply takes the output of that download, takes the attachments table and transposes those rows into columns.  Those values just point to the directory where the images were downloaded to and a final csv is generated that is used as the datasource to the word document template with the mail merge all setup.

The last step just uses win32com.client to perform the merge operation and save each document out to a pdf output that is saved in a desired network directory that the customer has specified.

Like I said, messy.  But doable.

As with a lot of things I've been asked to implement, the challenge is with non-arcpy operations.  With this one it was figuring out simply how to insert images into the template mail merge document and force sizing.

View solution in original post

9 Replies
TedKowal
Regular Contributor II

This is one of the reason why I want to keep the personal MS Access geodatabase.... if it is an access database then treat the source as any other MS Access table for your mail merge....  at least that is how I do it.  (Plenty of examples of that out there in wild)     

Can you not create an ODBC connection to the data store and have Word read that directly?

JamesCrandall
MVP Frequent Contributor

I had thought about the Personal GDB but not sure if I can go there. 

I've actually made some progress, the challenge as usual is not necessarily arcpy related but is in re-working an intermediate output data source that contains merged rows of the attachments with the feature class and dumping all of the attachments into a single directory that you can map the rows to.

From there it's a matter of building the merge fields (which is actually sort of tricky) to use the directory path + image names that are mapped to each row in the intermediate output.  It will look something like this,

{INCLUDEPICTURE "{IF TRUE "{Mergefield <<column in source data>>}" } " \d"

Once the datasource process is figured out and the source MS Word doc is setup, it's just a matter of implementing  win32com.client to run the merge.

app = win32com.client.Dispatch("Word.Application")
doc = app.Documents.Open(doc_template_name)
doc_template = app.Documents.Open(doc_template_name)
0 Kudos
TedKowal
Regular Contributor II

Given that ESRI will be dropping MS Access, I do not recommend that...but this type of thing is easy peasy using Access.  In my shop, speed is related to functionality (geoprocessing is only one part of the equation).  

Back to your issue .. Since you are using variable picture/images, are your path names separated by "\\"  double slashes, I use vb script so I do not know if py handles this .... but that was my issue when I first started using mail merge and including images.  For my images, I use a standard name scheme so I generally can skip the joins since my image names are data in-it-self (Yes, I know this is not best practice...but it works).  But for what it is worth here are some of my notes that help me ....

To insert variable images in a mailmerge, you need to embed the relevant mailmerge field in a INCLUDEPICTURE field. However, there are some issues with this that make the process less straightforward. For example, when embedding a mailmerge field in an INCLUDEPICTURE field for the purpose of merging graphics:

1. the file paths to the fields need to have the separators expressed as '\\' or '/' instead of the usual '\';
2. the pictures usually won't show until you refresh the fields (eg Ctrl-A, then F9) in the output document after completing the merge; and
3. even after updating the images, they remain linked to the image files, which can be an issue if you later delete the image or you need to send the merged output to someone else.

{IF{INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d}}

The Braced pair help handle the refresh or re-linking issues....Note cannot add the brace programically need to do this in Word!

0 Kudos
TedKowal
Regular Contributor II

Found some more notes on Single Directory vs path to image: 

If you can be sure the pictures will always be in the same folder as the mailmerge main document, you can incorporate a FILENAME field thus:
{IF{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d}}
or:
{IF{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d}}
 
If the path data are included with in the image field, you can use:
{IF{INCLUDEPICTURE {IF TRUE «Image»} \d} {INCLUDEPICTURE {IF TRUE «Image»} \d}}
or:
{IF{INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d} {INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d}}
For what it's worth, *provided* the path has the separators expressed as '\\' or '/', you can retain the links by omitting the all-encompassing IF test and the images will display correctly without the need to refresh the fields after completing the merge. For example:
{INCLUDEPICTURE {IF TRUE "C:\\Users\\My Document Path\\Pictures\\«Image»"} \d}

0 Kudos
JamesCrandall
MVP Frequent Contributor

Yes, that is what I am doing.

0 Kudos
JamesCrandall
MVP Frequent Contributor

I can insert the images into the template .docx file, that's solved.  The issue is finalizing a comprehensive data source for that docx to hook the merge fields.  It's challenging because the attachment table is related to the feature class and these must be processed into a single data source as a final output that the doc can use.

0 Kudos
TedKowal
Regular Contributor II

I don't use the attachment table... reason why I name the attachments something like FeatureID-X.png; the -X is for when I have multiple images for one feature eg Sign Post and its related Sign Panels(Multiple) and the -X is the placement order.  So in my case I don't have to make a walk through making a comprehensive data store, I simply only have to create a query/view  and Word uses that view as my single source..... reason why I put smarts in my image naming....

Select SignID.Signs, SignType, Config, (SR & "\\" & SignID.Signs & - & Config) & ".png") as Filename from Signs, SignPanels where SignID = ParentID;

Signs are a Feature Class and SignPanels is an attribute table.  My panel images are stored under each respective SR (State Route) they are assign to.

There I have my single datastore with no extra work except for the query development.  In my case, no coding since I am using Access other than the macro in Word.  Most of my use of attachments come outside of GIS in my case, so the attachment table and its funky relationships are not needed since I don't really put them on maps and my online stuff is Google/Bing Maps over which I overlay the attachments with a similar query and using Lat/Long for placement.

Good luck!  Maybe someone who has knowledge of the Attachment Table can provide more assistance!

JamesCrandall
MVP Frequent Contributor

The solution is that there is no straight forward way to implement this.  To complicate things, I've been tasked to replicate the reporting capabilities found on the Survey123 site, which allows users to upload a word doc template with merge fields setup to map to the fields in the hosted feature service source data.

Implementing this with python is just messy.  However I was able to coble it all together and just have the source script, word document template and run this on a timed schedule from a batch scripting server we have.  The download feature service is an adaptation of Jake Skinner's script https://community.esri.com/docs/DOC-6496-download-arcgis-online-feature-service-or-arcgis-server-fea...

The rest of the implementation simply takes the output of that download, takes the attachments table and transposes those rows into columns.  Those values just point to the directory where the images were downloaded to and a final csv is generated that is used as the datasource to the word document template with the mail merge all setup.

The last step just uses win32com.client to perform the merge operation and save each document out to a pdf output that is saved in a desired network directory that the customer has specified.

Like I said, messy.  But doable.

As with a lot of things I've been asked to implement, the challenge is with non-arcpy operations.  With this one it was figuring out simply how to insert images into the template mail merge document and force sizing.

ChristianeRoy
New Contributor

Hi James, I know this is 2 years old but we are just getting around implementing something similar. I am not a developer or programmer btw. I am struggling with some of your instructions related to the pivot of the attach tables to included the images in the csv. Maybe you even have a smoother approach by now (wishful). thanks for any input or updates.   

0 Kudos