Creating a mailing label from a spreadsheet
-
- Posts: 5
- Joined: Wed Jan 07, 2009 10:32 pm
Creating a mailing label from a spreadsheet
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
Re: Creating a mailing label from a spreadsheet
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
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Creating a mailing label from a spreadsheet
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.
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
-
- Posts: 5
- Joined: Wed Jan 07, 2009 10:32 pm
Re: Creating a mailing label from a spreadsheet
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
-
- Posts: 5
- Joined: Wed Jan 07, 2009 10:32 pm
Re: Creating a mailing label from a spreadsheet
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
Re: Creating a mailing label from a spreadsheet
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
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Creating a mailing label from a spreadsheet
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!
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
Re: Creating a mailing label from a spreadsheet
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.
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
Re: Creating a mailing label from a spreadsheet
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
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
Gurkha Welfare Trust
Re: Creating a mailing label from a spreadsheet
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
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Creating a mailing label from a spreadsheet
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?
Any other suggestions?
OOo 2.4.X on Ubuntu 8.x
Re: Creating a mailing label from a spreadsheet
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
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Creating a mailing label from a spreadsheet
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.
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 1150 times
-
- label.odt
- (11.86 KiB) Downloaded 1054 times
OOo 2.4.X on Ubuntu 8.x
Re: Creating a mailing label from a spreadsheet
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.
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.
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: |
AOO4/LO5 • Linux • Fedora 23
Re: Creating a mailing label from a spreadsheet
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.
I see Acknak has solved it.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Re: Creating a mailing label from a spreadsheet
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?acknak wrote: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.
Edit: PS:
OOo 2.4.X on Ubuntu 8.x
Re: Creating a mailing label from a spreadsheet
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
Reason: Fixed broken link
AOO4/LO5 • Linux • Fedora 23
Re: Creating a mailing label from a spreadsheet
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
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
and Windows 7
Re: Creating a mailing label from a spreadsheet
Turn off View--->Field Names in the Writer document.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
SOLVED! Re: Creating a mailing label from a spreadsheet
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!
"<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
Re: Creating a mailing label from a spreadsheet
@Webber
Start the print process (Ctrl+P)
Confirm that you are going to print a serial letter.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice