Apache OpenOffice Community Forum

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.

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/.

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.

Yay! That did the trick! Thank you both for your help! You saved me a ton of time!

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?

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.

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 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.

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

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.

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?

Can you post a sample file? How to attach a document here

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.

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.

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.

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?

You can file a request for enhancement with the OOo issue tracker: [Tutorial] Reporting bugs or suggestions

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

Turn off View--->Field Names in the Writer document.

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!

@Webber
Start the print process (Ctrl+P)
Confirm that you are going to print a serial letter.