Creating a mailing label from a spreadsheet

Writing a book, Automating Document Production - Discuss your special needs here

Creating a mailing label from a spreadsheet

Postby rebeccaferguson » Wed Jan 07, 2009 10:43 pm

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
rebeccaferguson
 
Posts: 5
Joined: Wed Jan 07, 2009 10:32 pm

Re: Creating a mailing label from a spreadsheet

Postby thomasjk » Wed Jan 07, 2009 11:31 pm

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 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3602
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Postby acknak » Wed Jan 07, 2009 11:37 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17400
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Postby rebeccaferguson » Thu Jan 08, 2009 9:32 pm

: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

Postby rebeccaferguson » Fri Jan 09, 2009 3:57 am

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
rebeccaferguson
 
Posts: 5
Joined: Wed Jan 07, 2009 10:32 pm

Re: Creating a mailing label from a spreadsheet

Postby thomasjk » Fri Jan 09, 2009 6:17 am

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 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3602
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Postby acknak » Fri Jan 09, 2009 5:55 pm

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!
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17400
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Postby pearsonca » Tue Jan 13, 2009 11:29 pm

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
pearsonca
 
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Postby TheGurkha » Wed Jan 14, 2009 12:05 am

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 13.04 Raring ringtail, LibO 4.0.2.2 (Build ID 400m0 (Build:2))
Gurkha Welfare Trust
User avatar
TheGurkha
Moderator
 
Posts: 6426
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: Creating a mailing label from a spreadsheet

Postby thomasjk » Wed Jan 14, 2009 12:18 am

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 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3602
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Postby pearsonca » Wed Jan 14, 2009 6:11 pm

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
pearsonca
 
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Postby thomasjk » Wed Jan 14, 2009 7:16 pm

Can you post a sample file? How to attach a document here
Tom K.
Windows 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3602
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Postby pearsonca » Wed Jan 14, 2009 9:54 pm

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 685 times
label.odt
(11.86 KiB) Downloaded 679 times
OOo 2.4.X on Ubuntu 8.x
pearsonca
 
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Postby acknak » Wed Jan 14, 2009 11:36 pm

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17400
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Postby thomasjk » Thu Jan 15, 2009 12:12 am

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 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3602
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Creating a mailing label from a spreadsheet

Postby pearsonca » Thu Jan 15, 2009 4:15 pm

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
pearsonca
 
Posts: 4
Joined: Tue Jan 13, 2009 11:22 pm

Re: Creating a mailing label from a spreadsheet

Postby acknak » Fri Jan 16, 2009 12:18 am

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
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17400
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Creating a mailing label from a spreadsheet

Postby TonyG » Mon Dec 17, 2012 4:31 pm

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
TonyG
 
Posts: 1
Joined: Mon Dec 17, 2012 3:27 pm

Re: Creating a mailing label from a spreadsheet

Postby thomasjk » Mon Dec 17, 2012 6:45 pm

Turn off View--->Field Names in the Writer document.
Tom K.
Windows 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3602
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

SOLVED! Re: Creating a mailing label from a spreadsheet

Postby Webber » Thu Aug 22, 2013 8:31 pm

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.0.0 on Windows 7
Webber
 
Posts: 1
Joined: Thu Aug 22, 2013 7:23 pm

Re: Creating a mailing label from a spreadsheet

Postby Villeroy » Thu Aug 22, 2013 9:55 pm

@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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17291
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Advanced Uses

Who is online

Users browsing this forum: No registered users and 3 guests