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

[kmh]

I followed the steps above, with no problem.

However, when I click on the table I get this error message.
"The connection to the data source ____ could not be established. The file _____ could not be loaded.

Any help.

[Solveig]

Hi KMH,

Either the database you're pointing to is deleted, or if the database is based on a spreadsheet, you might be in, or have open, the spreadsheet it's based on.

As a control, do the same type of thing just on a plain spreadsheet and it should work.

Solveig

[kmh]

The spreadsheet is a Lotus 1-2-3-file, with a .123 extension. I tried different Lotus files, .123 extension and WK1 extensions, still no luck. Is there a known problem with OpenOffice not converting Lotus spreadsheets into databases?

If I open the Lotus file in Calc, save the spreadsheet as ods, then the operation works. That's a run around though.

[TG]

Thank You for these useful instructions!

I wanted to prepare something else than mass-mail - a series of sheets for checking attendance at classes /* I'm sorry for my English */. There were a number of groups, all having classes on different days in different rooms. I worked hard to put this data into a OOo database (which I want to write about in a minute), and tried to produce such sheets, with changing times, dates, lesson topics and group names. However - the tools for "mass-mailing" (I have a localized version of OOo, so my command translations are a clumsy round-trip from Polish) wanted a fixed, unflexible "address block" and my database query "did not fit" as data source - it had no "title" "name" "surname" etc., just date, hour, group_name etc. Also, trying to generate a report from the OOo Base did not produce the result I wanted.

This "data source view" in a simple text document just helped me out! Still, the key to success was the keyword "print" - that's when the document is generated - I might have to print it somewhere else, where my datasource would be completely unavailable.

So, Ms. Haugland, I think You're doing great work, showing all these simple tricks! For some time now, when having trouble with OOo, I have googled for some keywords (like "change impress slide background"), and looked for results from Your blog, as these were most likely to point in the right direction.

Now - for the database part (it is less connected to this article, so I'll keep it short): is it possible to create forms with comboboxes which return data from other tables as keys, but display nice captions made up of other fields (or at least one field). I got it working with 1 column tables, without a primary key:
table rooms: room(varchar)
in my table "classes" there is a field "room" also of type varchar.
A typical database-like situation would be, when there were two columns:
table rooms: id(int, autoinc), room(varchar)
and a reference field in "classes" "room_id"

Now I want a form for classes, which has a combobox "Room", which displays rooms' names, but stores room ids in the classes table. I haven't got this working like this, though: I had to redesign my tables. Perhaps I have missed something.

With regards,
Tomasz

[karry]

http://www.batterygoshop.co.uk/batterycharger/fujitsu/fujitu-19v-3.16a-60w.htm New OEM Fujitsu Lifebook T-4020 T4020D T4020 AC 3.16A ,

[Martin]

Just a remark: You need to have OO Base installed. I didn't have it and didn't have Database in Create new menu.

[Alex VanderWoude]

I have the same question posed by Rudy (April 12, 2008 at 02:09 PM), namely that my mail merge prints one page per data line but I want to have three on a page to save paper. Note that I don't mean three copies of the same person's data per page, but the next three people in the data source on one page. Following the link to http://openoffice.blogs.com/openoffice/2007/05/wayyyyy_simpler.html as suggested did not seem to address that issue, or if it did I am unable to find the information. Am I just blind? By the way, I'm using 3.0.1 now if that makes a difference.

[Alex VanderWoude]

Never mind, I solved the multiple-items-per-page thing by setting them up as custom-sized labels rather than as a mail merge. It works great!

[Real Exam]

Nice way of explaing difficult things.

[tower defense]

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.

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/

[David]

It's simpler to keep using M$Word, sadly. Bye bye OO.

[EMR Software]

Nice way to perform the mail merge operation. I have been trying to do this using my custom desktop blogging engine but got more options here in open Office and process, too, was rather simpler.

[vijayan]

madam
thanks a lot. It is very nice description about Mailmerge.

vijayan calicut