OpenOffice.org Training, Tips, and Ideas

In OpenOffice mail merge documents, you see the field names like Name onscreen, not the data like Bob. The data appears when you print.

I just wanted to clarify something since I hate to see people thinking they're doing something wrong when they're doing it right.

Here's an example using labels.  This is how it's supposed to look. You won't see the data onscreen, you'll see the field names like first name. When you print, the correct data will appear.

If you don't know want to print all the data, you can pick the ones you want in this  window. Just choose File > Print, click yes that you want to print a form letter, and then select the records you want in this window. Select the first record, hold down Ctrl and select the next one, and so on.

Or specify a range.

Then just click OK.

A Huge Printable PDF Article on Advanced OpenOffice.org Report Techniques, Including SQL-Based Calculated Fields on Reports and Using the Next Record field (Repost)

I decided to repost this since it's got lots of really fun powerful NOT obvious technical stuff for reports.

---------------------------------------------------------------------

Here's the TOC.

A Quick Review of the Report Writer Tool

What You Can Do in the Query Design Tool and SQL View

What You Can Do With Report-Specific Fields

Quick-and-Dirty Reports

Creating Your Own Reports Using the Next Record Field

Powerful Reports Can Be Fun....If You Enjoy Getting Your Hands Dirty

 

Here's the link. It's a big PDF.

---

---

Starting a new database: spreadsheet or native Base database?

Let's say you've got a bunch of data: addresses, or lists of your DVDs, or whatever. It's not world-changing high-transaction information, but you want it in a good storage format

Do you create a spreadsheet and put your info in that, or do you go straight for the higher-end solution and create a Base database?

The good news, it doesn't matter that much. Generally, I would recommend that you start with the spreadsheet, then if necessary go to the Base format. The transition isn't a matter of right-clicking and choosing Convert but it's not bad. Pasting, essentially.

Follow these directions to get your data in a spreadsheet, then make a database file that points to that spreadsheet. Your spreadsheet might be called mymailinglist.ods and your database file might be called mailinglistdatabase.odb. Note the different file extensions. (You can call the files Bob.ods and Mary.odb if you want; these are just examples.)

Then if you find you want to do more complex operations like queries, data entry forms, etc. follow the directions in this article, also below, to turn your spreadsheet into a true Base database.  You create a new database file, like mynativedatabase.odb, and paste the contents of your spreadsheet into it. You will then use mynativedatabase.odb instead of mailinglistdatabase.odb.

Turning a spreadsheet into a native Base database

Creating or opening a new database

Now, you get to choose what database file you want to bring the data into.

If you've already got a database file
If you've already got a database, open it up by choosing File > Open. The database file can have existing tables in it; you can add new tables to it from the external data or you can append the external data to an existing table if the data has the same columns.

If you need to create a database file
If you don't have a database, you need to create one. Choose File > New > Database. Choose to create a new database and click Next.

In this window, just leave everything as is and click Finish.

You'll be prompted to save the file. Save it under any name you like and click Save.

You'll see the database file open up like this. You're ready to continue to the next step.

Bringing the data into your database

This is the simplest part. Open your spreadsheet, and paste it into your new database.

You heard me -- that's all there is to it. Well, there's a little more, but it's very easy. When you paste, you'll create a new table with the pasted data.

Note that you can append to an existing table or create a new table. I'll cover appending later in this section.

Creating a new table
Here's what the process looks like to create a new table.

1. First, in the spreadsheet, select all the data you want to bring in. Do include the column headings like Name, Address, etc.

   

2. Now move over to your database file. Click the Tables icon on the left, since you're going to be adding a table.

   

3. Right-click in the table area and choose Paste.

   

4. And you'll see this wizard, which will walk you through the rest of the process.

   

5. Name the table and leave the Definition and Data item selected. Also, decide whether you need to create a primary key. If you've already got something like a customer ID that is unique, you don't need to. If you don't, choose to create a primary key. Click Next.

   

6. In the next window, insert all the fields you want to bring over. You can use the >> button to add them all. Click Next.

   

7. In the Type Formatting window, be sure that the file types and other settings are correct. Then click Create. If you're prompted to create a primary key, you can do it now or do it later (I'll cover that toward the end of this article).

   

8. Your table appears in the database.

   

Appending data to an existing table
You approach this the same way as adding. But in the first wizard window, choose to append, and type the name of the table to append to.

Click Next. Line up the fields you're pasting with the fields that are already there. You can unmark fields to import and use arrows on both sides to make sure that the fields are lined up correctly. The fields don't have to have the same names: Zip will import correctly into Postal Code as long as the data types and other factors are correct.

Click Create, and the data will be added to the table.

Editing and viewing the imported data

Once you've got the data in, you can edit it normally. Here are a few common tasks.

Editing the table definition
In the main database window, right-click on the table name and choose Edit. The window will look something like this. Make changes to field names, types, etc. and save changes.

Opening the table to view or add data
In the main database window, double-click on the table name. The window will look something like this. Make changes to data and save changes.

Specifying a primary key
Each table must have a primary key. Right-click on the table name in the Tables area and choose Edit. In the window that appears, right-click on the name of the field that should be primary key and choose Primary Key. Close the table, saving changes.

Conclusions

This is a pretty straightforward approach -- once you know about it, of course. I think something along the lines of, oh, an option called Import under the Tools or File menu would have been nice; let's hope it'll be in the next version.

But enough "wouldn't it be nice" for now. Returning to the metaphor I began with, you've now got a front door in your database so that you can get the data in. But what if you want to get data out again? Let's say you want to take your table, query or view, and you then want to export it to a spreadsheet or CSV text format. Again, there is no easy way. There is no option under File or Tools. But there is a way. And it's the topic for my next article.

---

---

How to turn your spreadsheet into a database for mail merges in OpenOffice

Got labels?

I've been pointing folks to the instructions in this blog, but I wanted to pare things down so that I have one blog entry, just one, that shows only how to make your spreadsheet into something you can use in mail merge letters, label mail merges, envelope mail merges, etc.

Overview of How Mail Merges Work in OpenOffice

First: To print labels from a list of addresses, you should  put them in a spreadsheet. (You don't have to since text files, address books, and regular databases are fine for holding your data for mail merges, too. But spreadsheets are a simple approach.) Put headings across the top like Firstname, Lastname, etc.

Then: To make your spreadsheet into something that your mail merge document (labels, Christmas letter, etc.) can talk to, you need to make a little database file that points to your spreadsheet.

Finally: You make your mail merge document (labels, Christmas letter, etc.) and have it point to your database.

That's the summary. Here are the specifics.

Instructions for the First Step, Putting Together Your Data in a Spreadsheet

Here's what your data should look like. The columns you use are up to you. Put in whatever data you will need. Put City, State, etc. all in different columns. Be sure to put the label at the top for what the field is: Name, etc. This is important.

Here's a sample file. Right-click on the link and choose to save it to your computer.

Instructions for the Next Step, Making Your Spreadsheet Into a Database

You need to make a database file that points to the spreadsheet.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is. This is the name that you will see in your lists of databases.
 

You're done with the database.

Instructions for Making Labels or a Letter and Pointing It at a Database

To make labels that point to the database you created, follow these instructions. You've already done the database so

http://openoffice.blogs.com/openoffice/2006/07/mail_merge_labe.html

To write a letter that points to the database you created, follow these instructions, steps 3 and 4. You've already done 1 and 2.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

To do envelopes that point to the database you created, follow these instructions.

http://openoffice.blogs.com/openoffice/2007/08/printing-envelo.html

I strongly advise you NOT to use any of the mail merge wizards under the Tools menu. Far too complicated.

---

---

Rough Draft: Video Tutorial of Creating a Database and Simple Mail Merge

This is not fancy. There are a couple goofs. But it is accurate, and it's a video. Let me know how you like it!

~~~~~
I realize the size is quite big -- one thing that isn't obvious because of that is the navigation tools way at the bottom. (I'm pretty sure that they show up for everyone; I hope they're not just appearing for me somehow because I have Captivate installed.)

Sun Report Builder Extension

Anyone who's worked with the OpenOffice.org Base report writer knows that it's....a first generation product. It works but it doesn't have huge features. So I’m particularly glad to see some work being done with reports, in the new Report Builder extension from Sun.

http://extensions.services.openoffice.org/    for all extensions

http://extensions.services.openoffice.org/project/reportdesign   for the Sun Report Builder

The Report Builder extension looks like it has a lot of powerful features, though not exactly easy to see how to use. I’ve spent a few hours with it and one thing that bugs me a bit is that the tab for selecting the data source for the report disappears if you click on something else first. Ease of use aside, though, it does have quite a feature set, including grouped records, sorting of records, different alignment of text fields, and calculations.

I'm going to have to spend a lot more time with this to really figure it out and give some procedures, but here's a short tour of the basics.

To use the Sun Report Writer extension, download and install it first. (Tools > Extension Manager). Then open the .odb database file for the database you want to create a report for. Choose Insert > Report, and you’ll see the report writer interface.

 

This is the tab that disappears too quickly. Select Table or another type of data, then select the actual source. Once you make that selection, the Add Field palette appears; use it to drag fields onto the appropriate section of the report.
   

Click the Sorting and Grouping icon on the toolbar to get this window where you have a lot of control over how the fields and the report behave.

 

When you’ve dragged fields onto the report, set options, inserted page numbers, and done other formatting, save the report. The report will show up in the Reports area of the main editing windows of the .odb database file.

 

---

---

Summary of new features in OpenOffice.org 2.3

Here’s a summary of the features from the 2.3 new features list that I considered the most useful or important to write about. This page  http://wiki.services.openoffice.org/wiki/New_Features_2.3 about the new features is an excellent guide, as well.

General

• This is convenient for anyone who prints to multiple printers, all over the world. You can load or ignore the printer settings for your documents. This means you don’t end up accidentally printing to the printer in building 4 which is on the opposite side of the country, just because you were on a business trip there last week and that’s where you last printed your document.
  

• If your document isn’t wider than the OpenOffice.org window, then it will be centered in the window, not left-aligned.
  

• Lots of locale information was added, for locations such as Tagalog, Frisian, and Hausa.
  

Writer and Web

• The HTML editor now has a preview feature. Choose File > Preview in Web Browser and the document opens in the default browser.

• I love this feature. You know how when you get a hyperlink but then want to retype it or reformat it, but clicking on it takes you to the target of the link? No more. You can select hyperlinked text all you want; you now have to Ctrl Click to open a link. This is very nice.
  

• The notes say that there is a new compatibility option on Tools > Options > OpenOffice.org Writer > Compatibility: Do Not Justify Alignment in Lines Ending With Manual Line Break. However, I’m mentioning this because I couldn’t see it. The illustration shows the compatibility options that are there.
  

• When you open the Styles and Formatting window (Format > Styles and Formatting), you can set what kinds of styles you wanted to see: Applied, Custom, Automatic, etc. Previously, you had to reset this every time you opened a new document or re-opened OpenOffice.org. Now, thankfully, that category will stick. The setting is saved per application. However, the choice you make for Paragraph, Character, Frame, List, or Page doesn’t stick.
  
  

• When you right-click on text, you used to see Default as one of the options. Now you see Default Formatting, which is clearer. (Default Formatting is a great way to just clear out any extraneous formatting and apply the default style to the selected item.) This is a very nice feature regardless of the text; for one thing, it’s the best way to remove the hotlink from a URL.
  

• A new export filter lets you export to MediaWiki format. Choose File > Export and select MediaWiki in the file format list.

Calc

• This is a very, very smart change. By default, the print options for Calc are now set to Print Only Selected Sheets and Suppress Output of Empty Pages. If the Print Only Selected Sheets option is enabled, the Calc page preview shows only the displayed sheet and the message “There is nothing to print.” To change these options, choose Tools > Options > OpenOffice.org Calc > Print, or choose File > Print and click the Options button.
  

• Here’s another very smart change that will screw up all my documentation. :) The SUM icon on the main Calc toolbar has changed. Now you can select the range of numbers to add, click the SUM icon, and get the total in the first cell below the selected range. Phew. But if you liked it the old way, it still works that way, too.
  

• Graphics can be linked to macros. This should help with Excel compatibility.
  

• The Excel export filter now handles the cotangent functions COT, ACOT, COTH, and ACOTH.

• Calc now supports inline matrix/array constants in formulas. An inline array is surrounded by curly braces '{' and '}'. Elements can be each a number (including negatives), a logical constant (TRUE, FALSE) or a literal string. See this link for more detail. http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=230

• You can now use dynamic ranges, rather than absolute ranges defined with $, in lists in Data Validity. Choose Data > Validity, and under the Criteria tab select Cell Range from the list.
  
  

• The GETPIVOTDATA function returns a result value from a DataPilot table, so it can be used in a cell formula.
  

Mail Merge, Databases, and Forms

• The infamous checkbox on the print message when you print a mail merge document, Do Not Show Warning Again, is gone. Phew! See this blog http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html for why that caused problems.
  
  

• This is nice. When you choose File > Print with a mail merge document, in the Mail Merge window, you can choose to save the document as separate documents or as one document.
  
  

• Unfortunately, in Base there is still no File > Export or File > Import feature. File > Export does appear, but it’s dimmed.

---

---

A Huge Printable PDF Article on Advanced Report Techniques, Including SQL-Based Calculated Fields on Reports

I realized that while this was published on TechTarget, I hadn't made much of a big deal of it here.

Here it is. Lots of fun, techy stuff about reports. Here's the TOC.

A Quick Review of the Report Writer Tool

What You Can Do in the Query Design Tool and SQL View

What You Can Do With Report-Specific Fields

Quick-and-Dirty Reports

Creating Your Own Reports Using the Next Record Field

Powerful Reports Can Be Fun....If You Enjoy Getting Your Hands Dirty

 

Here's the link. It's a big PDF.

---

---

Much easier, much simpler ways to print the contents of a database in OpenOffice.org Base

I've been so caught up in the complicated tools and cool hacks that I've forgotten about the really simple ways to get data from a database into a Writer document.

I'm so embarrassed.

Here's a simple way to bring in data, whether it's still in fields, or just plain text. You probably want to use this instead of the Reports feature, and instead of the Next Record field under Insert > Fields > Other, Database tab.

Here are some screen shots. I'll do more on this later but I just wanted to slap these options out there so you know about them.

Click any of the screen shots to see them bigger and in more detail.

1. Create a new text document and choose F4.

2. Click the + by your database, click the + by the word Table (or Query) and click the table or query you want to print.

3. Click the blank gray square shown, to the left of the first fieldname.

4. Click and hold down on that gray square and drag into the document.

5. Now you have various options.

Pick the option you want, insert the fields you want, apply any relevant formatting, and click OK.

Table

Fields

Text

Then print. If you bring in the data in fields, when you choose Print, click Yes when prompted and DON'T click the checkbox.)

Again, if you're printing in fields, you cna print to files or a printer. To select specific records, select the first record, hold down Ctrl, select another record, and so on.

 

 

---

---

Updating the Preview in Your Mail Merge Docs so You See Different Data in Every Field

This is a very specific but annoying problem for which I've found a workaround (thanks to the smart folks in the training department at MASCO! ;> ).

A Little Background: Synchronize
So, you've got a mail merge doc. It's labels. You've set it up to Synchronize, so that when you make a change to the upper left address and click Synchronize, all other addresses take on the same updates. I.e. if you make the text blue, or insert a graphic, or whatever, you just make the change in one place and it updates all the others. This is a very nice feature.

Here's where you set synchronize. File > New > Labels, and in the Options tab select Synchronize contents.

Here's what a new sheet of labels looks like with the Synchronize floating button:

Here's what it looks like when you change the formatting for the upper left address:

Here's what it looks like when you then click Synchronize:

A Little Background: Preview
As you can see above, you see the field names, not the field contents, in mail merge documents. To see the contents, press F4 to view your data sources. Expand the data source you're using, select the table you're using, click the blank gray box that's circled to select all the rows, and click the also-circled icon called Data to Fields.

Here's what it all looks like.

But! What Happens When You Preview, Then Synchronize???????
Let's say you've previewed so you can see the data .Then you think, "Hey, the font needs to be different, and I want it to be green." So you make that change to the upper left frame like normal, you click Synchronize....and you get this. All the content is the same.

Don't Worry: It's Easy to Fix
You just click that same Data to Fields icon. Select all the data just like before, click the Data to Fields icon again, and this refreshes the display and gives you back the normal content.

---

---

Suppressing Blank Address2 Fields in OpenOffice.org Labels, Envelopes, or Other Documents, for Mail Merges

Here it is -- suppressing a blank Address2 field in your mail merges. It's not extremely simple, but it's reasonably straightforward and it works.

Here's the situation we're addressing. Sometimes your addresses have two lines for the address part, sometimes they don't.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave
Broomfield, CO 82211

But you have to put in the <Address2> field for everybody, since it's a mail merge. The setup has to be the same.

<Firstname> <Lastname>
<Address1>
<Address2>
<City>, <State>, <Zip>

But with this approach, your addresses look like this.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave

Broomfield, CO 82211

Ick. How do you suppress that second Address2 line and the corresponding carriage return if there's no content for a particular record, for that Addres2 field?

Select the Address2 field in your mail merge document, choose Insert > Section, and create a conditionally hidden section with this formula.

databasename.tablename.fieldname EQ ""

Here are the details, using an example of labels.

1. Create the labels for mail merge as usual. File > New > Labels, select your database and tables, insert the fields, etc.

2. Choose the Synchronize Contents checkbox.

3. Click New Document.

4. Here are the labels.

5. Turn on nonprinting characters if they're not on already.

6. Select the first soft return, shown selected.

7. Press Return or Enter to replace it with a hard return.

8. Repeat, to make them all hard returns.

9. Click Synchronize to update the other labels to be the same.

10. Select the Address2 field.

11. Choose Insert > Section.

12. Name the section Suppress. Select the Hide checkbox and type the following condition. The screen shot shows the syntax.

Syntax
databasename.tablename.fieldname EQ ""    (the last part is two double quotes together)

Example
databasewithtwoaddresslines.Table1.Address2 EQ ""

NOTE: if you are using the Thunderbird address book as a data source, you need to use square brackets if the field name includes a space (i. e.: [Address 2]=="") to hide the second line of the address if it the Address 2 field is blank.)  I would suggest in general avoiding field, table, or database names with spaces.

http://www.oooforum.org/forum/viewtopic.phtml?t=43528&highlight=

Click the screen shot to see it bigger. It shows the syntax, not an actual example.

13. Click Insert.

14. Click Synchronize.

15. Now preview the data or print the data and you'll see that it prints correctly.

15. If you need to change the section, select it in the first address and choose Format > Section. Select the one named Suppress for the master label and make changes, then click OK. Click Synchronize again in the labels.

---

---

Huge, detailed list of links for using macros in OpenOffice.org

This huge and excellent list was put together by the helpful and brainy folks at www.oooforum.org, including SergeM.

http://www.oooforum.org/forum/viewtopic.phtml?t=50952

I don't write macros myself, but if there's something you're doing with macros that you can't find help with on that page, I imagine you're doing something with macros that's either very advanced, like gene research, or something that just isn't possible.

---

Beware! Saving in Word format, and mail merges, don't mix.

Mail merges don't work if you're saving your OpenOffice document in Microsoft Word format.

Whether you're doing this manually, or doing it with the window under Tools > Options > Load/Save > General, saving in Word format will strip out the mail merge fields' connection to the database.

So you can save in .doc by default, but you need to save the mail merge documents, whether they're labels, envelopes, or other documents, in OpenOffice.org native .odt format.

---

Farrrrrr Simpler Exporting Data From OpenOffice Base to a Spreadsheet

Stop the presses. Forget that one article I wrote.

In OpenOffice.org 2.2 you can now export data in a very reasonable manner. Almost identical to the (albeit invisible) import method.

1. Open your database .odb file.
2. Click the Tables icon.
3. Right-click on the name of the table to export. Choose Copy.
4. Go to a spreadsheet. Paste.

The data will appear.

It's so beautiful and simple.

---

 

Sample Project for Joining Tables, Regarding Primary Keys

If you don't spend every day hip deep in databases, you might not always be clear on the whole primary key thing, and how to join them.

I want to clarify that this will work.

Let's say you've got two tables.

One is a table with city residents' IDs and names, street numbers, street names, etc.

The other is a table with street names and the days on which garbage is collected.

You need to send people a letter telling them what day their garbage is collected on. Thus you need to link the two tables.

The primary key on the first table, the ID field, is different than the primary key on the second. The primary key on the second could be the street name since the street name is unique in that table.

1   Oak    Monday
2  Elm     Monday
3  Main  Tuesday

and so on.

To create the query, you don't need to link the primary keys. You can link the two tables simply by connecting the two Street Name fields when you create the query.

1. Open the database file containing the two tables.
2. Click the Queries icon at the left.
3. Click the option to create a query in design view.

4. In the window that appears, select the first table name and click Add.

5. Select the second table name and click Add.

6. Click in the second table, on the common field, in this case the StreetName field, and drag your mouse to the other table's StreetName field. (If this doesn't work, drag from the first table's field to the second table's field.)

7. Now create your query. Double-click the name of any field you want in the query. In this case it might be Name, Street Number, Street Name, and Garbage Day.

Run the query by clicking the Run Query icon.

The results will appear.

Save the query and close it.

Now create your mail merge. You can create a new Text Document and choose View > Data Sources. Open the database containing the query and under Queries select the query you created

Drag the fields you want into the letter. You might want it like this.

---

Creating Calculated Fields in OpenOffice Base

When you make a database, you of course have it chock full of data.

Contractor name, Contractor pay rate, Number of hours the contractor worked, etc.
Invoice ID, Item price, Number of items purchased, etc.

Now, let's say you want to figure out something based that data. The pay rate times the hours to equal the total amount paid, for instance.

To do that, you make a query.

1. Create a database. (File > New > Database, select the first radio button in that window and continue.)
2. Create or add a table.  (Choose the Wizard or design view.)

<>

3. Open the database file. (File > Open and find the .odb file.)
4. Click the Queries icon at the far left.

5. Click Create Query in Design View.

6. In the window that appear, select the table that contains the fields you need to calculate and click Add.

7. The table will appear in its own separate window. Add all the fields that you want in the query -- you might all of them. To add a field, double-click it. It will appear in the design area at the bottom.

8. To add the calculation, click in the top cell of the first blank column. Type fieldname*fieldname

<>

For instance, to multiply the contents of Payrate by Hoursonjob,   type
Payrate*Hoursonjob

In the cell below that, the cell to the right of the Alias label, type the name you want to use to refer to this calculated field, like TotalPaid.

Then click the Run Query icon to run the query.

You'll see your results, with the calculation.

For any of the columns, including the result, you can apply currency formatting. Right-click on the column heading and choose Column Format.

Select the format you want and click OK.

The formatting is applied.

Now you can:

• Just view the data
• Run a report based on the query
• Create a mail merge of some sort based on the query

---

OpenOffice.org Base Queries: Switching Between SQL and Design View

One of the simplest ways to write a query in OpenOffice Base, while still not having to be a programmer, is to use the Design view. Or even if you're as comfortable with SQL as anything in life, design view is still kind of a pleasant little tool.

To create a query in design view, open the database file, click the Queries icon, and click the Design View option.

You can click Add to add the table or tables to the query, then double-click each field you want in the query. It appears in the query design area. Add your functions, criteria, etc. Run the query if you like.

Now, let's say you have a sudden attack of the SQLs. You can just choose View > Switch Design View On/Off and the view will change to SQL.

Here's the SQL view.

Make the same menu selection again and the view changes back.

---

Very cool feature in OpenOffice Calc spreadsheets: Edit > Paste Special, Transpose

Chris from the Boulder LUG pointed this out to me last night while I was singing the praises of Edit > Paste Special in OpenOffice.org.

Let's say you've got this data.

It's accurate, but you want the columns and rows in different positions. You want it flopped 90 degrees like this.

Here's all you need to do.

1. Copy the original data.

2. Choose Edit > Paste Special.

3. Leave All selected and select Transpose.

4. Click OK.

And there you are, with the whole thing flopped around the way you want it.

Now just delete the old data, drag the pasted data over it -- whatever you want.

---

Mail Merge in OpenOffice Writer: Creating Mail Merge Documents From Text/CSV or Spreadsheets

I've got a lot of info out there, including lots of coverage in my book, about mail merges. However, I don't have a nice simple straightforward blog on it with everything in the same place all spelled out. Didn't, that is. This is all you need to do to make a nice simple document based on data in text files or spreadsheets.

What You Have to Do

1. Get your data. You've already got it, probably. This blog  is for people with data in text files, and in spreadsheets.

2. Turn it into a data source.

3. Create your mail merge document and suck the data in through the data source.

4. Print, specifying how many of the data records you want to print for, and whether to print to a file or printer.

1. Get Your Data

You probably already have it. It's in a .txt file or .csv that's comma or tab separated, perhaps. Or it's just a spreadsheet.

2. Make the Data Source: Text File Instructions

If your data is in text files, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Text as the format.

3. Click Next.

4. Specify the DIRECTORY where the text files are. Each text file in that directory will be a table in your database. Then select the item separating fields, i.e. a tab or comma or something else.

5. When all the settings look correct, click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.

You're done.

2. Make the Data Source: Spreadsheet Instructions

If your data is in a spreadsheet, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.
 

You're done.

3. Create Your Mail Merge Document and Suck the Data In From the Datasource

You can also use the simple or complex mail merge.

Simple:  http://openoffice.blogs.com/openoffice/2006/03/techtarget_arti_1.html

Complex: http://openoffice.blogs.com/openoffice/2006/02/techtarget_arti.html

But this is a nice way to do it too.

1. Create a new Writer document or open a document containing text that you want in the mail merge document.

2. Choose View > Data Sources. Everything you've created will be displayed. Click the + sign by the data source you want to use, then click + by Tables til you see the data you want to use.

3. Type any content you want and do any formatting. You can do this later too.

4. Click on the NAME OF THE FIELD, not the piece of data, that you want in the mail merge.

5. Drag it into the document and release. The field name will appear.

 

6. Add any other content and fields you want.

Save the document. You're ready to print.

4. Print the Mail Merge Document.

1. Choose File > Print.

2. You'll see this message. Click Yes. DON'T MARK THE CHECKBOX SAYING YOU DON'T WANT TO SEE THE MESSAGE AGAIN.

3. In the print window, specify the range of records, if you don't want them all, and specify to print to a printer, or to files.

4. Click OK.

5. In the print window, specify the printer and click Print.

---

Modify the Layout of Prefab OpenOffice Labels Under File > New > Labels