Department of Technology

TECH 394 - Tech Computer Applications

E-mail Me

Syllabus

Schedule

Handouts

Assignments

All My Webs

Dept Home

Data Integration

Data integration is the process used to move data from one application to another.  For some tasks, this involves the use of the Windows feature called OLE - Object Linking and Embedding.  Word, Access and Excel have been designed to make use OLE.

Embedding

If your purpose is to plug a snapshot of data from one application into another, you probably want to embed the data. That is, you don't want the application to build an on-going link to the data source and update when it changes.  This can typically be done by copying and pasting.  For example, you can copy a cell in a spreadsheet, and paste it into a line in word.  Use the Edit, Paste Special menu selection to place the data.

The window above indicates that cell R2C2 (Row 2, Column 2 - cell B2) from a sheet named Sheet1 has been copied to the clipboard.  The "Paste" button is selected, indicating that the data will be embedded, but no active link will be created. 

Selecting the highlighted choice - Formatted Text - will bring the formatting from the spreadsheet with the text - it will use the font from the spreadsheet, rather than the existing Word format. Choose Unformatted Text if you want the pasted data to look like the rest of the document.

Linking

The process is similar, but you select the "Paste Link" option button.  A link  to the selected cell is established.  Each time the document loads, it tries to update that link.  If the data source file changes, the link may be lost.  To prevent such problems:

• Save the source file under the name and in the directory where you intend it to reside. Pasting a link and then moving the file or giving it a new name will cause the link to be broken.
• Name the cell you intend to paste - the link will refer to it by name, making it easier to follow.
• Close the source file before you try to update the link.

Once a link has been established, you can edit or update it by selecting the menu choices Edit, Links.

Selecting Automatic Update (the default) means that each time the document is opened, the source should be opened, and the linked data should be updated.  If the Manual button is selected, you must bring up this screen and press Update Now to update the link.  Selecting Locked prevents the link from updating until the selection is changed.

Notice that OLE is a good way to move or link small pieces of data.  If you want to create a document that displays one record from your database, such as an invoice for one sale, this is probably the easiest way to integrate the data.  If, however, you want to make multiple documents in a word processor like Word, and you want each document to draw information from a database, you will probably want to use a feature of Word called "Mail Merge". 

You may also find yourself trying to import large chunks of information from external databases.  Many firms maintain large operational databases that format the data in ways that Excel or Access can't handle.  Using products like SAP or Great Plains Dynamics, the IS department may generate expense, sales, or inventory reports for your department. You'll be expected to use that data to manage your department's operations.

There are ways to link Access and Excel directly to these databases, but most IS departments hesitate to do this - it offers too much chance for the original data to be corrupted.  The best you'll get in some firms is a printed report.  Then someone's job is to type the data from the report into Excel.  This is time consuming and offers the opportunity for new errors.  The better choice would be to import that data into Excel.

You can usually ask for any printed material to be delivered as a "text-only" file instead.  That means that the database program generates a file containing only standard text characters, which your spreadsheet can easily read.  Since the data comes from a database, its typically organized into fields and records, and the output needs to be structured so that Excel can put one field from one record in a given cell.  This is done in one of two ways:

• Fixed-width files display all the data for one record in a given line. Each field for that record takes up a designated number of characters.  For example, if the first two fields held a customer's first and last names, and both fields were formatted to be 15 characters wide, any text between character 16 and character 30 would automatically be dumped into the second cell in that row, regardless of what it contained.
• Delimited files use common characters to tell the importing program where each field ends.  A common format is the Comma Delimited format - the data for each field is separated by commas, with all the fields for one record in one line.  Because text may include commas, any text in a field is usually enclosed in quotes.  Typical data might look like:
        "Joe","Doe","126 N 3rd Street, Apt 3",125.74,"Yes","apples"
  Note that the comma embedded in the address will be ignored because it falls between the quotes.  Also note that the number - 125.74 - is not enclosed in quotes.  Numbers don't contain commas, and the importing program often needs to know which strings to treat as text, and which are number to be used for calculations. When you use delimited files, you need to know which character serves as the delimiter (the comma) and which serves as define text (the quote marks). 

Excel will import those files using a wizard to help you define the fields, and eliminate any unneeded information.  If the text file always uses the same format, you can record a macro as you import the file, and use that macro for all other imports form the same source.

A sample file called SampleImportData.txt should be saved to your "T" drive folder. Start Excel and use the File->Open menu to begin importing the file.