How to turn your spreadsheet into a database for mail merges in OpenOffice

Got labels?

I've been pointing folks to the instructions in this blog, but I wanted to pare things down so that I have one blog entry, just one, that shows only how to make your spreadsheet into something you can use in mail merge letters, label mail merges, envelope mail merges, etc.

Overview of How Mail Merges Work in OpenOffice

First: To print labels from a list of addresses, you should  put them in a spreadsheet. (You don't have to since text files, address books, and regular databases are fine for holding your data for mail merges, too. But spreadsheets are a simple approach.) Put headings across the top like Firstname, Lastname, etc.

Then: To make your spreadsheet into something that your mail merge document (labels, Christmas letter, etc.) can talk to, you need to make a little database file that points to your spreadsheet.

Finally: You make your mail merge document (labels, Christmas letter, etc.) and have it point to your database.

That's the summary. Here are the specifics.

Instructions for the First Step, Putting Together Your Data in a Spreadsheet

Here's what your data should look like. The columns you use are up to you. Put in whatever data you will need. Put City, State, etc. all in different columns. Be sure to put the label at the top for what the field is: Name, etc. This is important.

Here's a sample file. Right-click on the link and choose to save it to your computer.

Instructions for the Next Step, Making Your Spreadsheet Into a Database

You need to make a database file that points to the spreadsheet.

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. This is the name that you will see in your lists of databases.
 

You're done with the database.

Instructions for Making Labels or a Letter and Pointing It at a Database

To make labels that point to the database you created, follow these instructions. You've already done the database so

http://openoffice.blogs.com/openoffice/2006/07/mail_merge_labe.html

To write a letter that points to the database you created, follow these instructions, steps 3 and 4. You've already done 1 and 2.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

To do envelopes that point to the database you created, follow these instructions.

http://openoffice.blogs.com/openoffice/2007/08/printing-envelo.html

I strongly advise you NOT to use any of the mail merge wizards under the Tools menu. Far too complicated.

---

---

Comments

[Jeremy]

Ag. I just spent hours figuring out how to do that yesterday.

[Mike]

Hello,

When I view the sample database on this page, it displays in non readable characters. I tried to view in several types of character encoding, including auto detect, without luck. Perhaps you know what the encoding should be so I can view and download this sample file.

Thanks,

Mike

[Solveig]

Hi Mike,

Try rightclicking on the database link and saving it to your hard drive, then open it in openoffice.

Solveig

[Eric Standlee]

How do you query across databases that are linked to spreadsheets?
I have two different spreadsheets and I need to make a query that removes duplicates from the data in the two and displays only the unique data.

[Solveig]

Hi Eric,

A query can include two sheets (tables) in the same spreadsheet, I think, but not in two different spreadsheets.

[Katharina]

I followed all your instructions to do labels but when I cam to the table tab, it does not give me the option of table1 or sheet1..the window is blank. thanks for your help

[Solveig]

Hi Katharina,

Is the data set up correctly? The first row in the spreadsheet needs to be the labels, like

Name Address

and then the second and subsequent lines need to be the data.

[Laura Siersema]

Instructions for the Next Step, Making Your Spreadsheet Into a Database

You need to make a database file that points to the spreadsheet.

1. Choose File > New > Database.

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

I am stuck at step one--where do I find this? I am using open office 2.4--thanks--hope this is enough info--laura

[Gerard]

How do you get rid of fields that have no information like Address 2, Positions, Company etc. I have blank spaces in my address labels.

[tower defense]

When I view the sample database on this page, it displays in non readable characters. I tried to view in several types of character encoding, including auto detect, without luck. Perhaps you know what the encoding should be so I can view and download this sample file.

[IT Certifications]

wow that's really great you make mailing really easy now.

[Cheap Computers]

Hello, i am glad to read the whole content of this blog and am very excited and happy to say that the webmaster has done a very good job here to put all the information content and information at one place, i will must refer this information with reference on my website i.e www.electrocomputerwarehouse.com

[matt harrison]

Here's what I needed to do (register db, align labels, feed labels one at a time):

http://panela.blog-city.com/openoffice_label_mail_merge_from_a_spreadsheet.htm

[historical places in pakistan]

Steeped in history and blended in culture, historical sites in Pakistan reflect the

tradition and way of life of the era gone by. There are a number of historical sites in

Pakistan that make an excellent tour.

[Social Bookmarking Submissions]

Social bookmarking submission has developed into a powerful tool for SEO experts. It involves linking sites within the various forums, blogs and message boards on social networking websites, blog sites and content centric sites like Digg. Considered a better alternative to submitting a website on blogs, manual social bookmarking is a simple, convenient and hassle-free way of making sure that your site is noticed and marketed in the right circle.