Mail Merge Data Sources:

Converting Word Tables To Calc Or dBase

Contents

Contents	2

Overview	3

Copyright and trademark information	3

Feedback	3

Acknowledgments	3

Modifications and updates	3

Introduction	1

A Fast Conversion Scheme	1

Copying The Word Table	1

Pasting in Calc	2

Saving The Final Document	3

A “Lengthy” Conversion Scheme	3

Opening The Original Word File	3

Converting The Table To Text	3

Saving	4

Importing The CSV File into Calc	5

Saving The Final Document	7

Miscellaneous information	8

Calc or dBase ?	8

More Information	8

Public Documentation License, Version 1.0	9

Overview

This documentation describes the steps necessary to convert any existing Word table documents used for mail merge with MSOffice towards Calc spreadsheets in order to use them as data sources for the same purpose with OpenOffice.org.

Copyright and trademark information

The contents of this Documentation are subject to the Public Documentation License, Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. A copy of the License is available at:
http://www.openoffice.org/licenses/PDL.rtf

The Original Documentation is Mail Merge Data Sources. The Initial Writer(s) of the Original Documentation is/are Jean-François Nifenecker © 2004. All Rights Reserved. (Initial Writer contact(s): jean-francois.nifenecker@laposte.net)

Contributor(s): Jean-Francois Nifenecker.

Portions created by Jean-Francois Nifenecker are Copyright © 2004. All Rights Reserved. (Contributor contact(s):jean-francois.nifenecker@laposte.net).

All trademarks mentioned in this guide belong to their respective registered owners.

Feedback

Please direct any comments or suggestions about this document to:
jean-francois.nifenecker@laposte.net

Acknowledgments

I’d like to thank the contributors on the french project documentation list, especially Alex Thurgood for his help with the translation.

This documentation wouldn’t have been what it is without their feed-back and comments.

Modifications and updates

Version	Date	Description of Change
0.5	11/21/2004	Initial translation

Introduction

While migrating from MSOffice (MSO) to OpenOffice.org (OOo), many people encounter difficulties converting Word tables that are used as mail merge data sources.

Indeed, though MSO offers several options for mail merge data sources – summarised as Word tables or Excel spreadsheets –, one often chooses to use Word tables for that task. Unsurprisingly, this seemingly user friendly feature of Word leads to misunderstanding about the very nature of the data being used, and hence to potential problems.

Choosing to use Word tables deprives the user of the calculation abilities that a spreadsheet offers and creates more problems when one has to extract the data to some other format.

This How-To aims to demonstrate painless conversion of Word tables to Calc spreadsheets or dBase files. From there, it will be possible to use the resulting files as data sources for OOo mail merge activities. Conversion methods shown here need only be executed once for each Word document.

As an example, the How-to will deal with the conversion of an address table. Suppose, for the sake of argument, that the file is called Addresses.doc. The aims is to get a Calc spreadsheet or dBase file, as quickly as possible, from this original Word file, then use the resulting files as data sources for the mail merge operations using Writer.

Two methods will be shown here : a quick one and a “lengthy” one. The latter will give us a greater degree of control in the conversion process. At the end of this How-To, you will find a few links to additional documentation that will give more insight into mail merge functions using OpenOffice.org.

Save!

If you follow the steps described here, you won’t lose the initial data. Before starting any other action, it is however strongly recommended to first make a backup of your data!

A Fast Conversion Scheme

As its name implies, this conversion mode is very fast. The counterpart being the very little control it will give in the conversion process. In fact, what is called “conversion” in this fast method isn’t really one at all: it is merely a standard copy and paste task between tools!

Since the aim is to be as safe as possible while converting, and though the copy/paste action is a classic one, it will be described in length in the following paragraphs.

Copying The Word Table

Start Writer and open the Word document to be converted to a Calc sheet. The screen should look like that of Figure 1.

Figure 1 - The Word table to be converted Selecting the whole table
It is probably safer to use the keyboard instead of the mouse to copy the table to the clipboard. Call the Edit > Select All menu (keyboard shortcut : Ctrl + A) twice: the first call selects the first cell in the table, the second one selects the whole table;

Copying to the clipboard
This is a standard operation that is carried out via Edit > Copy (or Ctrl + C).

So far so good.

Pasting in Calc

Start Calc, by opening a new spreadsheet.

Check that the active cell is A1 and proceed with pasting of the clipboard contents using the Edit > Paste (Ctrl + V) menu.

Figure 2 - The result of the pasting into Calc Figure 2 shows the result after pasting.

Remarks

Column width is not adjusted;

Cell formatting is kept;

Note that the numbers in the Fee column are correctly interpreted, whatever their decimal separator, a dot (.) or a comma (,).

Saving The Final Document

Save the new spreadsheet, using the File > Save as... menu option. A choice of formats is proposed : the default OpenOffice.org 1.0 Spreadsheet (.sxc), or the dBase format (Type list positioned on dBase (.dbf)).

A “Lengthy” Conversion Scheme

This modus operandi is a bit longer than the former one but it gives much greater control in the conversion process: doing it this way means that useless columns may be disposed of, or that the data conversion is correctly set up. Moreover, the column width will be correctly adjusted.

The original Word file conversion starts using Writer with which an intermediate file will be generated that will be then imported into Calc.

Figure 3 - The lengthy conversion process There are a very few simple conversion steps. The diagram of Figure 3 is a recap of the process, as detailed in the following paragraphs.

Opening The Original Word File

Start Writer and open the Word document to be converted.

Figure 1 (page 2) shows the Writer screenshot after the document is loaded.

Converting The Table To Text

Calc can’t read a Word table per se. Thus, the document will be modified so that this goal can be achieved.

Using the Tools > Text<->Table menu shows the dialog of Figure 4.

Figure 4 - Table to text conversion dialog Select Tabs (default selection), then confirm using OK.

The original table has now disappeared. Columns are now separated with the Tab character (à), that was chosen (Figure 5) .

Figure 5 - The text after the table conversion Saving

The document as displayed in Figure 5 can now be saved to an intermediate format that Calc can read: the CSV format (Comma Separated Values ).

Use File > Save As...

Figure 6 - Saving to CSV format Check the Type zone and select Text (.txt). Make sure that the Automatic file name extension checkbox is unchecked, then enter the File name, remembering to specify the .csv extension, as shown in Figure 6.

Click on Save.

Note 1

The default .txt extension is not recommended, since Calc wouldn’t open such a file automatically, resorting to Writer to actually open the file.

Note 2

Remember to check the Automatic file name extension checkbox afterwards!

Two documents are now available: the original Addresses.doc, that is untouched, and the newly created Addresses.csv that Calc will read.

We may close Writer.

Importing The CSV File into Calc

Open Calc and, using File > Open, point to the Adresses.csv document that was created above. Click on Open.

Calc knows about the CSV format and can turn it into a new spreadsheet. Such an operation is called Importing and will be processed under the user's control, with the help of the Text import dialog as in Figure 7.

Figure 7 - Text import dialog in Calc Usually it is safe to keep the value selected in the Character set list. This option only needs to be changed when the imported document (the original Word table) comes from a foreign system.

The From row option allows the user to skip any number of unwanted lines, starting from the beginning of the table. By default, Calc imports all data.

More importantly, it is necessary to specify the Separator Options that fit the situation: select Separated by, then Tab and untick any other option in this category.

The preview panel at the bottom gives feedback with which one can check that the current selection fits the to-be imported file format.

Deleting columns

There are situations where the user may not want some columns from the original file to be imported. In this case, just select them in the preview panel and select Hide in the Column type list (Figure 8). The hidden column headers show the current selection status.

Figure 8 - Hiding a column Hidden columns won’t be part of the final document.

Columns with numerical values which are incorrectly interpreted

If typos in numerical values in the original table are noticed (using a comma instead of a dot), these errors can still be fixed.

Figure 9 - Fixing a number format typo

Select the “faulty” column in the preview panel, then force the US English option which will prevent any misreading of numbers(Figure 9).

Click OK when the preview is satisfactory.

Figure 10 - Data in Calc after import Calc now displays the data as a result of the import process (Figure 10). Browse the spreadsheet to check that the data are correctly organized.

Notes

Column widths are adjusted;

Cell formatting is lost.

Note

Where mistakes are noticed in the spreadsheet layout, one of the above described operations has failed:

Saving with a text format in Writer (page 4);

Import process in Calc (page 5).

Saving The Final Document

Is the aspect of the new spreadsheet correct? Perfect! Now save it under any of the required formats for use as a mail merge data source: Calc format (.sxc file) or dBase format (.dbf).

Figure 11 - Saving under Calc format Call the File > Save as... menu (Figure 11).

In the Type list, choose either OpenOffice.org 1.0 Spreadsheet (.sxc) (at the top of the list) or dBase (.dbf), according to the desired target file format.

Make sure that the Automatic file name extension checkbox is checked and confirm using Save.

There are now three documents:

Addresses.doc	The original Word document, which is now useless;

Addresses.csv	The intermediate document that can be deleted;

Addresses.sxc (or Addresses.dbf).
The new datasource document.
Do not forget to save this latter document before starting work with it!

Miscellaneous information

Calc or dBase ?

As suggested, one can use either Calc spreadsheet or dBase files as data sources for mail merge needs. A “database” format is usually advised in order to gain access to the advanced editing and query functions.

The how-tos listed in the next paragraph give more details about the mail merge functions with OpenOffice.org and will be of help in deciding which format best fits any given situation.

More Information

The following how-tos provide more information about mail merge using OpenOffice.org:
http://documentation.openoffice.org/HOW_TO/word_processing/writer2_EN.pdf
http://documentation.openoffice.org/HOW_TO/data_source/data1_EN.html

These documents are freely available, along with many others in the documentation area of the OpenOffice.org project: http://www.openoffice.org/

Public Documentation License, Version 1.0

1.0 DEFINITIONS.

1.1. "Commercial Use" means distribution or otherwise making the Documentation available to a third party.

1.2. "Contributor" means a person or entity who creates or contributes to the creation of Modifications.

1.3. "Documentation" means the Original Documentation or Modifications or the combination of the Original Documentation and Modifications, in each case including portions thereof.

1.4. "Electronic Distribution Mechanism" means a mechanism generally accepted for the electronic transfer of data.

1.5. "Initial Writer" means the individual or entity identified as the Initial Writer in the notice required by the Appendix.

1.6. "Larger Work" means a work which combines Documentation or portions thereof with documentation or other writings not governed by the terms of this License.

1.7. "License" means this document.

1.8. "Modifications" means any addition to or deletion from the substance or structure of either the Original Documentation or any previous Modifications, such as a translation, abridgment, condensation, or any other form in which the Original Documentation or previous Modifications may be recast, transformed or adapted. A work consisting of editorial revisions, annotations, elaborations, and other modifications which, as a whole represent an original work of authorship, is a Modification. For example, when Documentation is released as a series of documents, a Modification is:

A. Any addition to or deletion from the contents of the Original Documentation or previous Modifications.

B. Any new documentation that contains any part of the Original Documentation or previous Modifications.

1.9. "Original Documentation" means documentation described as Original Documentation in the notice required by the Appendix, and which, at the time of its release under this License is not already Documentation governed by this License.

1.10. "Editable Form" means the preferred form of the Documentation for making Modifications to it. The Documentation can be in an electronic, compressed or archival form, provided the appropriate decompression or de-archiving software is widely available for no charge.

1.11. "You" (or "Your") means an individual or a legal entity exercising rights under, and complying with all of the terms of this License or a future version of this License issued under Section 5.0 ("Versions of the License"). For legal entities, "You" includes any entity which controls, is controlled by, or is under common control with You. For purposes of this definition, "control" means (a) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (b) ownership of more than fifty percent (50%) of the outstanding shares or beneficial ownership of such entity.

2.0 LICENSE GRANTS.

2.1 Initial Writer Grant.

The Initial Writer hereby grants You a world-wide, royalty-free, non-exclusive license to use, reproduce, prepare Modifications of, compile, publicly perform, publicly display, demonstrate, market, disclose and distribute the Documentation in any form, on any media or via any Electronic Distribution Mechanism or other method now known or later discovered, and to sublicense the foregoing rights to third parties through multiple tiers of sublicensees in accordance with the terms of this License.

The license rights granted in this Section 2.1 ("Initial Writer Grant") are effective on the date Initial Writer first distributes Original Documentation under the terms of this License.

2.2. Contributor Grant.

Each Contributor hereby grants You a world-wide, royalty-free, non-exclusive license to use, reproduce, prepare Modifications of, compile, publicly perform, publicly display, demonstrate, market, disclose and distribute the Documentation in any form, on any media or via any Electronic Distribution Mechanism or other method now known or later discovered, and to sublicense the foregoing rights to third parties through multiple tiers of sublicensees in accordance with the terms of this License.

The license rights granted in this Section 2.2 ("Contributor Grant") are effective on the date Contributor first makes Commercial Use of the Documentation.

3.0 DISTRIBUTION OBLIGATIONS.

3.1. Application of License.

The Modifications which You create or to which You contribute are governed by the terms of this License, including without limitation Section 2.2 ("Contributor Grant"). The Documentation may be distributed only under the terms of this License or a future version of this License released in accordance with Section 5.0 ("Versions of the License"), and You must include a copy of this License with every copy of the Documentation You distribute. You may not offer or impose any terms that alter or restrict the applicable version of this License or the recipients' rights hereunder. However, You may include an additional document offering the additional rights described in Section 3.5 ("Required Notices").

3.2. Availability of Documentation.

Any Modification which You create or to which You contribute must be made available publicly in Editable Form under the terms of this License via a fixed medium or an accepted Electronic Distribution Mechanism.

3.3. Description of Modifications.

All Documentation to which You contribute must identify the changes You made to create that Documentation and the date of any change. You must include a prominent statement that the Modification is derived, directly or indirectly, from Original Documentation provided by the Initial Writer and include the name of the Initial Writer in the Documentation or via an electronic link that describes the origin or ownership of the Documentation. The foregoing change documentation may be created by using an electronic program that automatically tracks changes to the Documentation, and such changes must be available publicly for at least five years following release of the changed Documentation.

3.4. Intellectual Property Matters.

Contributor represents that Contributor believes that Contributor's Modifications are Contributor's original creation(s) and/or Contributor has sufficient rights to grant the rights conveyed by this License.

3.5. Required Notices.

You must duplicate the notice in the Appendix in each file of the Documentation. If it is not possible to put such notice in a particular Documentation file due to its structure, then You must include such notice in a location (such as a relevant directory) where a reader would be likely to look for such a notice, for example, via a hyperlink in each file of the Documentation that takes the reader to a page that describes the origin and ownership of the Documentation. If You created one or more Modification(s) You may add your name as a Contributor to the notice described in the Appendix.

You must also duplicate this License in any Documentation file (or with a hyperlink in each file of the Documentation) where You describe recipients' rights or ownership rights.

You may choose to offer, and to charge a fee for, warranty, support, indemnity or liability obligations to one or more recipients of Documentation. However, You may do so only on Your own behalf, and not on behalf of the Initial Writer or any Contributor. You must make it absolutely clear than any such warranty, support, indemnity or liability obligation is offered by You alone, and You hereby agree to indemnify the Initial Writer and every Contributor for any liability incurred by the Initial Writer or such Contributor as a result of warranty, support, indemnity or liability terms You offer.

3.6. Larger Works.

You may create a Larger Work by combining Documentation with other documents not governed by the terms of this License and distribute the Larger Work as a single product. In such a case, You must make sure the requirements of this License are fulfilled for the Documentation.

4.0 APPLICATION OF THIS LICENSE.

This License applies to Documentation to which the Initial Writer has attached this License and the notice in the Appendix.

5.0 VERSIONS OF THE LICENSE.

5.1. New Versions.

Initial Writer may publish revised and/or new versions of the License from time to time. Each version will be given a distinguishing version number.

5.2. Effect of New Versions.

Once Documentation has been published under a particular version of the License, You may always continue to use it under the terms of that version. You may also choose to use such Documentation under the terms of any subsequent version of the License published by______________________ [Insert name of the foundation, company, Initial Writer, or whoever may modify this License]. No one other than ________________________[Insert name of the foundation, company, Initial Writer, or whoever may modify this License] has the right to modify the terms of this License. Filling in the name of the Initial Writer, Original Documentation or Contributor in the notice described in the Appendix shall not be deemed to be Modifications of this License.

6.0 DISCLAIMER OF WARRANTY.

DOCUMENTATION IS PROVIDED UNDER THIS LICENSE ON AN "AS IS'' BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, WITHOUT LIMITATION, WARRANTIES THAT THE DOCUMENTATION IS FREE OF DEFECTS, MERCHANTABLE, FIT FOR A PARTICULAR PURPOSE OR NON-INFRINGING. THE ENTIRE RISK AS TO THE QUALITY, ACCURACY, AND PERFORMANCE OF THE DOCUMENTATION IS WITH YOU. SHOULD ANY DOCUMENTATION PROVE DEFECTIVE IN ANY RESPECT, YOU (NOT THE INITIAL WRITER OR ANY OTHER CONTRIBUTOR) ASSUME THE COST OF ANY NECESSARY SERVICING, REPAIR OR CORRECTION. THIS DISCLAIMER OF WARRANTY CONSTITUTES AN ESSENTIAL PART OF THIS LICENSE. NO USE OF ANY DOCUMENTATION IS AUTHORIZED HEREUNDER EXCEPT UNDER THIS DISCLAIMER.

7.0 TERMINATION.

This License and the rights granted hereunder will terminate automatically if You fail to comply with terms herein and fail to cure such breach within 30 days of becoming aware of the breach. All sublicenses to the Documentation which are properly granted shall survive any termination of this License. Provisions which, by their nature, must remain in effect beyond the termination of this License shall survive.

8.0 LIMITATION OF LIABILITY.

UNDER NO CIRCUMSTANCES AND UNDER NO LEGAL THEORY, WHETHER IN TORT (INCLUDING NEGLIGENCE), CONTRACT, OR OTHERWISE, SHALL THE INITIAL WRITER, ANY OTHER CONTRIBUTOR, OR ANY DISTRIBUTOR OF DOCUMENTATION, OR ANY SUPPLIER OF ANY OF SUCH PARTIES, BE LIABLE TO ANY PERSON FOR ANY DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES OF ANY CHARACTER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF GOODWILL, WORK STOPPAGE, COMPUTER FAILURE OR MALFUNCTION, OR ANY AND ALL OTHER DAMAGES OR LOSSES ARISING OUT OF OR RELATING TO THE USE OF THE DOCUMENTATION, EVEN IF SUCH PARTY SHALL HAVE BEEN INFORMED OF THE POSSIBILITY OF SUCH DAMAGES.

9.0 U.S. GOVERNMENT END USERS.

If Documentation is being acquired by or on behalf of the U.S. Government or by a U.S. Government prime contractor or subcontractor (at any tier), then the Government's rights in Documentation will be only as set forth in this Agreement; this is in accordance with 48 CFR 227.7201 through 227.7202-4 (for Department of Defense (DOD) acquisitions) and with 48 CFR 2.101 and 12.212 (for non-DOD acquisitions).

10.0 MISCELLANEOUS.

This License represents the complete agreement concerning the subject matter hereof. If any provision of this License is held to be unenforceable, such provision shall be reformed only to the extent necessary to make it enforceable. This License shall be governed by California law, excluding its conflict-of-law provisions. With respect to disputes or any litigation relating to this License, the losing party is responsible for costs, including without limitation, court costs and reasonable attorneys' fees and expenses. The application of the United Nations Convention on Contracts for the International Sale of Goods is expressly excluded. Any law or regulation which provides that the language of a contract shall be construed against the drafter shall not apply to this License.

Appendix

Public Documentation License Notice

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. A copy of the License is available at http://www.openoffice.org/licenses/PDL.rtf.

The Original Documentation is _________________. The Initial Writer of the Original Documentation is ______________________ (C) ____. All Rights Reserved. (Initial Writer contact(s):_______________[Insert hyperlink/alias].)

Contributor(s): ______________________________________.

Portions created by ______ are Copyright (C)_________[Insert year(s)]. All Rights Reserved. (Contributor contact(s):________________[Insert hyperlink/alias]).

Note: The text of this Appendix may differ slightly from the text of the notices in the files of the Original Documentation. You should use the text of this Appendix rather than the text found in the Original Documentation for Your Modifications.

Note : Tab characters are shown in the Figure 5 screenshot because the display option View > Nonprinting Characters (or Ctrl + F10) was selected.

Yes, although Tabs were requested (and are shown), but the output format is still CSV...

A “Lengthy” Conversion Scheme

Mail Merge Data Sources	11

Title:	Mail Merge Data Sources
Version:	0.1
First edition:	11/08/04
First English edition:	11/20/04

Overview

Contents

Mail Merge Data Sources	xi

Public Documentation License, Version 1.0

Mail Merge Data Sources

G. Roderick Singleton

2004-11-19T08:40:18

G. Roderick Singleton