Section 25.3. Transferring Data | Excel 2007[c] The Missing Manual

25.3. Transferring Data

With embedding and linking, two or more programs work together to create a compound document. However, even though the different objects appear side by side, different programs actually own them. When you embed an Excel table in a Word document, you can't spruce up any text in your table by using Word's built-in grammar checker.

Furthermore, sometimes the program you're embedding an object into can just as easily create the object you've inserted. Consider, for example, a typical Word document, which supports all the same formatting options as Excel, and can organize information into a grid perfectly well by using its table-creation features.

In such cases, you could transfer raw data , rather than objects. So instead of embedding a worksheet object inside Word, you could simply copy Excel's formatted worksheet data, and then move it into Word. You do lose the ability to update the information with Excel, but you gain a few benefits as well:

You can edit the data directly in Word without needing access to Excel.
You can edit the data quickly and more conveniently. This ability is particularly important if you want to format the data to match the rest of your document.
The file is smaller than it would be if you used an embedded object.
You avoid accidentally modifying information if you change the source worksheet (as you would if you used a linked object).

For these reasons, it's worth carefully considering whether you should copy a full-fledged worksheet object, or just transfer the information you want to use.

25.3.1. Exporting Tables of Data

The secret to pasting worksheet data into another application like Word is the same Paste Special command you use to create embedded and linked objects. The following steps walk you through the process.

Select a range of cells from your worksheet, and then choose Home Clipboard Copy .

For best results, try to avoid selecting empty rows and columns .

Switch to the target document, and then use the Paste Special command .

The Paste Special dialog box appears. This dialog box shows what you'd see if you were copying a selection of cells from Excel into Word. When you copy a selection of cells, the Paste Special dialog box gives you a slew of choices for how you want to import your data from Excel. As shown in Figure 25-7, the target application recognizes that your clipboard data includes ordinary text content. In most applications, you have the option to insert formatted or unformatted versions of the text, a linked or embedded object, or even a picture of the text that you've selected!

Figure 25-7. In most cases, the best approach is to avoid embedding and linking altogether, and just choose formatted or unformatted text. Keep in mind that if you embed a worksheet object, you'll wind up embedding all the worksheet's data, even though you see, in the embedded object box, only the portion of data that you selected.

In the Paste Special dialog box, select the type of object you want to insert .

You can choose any supported format from the list. The format choices depend on the type of object you're inserting. For example, Excel lets you paste the selected cells as an Excel object, or convert it to unformatted text, HTML text, or even a low-resolution picture.

You can also choose how Excel synchronizes the pasted data with the source. Choose "Paste link" if you want to create a linked object that Excel refreshes based on changes you make to the source information. Choose "Paste" if you want to create an embedded object that you can modify independently.
Click OK .

The Excel information appears in the format you chose.

25.3.2. Importing Tables of Data

There's also no reason that you can't take text out of another program and then paste it into Excel. This tactic works best if the information is already arranged in a grid-like structure (for example, a Word table), but Excel can insert the data no matter how it's organized. Figure 25-8 shows an example.

Instead of using Home Clipboard Paste Special to paste content into Excel, you can use Home Clipboard Paste as a shortcut. When you do, Excel inserts formatted text, and a smart tag icon appears next to the newly inserted content. You can click this icon, and then choose either Keep Source Formatting (which applies whatever formatting was used in the original program) or Match Destination Formatting (which ignores the source formatting and keeps whatever formatting was already applied to the cells).

25.3.3. Importing Text Files

Sometimes you want your Excel spreadsheet to use data that's stored in an ordinary text file. In this case, you can import the information into Excel, but you need to go through a special conversion process. This conversion process scans the text and splits it into separate rows and columns, so that Excel can insert it into a work-sheet's cells. Excel provides an intelligent tool, the Text Import wizard, that helps you perform text file conversions.

To import a text file, follow these steps:

From within Excel, select Office button Open .

The Open dialog box appears.

From the "Files of type" list at the bottom of the window, select Text Files .

Excel knows you're looking for files that have the extension .prn, .txt, or .csv. If you have a text file that has a different file extension, choose the All Files type instead.

Figure 25-8. These examples show some Word content (top) and how it appears when it's copied into Excel (bottom). As you can see, you can transfer large paragraphs, but the result isn't too pleasant. The entire paragraph is crammed into one cell , and it's hardly readable. On the other hand, tables are much easier to copy and paste. Excel correctly distributes the rows and columns into cells on the worksheet, and it even merges cells in order to match the original document's formatting. This technique works just as well if you copy a table of information off a Web page, and then paste it into Excel.

Browse to the file you want to import, and then select it .

For a quick and easy test, use the log.txt file included with the downloadable content for this chapter on the "Missing CD" page at www.missingmanuals.com. This text file contains a list of log entries that reflect system activity on a computer.

Click OK to continue .

Excel starts the Text Import wizard.

UP TO SPEED
Why Text Files Matter

With all the copying, pasting, and formatting features available in Windows, you may wonder why anyone would ever stoop to the lowly level of plain text. In fact, using plain text is more common than you may think, because text files are the lowest common denominator when it comes to data. Even if you have a program that runs on a dinosaur-era operating system like DOS, you can still transfer information, as long as your program includes a text export feature.

Even if you're using a Windows application, you can still find yourself in this situation if the application doesn't provide a way to select and copy objects. You'll often find yourself in this predicament with applications that aren't document-oriented (in other words, applications that aren't designed to help you create, edit, and save some sort of document).

Consider a management tool that lets you assign projects to employees in a company. This tool probably doesn't let you copy employee and project information directly, but it might include an export feature that dumps this information into a text file. Once the text file is ready, you can use Excel's Text Import wizard to bring it into your worksheet.

In step 1 of the wizard (Figure 25-9), choose "Original data type". You have two options, "Delimited" or "Fixed width", depending on your text file's format .

Usually, the program that creates the text file indicates what type of format it's using, or give you the chance to choose one of several supported formats. If you're unsure of the exported format, you may need to take a closer look at the file by first opening it in a program like Notepad, which should help you figure out what kind of format you're dealing with.

Figure 25-9. In this example, a delimited text file containing a list of purchases is being imported. In the first step, the file appears as a stream of unstructured information.

Delimited means that there's a separatorusually a comma, a tab, or a semi-colonbetween each column. In a comma-delimited file, the data shown below would comprise three columns of information, one with the region name (Region 1 is the first name ), followed by separate columns containing the month and the number at the end of the line:

 Region 1,January,43432 Region 2343,March,839

Fixed width means that the columns are separated using a series of spaces. All the columns are spaced evenly. The same data is shown in a fixed-width format below. The problem with fixed-width formats is that a value can't exceed the maximum space allocated to the column.

 Region 1 January 43432 Region 2343 March 839

Note: Most programs that export data to text use delimited text files. When you're importing data into Excel, you'll often use the delimited option. To tell whether your text file's delimited, look for its telltale signa repeated character that's used to separate each piece of information. Any character can separate columns in a delimited file, but commas and tabs are the top choices.

Click Next, and then complete step 2 of the Text Import wizard (Figure 25-10) .

This step varies depending on whether you're importing a fixed width or a delimited file. If you're importing a delimited file, you need to specify, in the Delimiter box, the character that's used to separate columns. If you're importing a comma-delimited file (where a comma separates each column), then turn on the Comma checkbox. Once you make your change, Excel updates the preview, separating the data into columns using the delimiter you chose.

If you're importing a fixed-width file, you need to indicate where each column starts. Click the preview window in the appropriate location, once for each column. As you click, Excel adds column dividers between each column.

Click Next to move to the last step (Figure 25-11) .

The last step lets you specify the data type for each column. Usually, the standard option (General) works perfectly well. If you choose General, Excel examines the content, and then changes it into a text, number, or date entry, depending on the type of values the file contains.

If you want to override this automatic decision-making process, simply select a column in the "Data preview" section. Then, from the column in the "Column data format" box, choose the data type. You may take this extra step if you have a cell that contains numeric content, but you want to treat it as text.

Figure 25-10. Life gets better in step 2, which gives you the chance to specify the delimiter that separates columns (in this case, the tab character). Once Excel knows that the file is organized using tab characters , it can parse the file and display it properly in the "Data preview" window.

Figure 25-11. The third step provides a last chance to change how Excel interprets the data in each column. Here, it's important to tell Excel the third column contains date information, not ordinary text.

Click Finish to complete the wizard .

POWER USERS' CLINIC
Importing Text into an Existing File

Rather than using your text file to create a new workbook, you place it into an existing workbook. This process uses the same Text Import wizardthe difference is that once the last step is finished, Excel asks you where you want to place the data. (You can use a specific location in the current worksheet, or create a new worksheet.) To launch the process, choose Data Get External Data From Text.

At first glance, you may wonder why Excel has two features that seem so similar. However, there's a subtle but important difference in the way you use these features. The Office button Open command is designed for Excel fans who want to import external data and change it. On the other hand, the Data Get External Data From Text command is for those who just want to analyze the information that's in a text file. Here are two examples that make the difference a bit clearer:

Your company has oodles of old data created by a Paleolithic piece of mainframe software. This data is stored in bare-bones text files. Now, your company is finally moving into the new millennium . It's up to you to take this data and change it into Excel workbooks, where you can maintain it more easily. In this situation, you need the Office button Open command to make the jump from text files to more capable Excel spreadsheets.
It's your job to review the daily sales transactions and look for emerging patterns or warning signs. To perform this analysis, you use Excel's formula smarts and charting tools. But the data you need to analyze is emailed to you at the end of each day as a text file. It's up to you to pull this information out of the text file and look at it in Excel. In this situation, you need the Data Get External Data From Text command. That way, you set up the link once, and you can refresh your connection whenever you need to bring it the newly updated data from the latest version of the file (just choose Data Manage Connections Refresh All). Best of all, your linked formulas and charts update themselves automatically when the new data flows in.

Data comes in many forms, and Excel isn't limited to ordinary text files. In fact, the Data.Get External Data.From Text command is part of a broader set of features for creating data connections . These connections can link to outside text files, XML files, databases, or even pages on a Web site. Using the data connection feature, you can perform regular (and even automatic) refreshes so you always have the latest data on hand. For more information about Excel's data connection features, see Chapter 24.