Getting data from MySQL into Microsoft Word

Question

I am preparing a Word document with fields that will be populated from MySQL. As an example, I have the following sentence in my document:

The value of your portfolio is $xxxx.

xxxx is the result of the following query which I would like to send to MySQL:

SELECT sum(colA) WHERE colB='yourname' colC='equity'

So my questions are:

1. How to connect Word to MySQL?
2. How to send queries to MySQL to get results?
3. How to create and use merge fields to display those results dynamically in my document?

Some more info:

I am trying to create an automated dynamic report template in Word. I want to be able to copy over this template, change a few parameters and then refresh the connection to MySQL. The MySQL 'fields' (like xxxx above) in the template will be refreshed automatically. The copy of the Word document will then be converted to pdf and emailed to the audience.

Any help will be greatly appreciated. Thanks in advance.

Solution

You HAVE to have the MariaDB ODBC connector

Here is the connector 32 bit Here is the connector 64 bit

The MariaDB is a fork of the mysql project so this connector should work just fine for mysql.

Had a bizarre error where it wouldn't save the dsn connection setting up from control panel before going into MS word

ODBC tools

Pre-existing connection(s) shown here

MariaDB connection

Name and description

Database login details

SQL NOW

Options

Optional encryption

More options

DSN setup

Launch word

New Source

Advanced

specify DSN from before

Select table(s)

Final DONE!!! :)

Here are all the columns in my table

Comments

[Anaksunaman]

Word should be able to use the standard MySQL ODBC driver - - dev.mysql.com/doc/connector-odbc/en/…

[KMLN]

Thanks. That answers part 1. But I need to understand how to use this connection as specified in parts 2 and 3.

[Michael - sqlbot]

It seems more likely that you'll want to get the data into Excel and merge it into Word from there... but you aren't telling us what you are actually trying to accomplish. You're telling us how you are trying to accomplish it. What is the purpose of the Word document? Why is it a Word document, not something more portable like PDF or HTML? Who will be reading it? Why can they be trusted to access the data directly from the database? These all probably have sensible, perhaps even obvious (to you) answers, but for now I can imagine no useful scenario for MySQL > Word.

[KMLN]

@Michael-sqlbot I have added some more info in the last para of my question for benefit of everyone. To your other questions - I am using Word because I don't know HTML and I guess non-coders will be more comfortable with Word. There are a lots of charts and tables which are easier for me to do in Word. Since only a read-only access is required and the Word doc itself is never exposed to anyone, I don't think there are too many security issues. I could be wrong about this though.

[cybernard]

@KMLN Added 2 more options. They are very similiar, but in one I pre-created the DSN before launching MS Word. The second I had issues where it would save it, but that could just be a quirk of the VM I was testing in.

[KMLN]

thanks for the detailed response. However, by following the steps, I can only establish a connection to MySQL and if I go further, I can get a list of records as a table in Word. However, I am looking for more granular results, not tables and ideally define some fields (quick parts) to contain those results. Please look at my question - parts 2 and 3.

[cybernard]

@KMLN finished the instructions.

[cybernard]

@KMLN If you get stuck let me know which step.

[KMLN]

Great, thanks @cybernard .. this is extremely helpful!

[Kar.ma]

This step-by-step answer is painless. I followed it without a flaw.

[cybernard]

@Kar.ma I am glad you liked it, it took a while to verify and test each step.