12 April 2014: The OpenOffice Wiki is not, and never was, affected by the heartbleed bug. Users' passwords are safe and wiki users do not need take any actions.

Documentation/How Tos/Creating Mail Merge Documents From Text/CSV or Spreadsheets

From Apache OpenOffice Wiki
Jump to: navigation, search


Checked.png Checked for technical accuracy mwaller june08

This is all you need to do to make a nice simple document based on data in text files or spreadsheets.


Get Your Data

Here's what your data should look like. It may be in a .txt file or .csv that is comma or tab separated. Or put it in a spreadsheet. 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.


To Make the Data Source (Text File Instructions)

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

  1. Choose File > New > Database.
  2. Select Connect to an existing database, 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. Unmark the option to open the database for editing. You can open it; you just do not have to.
  7. Click Finish.
  8. Save the data source (aka database) under a name that will help you remember what it is.


To Make the Data Source (Spreadsheet Instructions)

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

  1. Choose File > New > Database.
  2. Select Connect to an existing database, 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. Unmark the option to open the database for editing. You can open it; you just do not have to.
  7. Click Finish.
  8. Save the data source (aka database) under a name that will help you remember what it is.


To Create Your Mail Merge Document and Suck the Data in From the Data Source

  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 have created will be displayed.
  3. Click the + sign by the data source you want to use, then click + by Tables until you see the data you want to use.
  4. Type any content you want and do any formatting. You can do this later too.
  5. Click on the field name, not the piece of data, that you want in the mail merge.
  6. Drag it into the document and release. The field name will appear. When you print, the correct data will appear.
  7. Add any other content and fields you want.
  8. Save the document. You are ready to print.

To Print the Mail Merge Document

1. Choose File > Print.

2. You will see a message asking you whether you want to print a form letter. Click Yes.

Documentation caution.png Do not mark the checkbox saying you do not want to see the message again.


3. In the Mail Merge window, if you do not want all records, select the first record, hold down Ctrl and select the next one, and so on. Or, specify the range of records.

4. Specify to print to a printer, or to files and click OK.

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


To Print Envelopes

Envelopes are not too bad once you figure out how to do it the first time. Here are the key points.

  • You need to select your printer and setup the printer to expect envelope shapes, not letter or A4 shapes.
  • You also need to fiddle around for a while and figure out where (left, right, perpendicular, parallel) in the tray your printer expects envelopes. Buy a box of cheap envelopes and expect to waste a few while you experiment.
  • Then make sure that your envelope document is set up to print to the envelope size your printer is expecting. Envelope 10 is good.
    • You can type in your data for the envelope or use a data source. The simplest way is to just choose File > New > Text Document, then Format > Page Size and instead of Letter or A4, choose Envelope 10 and change the orientation to landscape. Then either with standard margin formatting or with frames, put the text where you want.
    • As another way to create the envelope document, you can also choose Insert > Envelope, and use the three-tabbed window that appears.
  • Print the envelopes separately from your letter or other document that goes with the envelope. It complicates things too much with the paper size that the printer expects. If you are using data sources, click Yes in the dialog box that appears asking if you want to print a form letter.
  • The next time you want envelopes, just use that same document you already created, and change the addresses. Either save the document in myimportantdocuments\envelopes, or if you are a template kind of person, make it a template. (File > Templates > Save, select a category and name the template, then choose File > New > Templates and Documents and pick your envelope template.)


Tip.png Some of the pain of envelopes is the printer setup. Instead of constantly changing and checking the printer settings, add another instance of the printer, configure it for envelopes and name it "envelope." To print an envelope choose this printer instead of the default one. That way your settings are always the same.


To Print Labels

Once you have got the database set up in OpenOffice.org, you are ready to go.

  1. Choose File > New > Labels.
  2. In the Labels tab of the Labels window, select the database you created in the Database dropdown list.

Note: You are looking for the name of the database you created by choosing File > New > Database, not the spreadheet, address book, or text file containing the data.

  1. Select a table from the Table list. This will be Sheet1 or whatever the sheet name is, if you are using a spreadsheet to hold your data.
  2. Select the first field you want to use from the Database field list and click the arrow next to it to insert it in the Label text field.
  3. Use the Database field list to insert any other fields you need. Type a space after the field and you can add the next field, such as LastName. If you want fields on the second line, click after the last character of the last field you inserted, in the big text box, and just press Enter. If you need to change the arrangement later in the created label document, you can.
  4. In the Brand dropdown list, select Avery Letter Size if you are not using A4.
  5. In the Type dropdown list, scroll through the billions of labels. Select the type of label you are using.
  6. Click the Options tab.
  7. Optional: Select the Synchronize contents option if you want to apply formatting, like a different font or colors or adding graphics, and make those changes apply to all of your labels.
  8. Click the Setup button next to the printer display.
  9. Select the printer you want to print to.
  10. In the printer options (this will vary according to your operating system) specify the appropriate paper feed or tray. For now, select Manual Feed.
  11. Click the New Document button at the bottom of the window. The labels will appear. You will not see the data, you will see the field names like first name. When you print, the correct data will appear.
  12. Optional if you selected the Synchronize contents checkbox: If you need to make changes, like adding spaces, rearranging fields, or changing formatting, do so in the upper left label. Make the text an interesting font, or make it the size you need. Then click Synchronize Labels to apply those changes to all labels. You can also right-click on the border of the upper right label, choose Frame, go to the Background tab, and give it a background color.
  13. Choose File > Print and proceed as described above in "Print the Mail Merge Document".

You can save the label document and just go back to it again when you need to use it again.

Documentation note.png If you have any problems printing, check your printer setup using your operating system setup tools.
Tip.png As mentioned above, you see the field names, not the field contents, in mail merge documents. To see the contents, press F4 to view your data sources. Expand the data source you are using, select the table you are using, click the blank gray box in the upper left table corner to select all the rows, and click the Data to Fields icon in the Table Data toolbar above the table.




See Also



This How To comes from Solveig Haugland's Blog

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages