Mail Merge in OpenOffice Writer: Creating Mail Merge Documents From Text/CSV or Spreadsheets

I've got a lot of info out there, including lots of coverage in my book, about mail merges. However, I don't have a nice simple straightforward blog on it with everything in the same place all spelled out. Didn't, that is. This is all you need to do to make a nice simple document based on data in text files or spreadsheets.

What You Have to Do

1. Get your data. You've already got it, probably. This blog  is for people with data in text files, and in spreadsheets.

2. Turn it into a data source.

3. Create your mail merge document and suck the data in through the data source.

4. Print, specifying how many of the data records you want to print for, and whether to print to a file or printer.

1. Get Your Data

You probably already have it. It's in a .txt file or .csv that's comma or tab separated, perhaps. Or it's just a spreadsheet.

2. Make the Data Source: Text File Instructions

If your data is in text files, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Text as the format.

3. Click Next.

4. Specify the DIRECTORY where the text files are. Each text file in that directory will be a table in your database. Then select the item separating fields, i.e. a tab or comma or something else.

5. When all the settings look correct, click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.

You're done.

2. Make the Data Source: Spreadsheet Instructions

If your data is in a spreadsheet, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.
 

You're done.

3. Create Your Mail Merge Document and Suck the Data In From the Datasource

You can also use the simple or complex mail merge.

Simple:  http://openoffice.blogs.com/openoffice/2006/03/techtarget_arti_1.html

Complex: http://openoffice.blogs.com/openoffice/2006/02/techtarget_arti.html

But this is a nice way to do it too.

1. Create a new Writer document or open a document containing text that you want in the mail merge document.

2. Choose View > Data Sources. Everything you've created will be displayed. Click the + sign by the data source you want to use, then click + by Tables til you see the data you want to use.

3. Type any content you want and do any formatting. You can do this later too.

4. Click on the NAME OF THE FIELD, not the piece of data, that you want in the mail merge.

5. Drag it into the document and release. The field name will appear.

 

6. Add any other content and fields you want.

Save the document. You're ready to print.

4. Print the Mail Merge Document.

1. Choose File > Print.

2. You'll see this message. Click Yes. DON'T MARK THE CHECKBOX SAYING YOU DON'T WANT TO SEE THE MESSAGE AGAIN.

3. In the print window, specify the range of records, if you don't want them all, and specify to print to a printer, or to files.

4. Click OK.

5. In the print window, specify the printer and click Print.

---

Comments

[BASKAR]

thanks a lot... i was really searched all the web about mail merge including openoffice.org... your tutorial only helped to understand about mail merge... i really want to thank you once again...

[Paolo]

Thanks a lot, it was very helpful.

I actually have a related question: what does it happen if I change the data source (aka the DabaBase)? I would expect the change reflected in the database embedded in the text file. That happens NOT to be the case in my Oo2.3!

Conversely, if I changed the database embedded in the text document, the change would NOT be reflected in the original DB. Strange, isn't it?

It is as if the original DB and the embedded one were a two different things!

Regards,

Paolo

[Mike]

Thanks, Solveig, for the clear and very useful instructions on mail-merge.

Mike

[Phillip]

Thank you for this tute. I'd been trying to figure this out for days, having spent weeks building my source spreadsheet. Now have my mail merge behaving.

Appreciated

Phillip

[Solveig]

Hi Philip,
Glad it helped -- mail merge works reliably but the simplest way is not obvious. ;>

Solveig

[rudy]

is there any way to do what micro$oft word does with a catalog merge where it merges all data to a single file without page breaks?

essentially I would like to merge a database into a file with similar wording in between each entry, but I do not want each entry in a separate file or separate page.

example:

Name of product: "MERGE FIELD ONE"
price: "MERGE FIELD TWO"
Size: "MERGE FIELD THREE"
--->Line Break<----

Then when I merge, it repeats this process for whole database in a single document with just the single line break between each entry.

Output would look like this:

Name of product: Computer 1
price: $200
Size: 5 GB

Name of product: Computer 2
price: $400
Size: 10 GB

Name of product: Computer 3
price: $600
Size: 20 GB

etc.

[Solveig]

Hi rudy,

Yep!
http://openoffice.blogs.com/openoffice/2007/05/wayyyyy_simpler.html

[Bill]

I used your instructions to create my first BASE dB and it went without a hitch. But when I tried to create a second new database I am not able to get the headings to import as field names. The only thing that shows in the wizard is the A B C column titles. The first row instead of importing as titles import as data in the first record. Also the first time I remember a comment in the instructions about cutting and pasting or drag/drop the spreadsheet to the "table" field in BASE and it would set up the table which it did. I just cannot find that set of instructions. Can you provide the link.

[Charlotte]

I wonder why OpenOffice doesn't allow merging from the text or especially the spreadsheet files directly. What's the rationale behind first having to convert these files to "data sources"? I notice the Mail Merge Wizard automatically converts and registers spreadsheet files as data sources. This doesn't seem to be the case in View -> Data Sources. There seems to be no way simply to drag the spreadsheet file into the explorer window or anything like that, or just to use the spreadsheet file "as is" for a merge without converting it. It must be converted to a "data source" manually first for some reason. I wonder why this is necessary. Doesn't it mean that any future changes to the spreadsheet file will require converting the spreadsheet again and registering the data source anew? Or is the new data source "linked" to the spreadsheet file?

[Solveig]

Hi Charlotte,

You get a lot of power from creating the data source, plus it's quick and you only need to create it once. Once you've got the .odb database file, then if you want to create a new document that's merged with the text file or spreadsheet, then bam, just view data sources, drag in your fields, and you're good.

Solveig