How To Make Mailing Labels From A Spreadsheet Using Libre/Open Office

Jim Avera (jim.avera [at] gmail.com)
Last updated Fri Jan 26 15:45:22 2024

Let's get this out of the way right now: The procedure is horrible for non-technical users. With Open/Libre Office you get fabulously more than you pay for, but that does not include a specialized application for printing labels from a mailing list. Instead, a combination of low-level features give us what we need.

First, Upgrade Libre Office to 6.x or later if necessary

sudo apt-get remove 'libreoffice*'
sudo add-apt-repository ppa:libreoffice/ppa  # once only
sudo apt-get update
sudo apt-get install libreoffice

Can I use Apache Open Office?

Overview of the Mail-Merge Procedure

1. Create a spreadsheet with your mailing list (name, address, etc.)
2. Create a dummy database to interface the spreadsheet to the mail-merge machinery.
3. Create a mail-merge document containing a grid of labels.
4. Edit the mail-merge doc to format labels using db field references.
5. Process the mail-merge doc to generate another text document with plugged-in values from the spreadsheet.
6. Print the last-mentioned document onto labels.

How-to in Detail

1. Put your data in a spreadsheet with column titles in the first row (e.g. "Name", "Address" ...)
   We will use the file name "mailinglist" as an example. Any supported file type is okay (.ods, .xlsx etc.)
2. Create a dummy database which interfaces the spreadsheet to the label generator:

   The "mail-merge" machinery only works with data from a database; it can not read a spreadsheet directly. You must manually create a separate "database" file to serve as a conduit. This "database" won't contain any data, but will obtain data from the spreadsheet when needed:

   1. Be sure the spreadsheet has been saved (File->Save or Control-S).
   2. Start Open/Libre Office if no document is open.
      Then: File->New Database
      • Click "Connect to an existing database"
      • Select "Spreadsheet" in the drop-down menu.
      • Click Next>>
      • Browse to your mailing-list spreadsheet.
        Click Finish>>
      • Check "Yes, register the database for me".
        Un-check "Open the database for editing".
        Click Finish
      • Again browse to the folder containing the spreadsheet and choose a name for the database (.odb) file. You can use a related name such "mailinglist_db", but using a very short name makes it less cumbersome to edit the label layout later. We will use "db" in this example.
3. Create a mail-merge text doc containing labels.
   A special procedure creates a grid of similar labels:
   1. Start Open/Libre Office if no document is open.
      Then: File->New Labels
   • On the Labels tab:
     Select the label brand/category in the "Brand" menu (most people in the U.S. will use "Avery Letter Size"), and a specific product number in the "Type" menu. You can also specify raw dimensions on the Format tab.
   • [very important] On the Options tab:
     Check the "Synchronize contents" box, which allows editing only the first label and pressing a button to copy the changes to all the other labels on the page. If you forget to check this, the document must be re-created from scratch.
   • In the "Database" drop-down menu, select the database you just created.
     In the "Table" menu, select "Sheet 1" (or the appropriate sheet).
   • Do not enter label fields now, but wait until after the document is fully created (this avoids a problem with line-breaks which interferes with suppressing empty lines). But if db fields already appear, just leave them as-is.
   • Click New Document to create the label mail-merge document.
   2. Save the new document ( File->Save As or Shift-Control-S). Once again browse to the folder containing the spreadsheet and choose a related name, for example "mailinglist_mmerge" (it will be an .odt file).

   The remaining steps can be done after closing and re-opening the mail-merge document.

4. Edit the mail-merge doc to format labels using db field references

   Note: Edit only the first label on the page at the upper left, then click the floating Synchronize labels button to copy your changes to the other label positions. If you don't see the Synchronize Labels button, look in the extreme corners of your screen; it can be moved wherever convenient.

   When editing label text, be sure to click inside the label border (when the cursor has an I-beam shape). If you click the border itself then the entire frame object will be selected, indicated by colored resize handles. You don't want to change the frame in any way, so if this happens click outside the page to de-select the frame. If you unintentionally change something, Control-Z will un-do the most recent change.

   1. Insert "database fields" where data values should be merged in.
      • View->Data Sources
      • View->Hidden paragraphs checked
      • View->Non-printing characters checked
      • View->Field Names checked

        Note: The field names which you're about to insert may be too long to display in the label box. To make editing easier, it may help to temporarily apply a very small font size such as 6 pt to avoid line-wraps (try Control-A to select all paragraphs before changing the font size). Then use the "zoom" control at the lower-right corner to magnify the display.

      • In the left Data Sources pane, navigate to the database created earlier and appropriate sheet name (might be done automatically).
      • Drag column headers ("Name", "Address" etc.) from the Data Sources area to where you want them in the first label of the document. At the end of each line, press Enter/Return and a "Paragraph" Pilcrow (¶) should appear. Don't be confused if long db field names wrap around - they will be replaced with data from your spreadsheet, and the line will wrap only when necessary.

        Insert appropriate spaces and comma between fields to form a proper postal address. The result might look like this:

                   db.Sheet1.FIRST NAME db.Sheet1.LAST NAME¶
                   db.Sheet1.ADDRESS1¶
                   db.Sheet1.ADDRESS2¶
                   db.Sheet1.CITY, db.Sheet1.STATE  db.Sheet1.ZIP

        Click the floating Synchronize labels button to update other label positions.

        Note: If the database fields were previously inserted, just insert paragraph breaks and spaces etc. between the existing fields as needed. Any LINE BREAKS (indicated by ↵ left-arrow symbols) must be deleted; lines should be broken only by starting a new paragraph. Don't be confused by wrapped long lines -- if you don't see a ↵ symbol then there is no line-break present.

        To delete a LINE BREAK, place the cursor immediately after it and press your Delete key.

        While editing, a special Next record token may have been automatically inserted after the last db field reference; be sure to not insert anything after it. See "Trouble Shooting" if this token is missing or gets deleted or mis-placed.

   2. Mark paragraphs to be hidden if their data field(s) are empty:

      In the example above "ADDRESS2" might sometimes be empty; in that case we want to to avoid inserting a blank line in the output. To accomplish this feat, the paragraph containing db.Sheet1.ADDRESS2 should be conditionally hidden if the ADDRESS2 data field is empty for a particular label:

      • View->Data Sources if needed.
      • View->Hidden paragraphs checked
      • View->Non-printing characters checked
      • View->Field Names checked
      • Click immediately before or after "db.Sheet1.ADDRESS2" to prepare to insert. Be careful to not select the db reference itself or you'll end up replacing it.
      • Then: Insert->Fields->Other (a "Fields" dialog opens)
      • Click the Functions tab, then Hidden Paragraph
      • Enter an expression in the "Condition" box like this:

        Drag the "ADDRESS2" column header from the Data Sources area to the "Condition" field in the Fields dialog (View->Data Sources to display Data Sources). Then insert an exclamation point in front of the resulting database reference. You should end up with something like this:

                       ![db.Sheet1.ADDRESS2]

      • Click Insert once only, then Close

      You should see Hidden Paragraph inserted into the label (visible only with View->Field Names).

   3. Add margin spacing: The boxes in the grid correspond to the physical edges of the labels, so the text must be inset from the edges to avoid registration problems while printing:
      • Add top margin: Click in the first paragraph (i.e. the top line) in the label, then Format->Paragraph and set "Spacing Above paragraph" to 0.10"; click OK.
      • Add left margin: Control-A to select all the paragraphs in the label, then Format->Paragraph, and set "Indent Before text" to 0.18" or similar; Click OK.
      • Finally, adjust the font size so all lines fit, taking into account whether you expect any data values to be long enough to cause line-wraps. It's helpful to disable "View->Field Names" at this point to reduce clutter. Select all paragraphs (all lines) in the label, then use the font-size drop-down in the tool bar to set an appropriate size. Note: You can type in fractional sizes which aren't in the menu, for example "11.5".

   4. Click the floating Synchronize labels button.
   5. Save your work (File->Save or Control-S).

   Now you have a mail-merge document which contains one page of labels, with database-field references where the actual data will be plugged in.

5. Process the mail-merge doc to generate another text document with the final label images.
   1. On a Mac, check that a Java JRE is enabled under Tools->Options->LibreOffice->Advanced (if Java is not already installed, downloaded from java.com).
   2. Open the label mail-merge document (mailinglist_mmerge.odt in our example) if not already open.
   3. File->Print (or Control-P)
      A pop-up will ask "Do you want to print a form letter?" — click Yes.
   4. A "Mail Merge" window will appear:
      • Check "All" records
      • Check "File" instead of "Printer"
      • Check "Save as a single document"
      • Click OK
      Navigate to the same folder as before and enter a related name, for example "mailinglist_forprint".
   5. Open the new document (mailinglist_forprint.odt) and look it over.
      If there are missing, duplicated, or scrambled names, see "Trouble Shooting".

6. Print the final document onto your labels.

   If you export to PDF instead, be sure to un-select "shrink to fit" or other scaling options when using it to print labels.

If blank lines appear in the middle of labels:

Re-visit the procedure to hide paragraphs which contain empty data fields. A "Hidden Paragraph" token must be inserted in each paragraph which might have no data, using an appropriate "Condition" expression. Note: If a line contains multiple db fields, and all of them might be empty, then the "Condition" expression should be something like ![db.Sheet1.FIELD1] && ![db.Sheet1.FIELD2]

Also make sure there are no hard line breaks; a new line should be started only by starting a new paragraph. To verify this, use View->Non-printing characters and look for paragraph Pilcrows (¶) and verify that no line-break symbols (↵) are present.

If the same address is printed on every label on a page:

The "next record" token is missing. In Libre Office this token is provided automatically, but in Open Office you must explicitly insert it. It is visible only with "View->Field Names" in effect.

To restore the "next record" token:

1. Edit the mail-merge file (mailinglist_mmerge.odt in our example).
2. Place the cursor just after last database field in the first label. If line-wraps make it difficult to put the cursor where you want, temporarily reduce the font size and increase magnification as suggested above.
3. Insert->Fields->Other,
   Click the Database tab,
   Click "Next record",
   Click Insert and Close,
4. Restore the desired font size if you reduced it temporarily.
5. Click the floating Synchronize labels button, then save.

If parts of different addresses are mixed together:

The "next record" token is in the middle, rather than after the last db field. This causes the first part of one record to be combined with the last part of the next record. Remove the token and re-insert it at the end, as described in the previous item.

If some records are skipped:

Probably there are multiple "next record" tokens present. See above.

I don't see any "floating Synchronize labels button" !

It's in a separate small window which can appear anywhere on your screen, and might be hidden when the document doesn't have input focus. Try minimizing or closing all other windows on your screen; The window with the button might be shown on your task bar (clicking it will make it visible on the screen).

Note: When the mail-merge file is first created with File->New Labels, the "Synchronize contents" option must be selected on the Options tab. It it wasn't, you'll have to re-create the file from scratch.

I moved my files to another folder or computer and now nothing works!

The connections from the mail-merge file to the dummy database, and from there to the spreadsheet use absolute file paths. That means that if the files are moved to a different location the connections stop working.

To fix the database file (db.odb in our example), open it and use Edit->Database->Properties to change the path to the spreadsheet document. Use Test Connection to verify the path.

To fix the mail-merge file (mailinglist_mmerge.odt in our example), open it and do Edit->Exchange Database and then browse to find the database file. It should then appear in the "Available Databases" panel. Click the triangle to view the sheets and select "Sheet1" (or as appropriate). Click Define.

Feedback / Contact

This guide was originally developed using Libre Office 4.0.2.2 on Ubuntu 12.04, and re-tested using Libre Office 6.4.8.0 on Ubuntu 23.04.
Relevant Open Office documentation is here.