Creating a mailing label from a spreadsheet

Writing a book, Automating Document Production - Discuss your special needs here
Post Reply
rebeccaferguson
Posts: 5
Joined: Wed Jan 07, 2009 10:32 pm

Creating a mailing label from a spreadsheet

Post by rebeccaferguson »

I received an Excel spread sheet from the person who formerly had my job. The spread sheet has almost 500 names and addresses. I am able to open the spreadsheet with Open Office calc. My question is how to I use this spreadsheet as a database and create mailing labels? I am not sure if I posted this is the correct category. I am rather new to Open Office. Any Help would be appreciated. I really do not want to retype 500 entries. Thank you.
OOo 2.4.X on Ms Windows XP
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Post by thomasjk »

You need to register the spreadsheet as a data source. Open Writer and go to File--->Wizards---->Address Data Source--->Other External Source. Click Next--->Settings---->Spreadsheet and navigate to your file. Press F4 to see the source file and any tables etc. Read this excellent article http://www.freesoftwaremagazine.com/art ... office_org for help with mail merge in Openoffice. This BLOG is also a very good resource http://openoffice.blogs.com/openoffice/.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Post by acknak »

I moved your question to the Beginners forum, since it's really not specific to the Base component of OOo. I think more people will see it here.

You shouldn't need to re-type anything, although you may need to re-organize it a little, to get each piece of information consistently in it's own column. E.g. all the names in one column, all the street addresses in another, and so forth. The sheet may already be set up that way.

Once that's done, all you need to do is set up a database that uses the spreadsheet data and register that database as a "data source", then you can easily access the address data in your spreadsheet from any OOo application. You will probably want to use OOo Writer (word processor) to create things like address labels or personnel rosters, but that's a whole 'nother topic.
AOO4/LO5 • Linux • Fedora 23
rebeccaferguson
Posts: 5
Joined: Wed Jan 07, 2009 10:32 pm

Re: Creating a mailing label from a spreadsheet

Post by rebeccaferguson »

:D Yay! That did the trick! Thank you both for your help! You saved me a ton of time!
OOo 2.4.X on Ms Windows XP
rebeccaferguson
Posts: 5
Joined: Wed Jan 07, 2009 10:32 pm

Re: Creating a mailing label from a spreadsheet

Post by rebeccaferguson »

OK, maybe I did something wrong, but upon further inspection, not all the records printed. I selected all the records, but only every other one printed. I have exactly half of the labels I need. Do I need to do something different? :?:
OOo 2.4.X on Ms Windows XP
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Post by thomasjk »

You most likely have an extra Next Record Field in the label. Open your template file and View--->Field Names. Delete the extra Next record Field and click synchronize. Turn off viewing field names and print to a file to verify the fix.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Post by acknak »

And if you have more than one page of output, double-check that all records are printed across the page transition; i.e., none are lost between the end of page one and the start of page two.

I've had OOo automatically add a "Next Record" field at the end of the page, skipping a record because I already put one there. Ack!
AOO4/LO5 • Linux • Fedora 23
pearsonca
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Post by pearsonca »

I appear to be having a similar problem.

I've gone through the steps of creating a database from a spreadsheet. Querying the database returns the records just fine.

However, when I go to create labels, I get garbage. The template file appears fine (i.e., the sheet of labels looks like what my physical sheet looks like, the fields appear to be the correct fields), but when I mail merge, the resulting sheet prints out wrong. My label sheets have 8 labels, 2 cols X 4 rows. The label in spot 1,1 is correct--on the first page, it corresponds to my first record, on the second page it corresponds to my 9th record, etc. All of the other labels, however, are filled with the data from the first record.

Any suggestions?

Other info that may be pertinent: I had to setup a custom label sheet since I didn't have the template for the labels.
OOo 2.4.X on Ubuntu 8.x
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Creating a mailing label from a spreadsheet

Post by TheGurkha »

Follow these tutorials, they are very informative and step by step. You won't need all of the information in them, but within them will be what you need to know to cover your immediate needs.

Here's a long page of all kinds of useful stuff to do with labels: Labels Tutorials

Mail merge tutorial: FSM Mail merge Turorial
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Post by thomasjk »

Go to the first label in your Template and turn on View--->Field Names. You most likely don't have a Next Record field. Insert the field and then copy the contents of the first label to the other labels in your template. Then turn off View-->Field names. If you used the File-->New-->Labels Wizard then just click the synchronize button to do the copying.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
pearsonca
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Post by pearsonca »

Those were the directions I initially followed, and when I saw the advice previously about extra next records, I went looking to see if I was perhaps missing them. I checked again, they are still there. Repeating those instructions also gets me the same problem.

Any other suggestions?
OOo 2.4.X on Ubuntu 8.x
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Post by thomasjk »

Can you post a sample file? How to attach a document here
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
pearsonca
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Post by pearsonca »

Sure. I've attached both the base template (labelsource) and the resulting file (labels) when I print (to file, obviously).

Edit: The labels I'm trying to print are for class nametags; the input spreadsheet has First name, Last name, and Team #. As you can see from the files, I want First name (large), Last name (smaller), and Team # on the labels.
Attachments
labelsource.odt
(11.44 KiB) Downloaded 1144 times
label.odt
(11.86 KiB) Downloaded 1050 times
OOo 2.4.X on Ubuntu 8.x
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Post by acknak »

Looking at the field names, I don't see any problem with the fields in your document either. It looks ok to me.

If I hook your merge document (labelsource.odt) up to a spreadsheet data source of mine (I created a query to match your field names), the merged output is correct.

I wonder if it's something off with your data source? There have been problems in the past with spreadsheet data sources not always being refreshed correctly. That was supposed to be fixed in 2.4.1 (as I recall), but maybe there are still some glitches.

You could try making a new database, but it would just be a shot in the dark.
 Edit: PS: 
Oops, wait a sec. When you open the merged output document, decline the offer to update the links. That's what's causing the duplicates.
AOO4/LO5 • Linux • Fedora 23
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Post by thomasjk »

I'm a bit stumped here. The template looks OK as does the label file. Each label in the label file has a different record from the first. So I don't see the problem you describe where the first label contents are replicated on the first page and so on. In Print preview I see blank pages being inserted: Pages 2, 4 and 6 are blank. In the Template go to Tools--->Options--->Openoffice.org Writer---->Print and uncheck the box that says "Print Automatically Inserted Blank Pages". Maybe someone else with more expertise can suggest something else.
I see Acknak has solved it. :D
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
pearsonca
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Post by pearsonca »

acknak wrote:
 Edit: PS: 
Oops, wait a sec. When you open the merged output document, decline the offer to update the links. That's what's causing the duplicates.
Worked like in a charm. Now that I have my labels, where is a good place to note this as a usability problem, so no one else snags on it? Or is this thread sufficient?
OOo 2.4.X on Ubuntu 8.x
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Post by acknak »

You can file a request for enhancement with the OOo issue tracker: [Tutorial] Reporting bugs or suggestions
Last edited by acknak on Fri Jan 16, 2009 7:15 am, edited 1 time in total.
Reason: Fixed broken link
AOO4/LO5 • Linux • Fedora 23
TonyG
Posts: 1
Joined: Mon Dec 17, 2012 3:27 pm

Re: Creating a mailing label from a spreadsheet

Post by TonyG »

I have been trying to create mailing labels using Open Office Writer (Wordprocessor) - and with a spreadsheet as a data source. The first row of the spreadsheet contains field names e.g. "Style of address for both" - " his initial" - "surname" - "address 1" - "address 2" - "zip code" - and so on.

I have managed to print a sheet of labels (spacing on the labels is fine) - but unfortunately EACH AND EVERY label contains the above field name headings. My objective, naturally, has been to have the different names and addresses of the people (in the rows beneath) showing individually on different labels. Can anybody please very kindly tell me what the missing ingredient of my construction of the labels file in Open Office Wordprocessor is likely to be?

Many thanks
Tony
Open Office version 3.1
and Windows 7
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Post by thomasjk »

Turn off View--->Field Names in the Writer document.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Webber
Posts: 6
Joined: Thu Aug 22, 2013 7:23 pm

SOLVED! Re: Creating a mailing label from a spreadsheet

Post by Webber »

I think I am having the same problem as the previous person. I have done the Address Data Source Wizard, and it is listed under View → Data Sources. Then I followed the File → New → Labels Wizard. My Fields seem to be set up fine and the page prints with good spacing for the Avery 5160 labels. The problem is that on every label it prints:
"<Name>
<Address 1>
<Address 2>
<Zip>"
When I switch to View → Field Names, every label reads:
"Contacts.Antiquarian.Name
Contacts.Antiquarian.Address 1
Contacts.Antiquarian.Address 2
Contacts.Antiquarian.ZipNext
record:Contacts.Antiquarian"

I get nothing transferred from my spreadsheet. I downloaded the latest version of OpenOffice, 4.0.0, and was prompted to have all my info updated with the new version, which I did. I went through all the motions again and it is still happening. Besides this thread I also read related links posted in this thread and elsewhere online.

My spreadsheet has the above titles across row 1, row 2 is blank and row 3 begins the addresses. There are no spaces between columns. What am I missing?

Thanks, starting the print process with Ctrl-P and clicking yes for a "form letter" did it!
Last edited by Webber on Fri Aug 23, 2013 9:57 pm, edited 2 times in total.
OpenOffice 4.1.7 on Windows 10 Home/version 2004, 4GB of ram
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a mailing label from a spreadsheet

Post by Villeroy »

@Webber
Start the print process (Ctrl+P)
Confirm that you are going to print a serial letter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply