Use advanced features of software

Methods of data exchange

Exchanging data between applications

Clipboard

Object linking and embedding (OLE)

What’s the difference between a linked object and embedded object?

Options for importing, exporting and linking data

File formats

Delimited and fixed-width text files

Spreadsheet files

HTML files

Importing data

Preparing to import data

An import checklist

Example: The process of importing data using MS Access

Exporting data

Preparing to export data

An export checklist

Example: The process of exporting data using MS Access

Using data in a word processing mail merge

Linking data

Lookup tables

Example: Lookup functions in Microsoft Excel

Pivot tables

Summary

Check your progress

Methods of data exchange

Preparing and entering data can be a time-consuming task. In some cases you already have the required data on your computer or network but not in the database where you need it. For example, you may have a text file that contains a list of your customers that you now want to transfer to a database. At other times, you may have information in a database that you’d like to use in another program. As an example, you may have information about your customers’ accounts in a database that you need to transfer to a spreadsheet where you can perform further analysis on the figures. There are three general methods for exchanging data that will meet your needs in situations like these:

1importing

2exporting

3linking.

Importing

Importing allows you to make a copy, or snapshot,of data from one program and store it in another application. Any changes you make will not affect the original data source, and vice versa.

Exporting

Exporting allows you to copy data to another program or file format. Again, exporting is like taking a snapshot of your original data. Modifications to the original data will not be reflected in the exported data, and vice versa.

Linking

Linking provides a way to connect to data in another application without actually importing it. You can view and access the data, but the file remains in its original application format. You can use or edit the data with the program that created it, and in many cases you can add, delete or edit the data from the linked application as well.

We’ll look at each of these methods and practise using the data transfer tools available in a range of computer applications found in a typical office suite of programs. These include: Microsoft Office, Open Office, WordPerfect Office, Lotus SmartSuite, Claris Works/Apple Works and others. The methods used to exchange data may differ slightly between these packages.

Exchanging data between applications

Each of the industry-standard office suites uses the following techniques to manage the importing, exporting and linking of data. For the software you are using, you will need to use the help facility to find information relevant to your applications.

Clipboard

Data that has been cut or copied is temporarily stored in an area of memory known as a clipboard (or pasteboard). Using the cut-and-paste techniques of the clipboard is one of the more familiar ways to exchange data. Aside from providing a way to cut/copy and paste information within an application, the clipboard also provides a versatile means of exchanging data between applications. Where possible, the original formatting of the data is preserved when you paste from one application to another.

For example, in Office 2000 up to twelve selections of data can be held in the clipboard at any one time, and the clipboard is emptied when you exit Windows. In Office 2002 (XP), up to twenty five items can be copied.

Object linking and embedding (OLE)

Object linking and embedding is another technique that allows multiple windows-based applications to share and exchange information in the form of ‘objects’. OLE involves three main concepts: the OLE server, the OLE client and the OLE object.

  • An OLE server is a program that generates, or ‘serves up’ an object for use in another program.
  • An OLE client is the program that can accept an object initially created by the server.
  • An OLE object might be data, a chart, graphic, sound, video or other document.

For example, an object from an OLE server — such as a graphic or a spreadsheet chart — can be linked or embedded in a field, form or report in a database which in this case would be the OLE client.

What’s the difference between a linked object and embedded object?

Linked objects and embedded objects differ in two main ways:

1where the object is stored

2how the object is modified or updated after you place it in the destination file.

A linked object is displayed in the destination file, but the object remains stored in its original source file. The destination file stores the location of the source file and displays only a representation of the object. If you modify the object in the source file the changes will be reflected in the destination file.

With an embedded object, a copy of the object is placed in the destination file. This copy is totally independent of the original object. Double-clicking the embedded object will open it in the source program ready for editing, but any changes you make will not be saved back to the source file. Likewise, changes made to the source file will not be reflected in the embedded object.

When would you want to use link rather than embed? Can you think of any examples?

You would link when you wanted to keep applications up to date with the latest version. An example would be inserting the results of a monthly sales spreadsheet into a word processing document. Each month it would automatically update with the new figures. Another example would be if your supervisor wanted to read data from a spreadsheet and that data was entered into a database as it was easier for data entry. You could link them both together and keep the information in the spreadsheet up-to-date.

Note: Be careful not to confuse OLE objects (data, charts, graphics, etc. createdoutside the database) with database objects (tables, forms, reports, etc.).

Dynamic Data Exchange—DDE

Microsoft applications also use Dynamic Data Exchange (DDE): a message protocol that permits data exchange between Windows-based programs. It allows software developers to design applications that share data and thereby provide the user with a more integrated Windows environment. For example, when you insert database information into an MS Word document, Word uses DDE to open and read the data file.

Options for importing, exporting and linking data

Think about the following questions:

  • Can you import an HTML file into a database?
  • Can you export data from one database program (eg MS Access to another database program (eg Paradox)?
  • Can you import a database query into a spreadsheet program?
  • Can you do a word processing mail merge using an email address book?

The easiest way to find out the answers to these questions is to use File/Save As in the program you are using and check the options in the Save As dialog box. Some programs require you to use an import and export method. Search help in your program to find programs that can be imported and exported.

Practice

Look at each of the above programs in turn, and examine the various import/export and link options that are installed. Browse the relevant menu options which carry out each function. Compare this to the document Options for importing exporting and linking data in Office 2000.doc. that you’ll find in the Research section of this Learning Pack.

File formats

A file format defines an exact structure for the data contained in a file. An application recognising the format can then read information about the file and its contents and layout. Often a file can only be read by the application that created it. However, if the specification for a file is a common format, it can be used by other applications.

The file format is defined by the file extension. For example, an MS Word file has a .doc suffix and an MS Access database file has an .mdb suffix.

While MS Access can import data from a wide range of database programs including dBASE, Paradox and ODBC databases such as Microsoft SQL Server, you will often have to work with data that is stored in other file formats. We’ll consider the main non-database file formats here.

Delimited and fixed-width text files

A delimited file is a file containing values (fields) that are separated by a delimiter character such as a comma, tab, or semicolon. While a text file can be delimited by almost any character, care must be taken that the selected character does not appear as part of the ‘normal’ data stored in the file.

For example, if a value in the file contains a semicolon, you would need to choose a different character to serve as the delimiter. The most common file extension for delimited files is .txt, but other valid file formats include .csv (comma separated values) and .tab (tab separated values).

The characters usually selected for delimiting data are:

Delimiter / Symbol / Comments
Comma / ,
Semicolon / ;
Tab / None / Is a control character and may not be visible (If it is visible, it should appear in the symbol column as )
Bar / | / Can be used because it rarely appears in text
Tilde / ~ / Can be used because it rarely appears in text

The values in a text file are organised in rows and each row ends with a carriage return/linefeed. One row represents one record of data. However, it’s common for the first row in a delimited or fixed-width text file to contain the field names as shown in several of the file examples below.

Note: Before importing a text file be sure that each value in a given field contains the same type of data, and that the field order is the same in each row. If these criteria are not met the import operation will not be successful.

Comma-delimited text files

Text files can be opened and read by any text editor such as Notepad, Wordpad or Microsoft Word. The following example shows a comma-delimited text file with optional quotes surrounding each value.

‘Mr’,‘Joe’,‘Cleary’,‘123 Marquee Place’,‘Woy Woy’,‘1234’,‘(02)234-1234’

‘Ms’,‘Mary’,‘Andrews’,‘1A Granny Smith Lane’,‘Bilpin’,‘2222’,‘(02)111-1111’

‘Ms’,‘Janet’,‘Simpson’,‘1 Washbourne Lane’,‘Horsnby’,‘2323’,‘(02)321-1234’

The advantage of using a delimited text file for import or export purposes is that:

  • It can be read by any computer that can read ASCII text.
  • It is compact as there is no wasted space in the file.

Tab-delimited text files

Tab-delimited files can also be opened and read by any text editor like Notepad, Wordpad, Lotus Notes, Corel WordPerfect, OpenOffice Writer or Microsoft Word, although you can’t see the tab characters (unless the hidden characters are selected to be shown or ‘on’).

Fixed-width text files

In a fixed-width text file, the values are arranged so that each field has a set width measured in characters. The fixed width is used to delineate the beginning and end of each field. One disadvantage of using fixed-width files is that values longer than the field width will be truncated, resulting in a loss of data.

Spreadsheet files

Spreadsheet files can be easily imported into a database provided the data is arranged so that:

  • each value in a given field contains the same type of data
  • each row contains the same fields in the same order
  • the first row in the spreadsheet contains either the field names or the first row of data you want to import.

Note: in older versions of MS Access and various brands of spreadsheets, multiple spreadsheets in a workbook are first saved as separate spreadsheet files. Newer versions of Access have the capacity to read and/import different sheets of a workbook, allowing the user to choose which sheet is to be imported — just not all at once — into a single table.

A / B / C / D / E / F / G
1 / Sal / FirstName / LastName / Address / Suburb / Pcode / Home Phone
2 / Mr / Joe / Cleary / 123 Marquee Place / Woy Woy / 2234 / (02) 1234-1234
3 / Ms / Mary / Andrews / 1A Granny Smith Lane / Bilpin / 2222 / (02) 1111-1111

An example of a simple spreadsheet

Find out what spreadsheet files will be imported into your database package.

HTML files

These are web-based files that are viewable in a web browser like Internet Explorer. The files can be .htm or .html format. Although you can edit these files using any type of text editor such as Notepad, the special formatting instructions that are embedded within them can make the material hard to read if you are not familiar with HTML code. HTML files are best edited in a dedicated application.

Note: The special requirement for importing HTML files into an MS Access database is that the data must be placed in an HTML table and the table given a name.

Importing data

Preparing to import data

Databases can import data from many different sources. In this section we will specifically cover the issues you need to consider before importing data from external files.

Garbage in, garbage out

‘Garbage in, garbage out’ — put simply, this means if you put incomplete or corrupt information into a system, you can only ever get the same back out of it.

Maintaining the integrity and consistency of data is an essential part of good database management. You should ensure you employ all available means to prevent the introduction of incorrect or incomplete data to your system. This includes using validation rules for data entry, limiting user choices to a range of pre-defined options, and placing user access restrictions on crucial parts of the system. You also need to ensure that any data you import directly into the database from external sources meets your data integrity requirements.

Unfortunately, the detection and correction of errors during import operations cannot be completely automated, even with programming. When a problem does occur during import your program may warn you, for example, MS Access usually produces a PasteErrors companion table (and warning) that contains a list of errors detected. But be aware that some types of errors produce no warnings or error tables. In such cases, if corrupted data is imported to a table it may be very subtle and difficult to detect. The impact on your database or organisation may not be so subtle however!

It’s worthwhile spending a little time reading through the following checklist of points to consider before and after you import any data into a database — it may save you a lot of time and heartache later.

An import checklist

Communicate with the file provider

If you are to receive the file from a third party:

  • Ask them to provide the file in your preferred format.
  • Will they notify you in advance of changes to the file format?
  • What can you do if the data format changes?
  • Will you receive updated files regularly?
  • Should you automate the import of regular updates?
  • Can you identify records by update batch or date?
  • Does the file need to be un-encrypted or decompressed before being importing?

Check the file format and extension to determine the file type

Some potential problems with file extensions are:

  • A file may not have a filename extension, which is often the case with Apple Macintosh files. You can rename the file and add the missing extension, assuming of course that you know what the file type is.
  • A file may sometimes have the wrong file extension for the type of data that it contains
  • Two different applications may use the same file extension but store data in a completely different format (rare but it does happen).

Is an import filter for the required file format available in the database?

  • Is the filter you require installed or available on the software application package (or database) CD-ROM?
  • Is the file format revision level compatible, eg you have been given an Access 2002 file and you only have Access 2000 installed?

Some file formats are more difficult to import than others

  • In general, other database file formats such as dBASE or FoxPro are the easiest to import (except when importing linked tables).
  • Spreadsheet formats are also easy to import, though you can sometimes end up with blank rows and fields.
  • HTML files require the most vigilance and handling, unless the files conform to agreed import specifications.

Some file formats cannot be imported into a database using either the Import Wizard or the Transfer (macro action) commands

While you can import most common file types without resorting to programming, some files can only be imported by creating custom code. In such cases you may be able to convert the file by opening it in another application and exporting it from that application to a format compatible with your database program.