Exporting for Word data merge

[Martin H.]

I'm exporting from an FM 9 db to create a data source for use with the data merge manager in Word 2004. (I need to create styled Word documents from FM output.)

The only way I can get the Word merge to work is to export from FM9 in Merge format specifying either Windows(ANSI) or ASCII (DOS) (but not Macintosh) as the output file character set.

But then I either get weird substitutions for high ASCII characters (when specifying Windows (ANSI)) or the high ASCII characters are just missing in the output (when I specify ASCII (DOS)). Smart quotes, apostrophes, em-dashes etc. all get replaced or dumped. Since there are quite a few large text fields (with text often pasted in from other sources), I'm ending up losing quite a lot.

Any suggestions for how to remedy this?

(I don't recall having this problem with earlier versions of FM. I used to do this kind of data merge into Word a lot, but haven't had need for it for the last few years.)

Edited May 30, 2008 by Guest

[JamesBand]

You need a an need an XSL stylesheet. The style sheet would be used to style the FM XML data as it exports. Maybe someone can point you in the direction of a tutorial.

You can also check out the XML/XSL forum here.

[Fenton]

Something like this. I've set the output of the xsl stylesheet to utf-16. That is the only way you're going to avoid messing up characters pasted in from wherever/however. It is how FileMaker stores text internally, so it can handle most anything.

I used Windows line breaks, in case that's your problem. Though I'm kind of surprised Word 2004 wouldn't handle the Macintosh output. I just tried it with Word 2008, and it accepts either. It also lets you choose the FileMaker Pro file itself as the source, choose the fields, even filter the data.

Merge.zip

[Martin H.]

Thank you SO MUCH for the example. It helped a lot, but not in the way I (or perhaps you) would have expected.

Using the data file you provided, I found that it didn't make a difference for my Word merge purposes whether I exported in Merge format or used your XSLT file. And, when I imported your Export script into my data file, I encountered the same problems with Word merge as I'd had before.

But this set me off on a new track, which lead me to recognize that my problem was with just one field in my db and with data in that field from only some records. The field in question has a large amount of text that users typically have been pasting in from Word.

The (to me) surprising discovery (after a couple of hours of banging my head against the wall): when that field contained smart quotes that weren't in matched pairs Word would insist that the export file had more fields in the data than in the header row and wouldn't open it as a data source. It was only when I used Find/Replace in FM to strip out the smart quotes that I could get the export to work as a Word data source - whether I did the Export in Merge/Macintosh format or via XLST.

I don't quite have the problem nailed. I added a notes field to the db you sent, put text in some records that contained unmatched smart quotes, and didn't experience as severe a problem as I'd been having with my db. With this variant on your db, Word would open the export file as a data source, but, when merging in the data, it just wouldn't continue the merge when it hit a record with a field containing unmatched smart quotes.

So, there may be something else going on with my db that compounds the problem.

I suspect that there's something else anyway. When I try using Word 2008 to access the FM db directly, I get a "Filemaker Pro error: object not found" (and then Word 2008 tends to crash). Haven't figured that one out yet.

[Fenton]

Hmm. I think if I was in your situation, I would use the auto-enter by calculation, with [x] Allow replacing of existing, to Substitute out the smart quotes. They don't add really anything useful, and are just a problem with other applications. You can copy/paste a smart quote into the calculation. I would also turn off the File Options default of [x] Use smart quotes.

You could, if you want, also strip all formatting from pasted text, using either:

Evaluate ( Quote ( text field ))

or

TextFormatRemove ( text field )

(came out in 8)