Section 22.3. Transferring Data | Excel 2010: The Missing Manual

22.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, they're actually owned by different programs. For example, even if 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.

Figure 22-6. Top : Visio is a leading diagramming tool. Thanks to linking and embedding, you can integrate Visio objects into your worksheets as easily as charts or pictures. When you select Edit Paste Special in Excel and theres a Visio drawing on the clipboard, Excel gives you three choices: convert the diagram to an Enhanced Metafile graphic, convert the diagram to a bitmap, or paste it as a linked Visio object.
Bottom : If you use the last option, you gain the ability to edit the diagram without leaving Excelall you need to do is double-click the embedded object. The menus you now see let you control Visio.

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.

What you might want to do in cases like that is 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 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 so that it matches 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 use 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.

22.3.1. Exporting Tables of Data

The secret to pasting worksheet data into another application like Word is the same Edit 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 Edit Copy.

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

Switch to the target document, then select Edit Paste Special.

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. That's because, as in Figure 22-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 22-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. This is true even though you see in the embedded object box only the portion of data that you selected.

Select the type of object you want to insert in the Paste Special dialog box.

You can choose any supported format from the list. The format choices depend on the type of object you're inserting. For example, Excel gives you the ability to 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.

22.3.2. Importing Tables of Data

There's also no reason that you can't take text out of another program and 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 22-8 shows an example.

Figure 22-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 formatting of the original document. This technique works just as well if you copy a table of information off a Web page and paste it into Excel.

Instead of using Edit Paste Special to paste content into Word, you can use Edit Paste as a shortcut. When you do, Word inserts formatted text, and a smart tag icon appears next to the newly inserted content. You can click this icon and 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).

22.3.3. Importing Text Files

Sometimes you might 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 it can be inserted into the cells of an Excel worksheet. Excel provides an intelligent tool, the Text Import Wizard, that helps you perform text file conversions.

Up To Speed Why Text Files Matter

With all the copying, pasting, and formatting features available in Windows, you might wonder why anyone would ever stoop to the lowly level of plain text. In fact, using plain text is more common than you might think because text files are the lowest common denominator when it comes to data. That means that even if you have a program that runs on an old-fashioned 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. This predicament is most commonly the case 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).

For example, consider a management tool that lets you assign projects to employees in a company. This tool probably won'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.

To import a text file, follow these steps:

From within Excel, select File Open

The Open dialog box appears.
From the "Files of type" list at the bottom of the window, select Text Files.

Now 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.
Browse to the file you want to import, and select it.

For a quick and easy test, you can use the log.txt file included with the downloadable content for this chapter on the "Missing CD" page at 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. Figure 22-9 shows all the steps you'll go through in the wizard.

Figure 22-9. The Text Import Wizard takes three steps to import the contents of an ordinary text file into a new worksheet. Top : In this example, a delimited text file containing a computer's log information is being imported. In the first step, the file appears as a stream of unstructured information. Middle: Life gets better in step two, 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 it and display it properly in the preview.
Bottom : The third step provides a last chance to change how Excel interprets the data in each column. Usually, you don't need to make any changes at this stage, and you can just click Finish to complete the importing process.

In step 1 of the wizard, choose "Original data type." You have two options, "Delimited" or "Fixed width," depending on the format of your text file.

Usually, the program that creates the text file will indicate what type of format it's using, or it'll give you the chance to choose one of several supported formats. If you're unsure of the exported format, you might 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.

Delimited means that there is a separatorusually a comma, a tab, or a semi-colonbetween each column. For example, 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 apart. For example, 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 
```
Tip: Most programs that export data to text use delimited text files. That means when you're importing data into Excel, you'll often use the delimited option. To tell whether your text file is 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 most common choices.
Click Next and complete step 2 of the Text Import Wizard.

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 the character that's used to separate columns in the Delimiter box. For example, if you're importing a comma-delimited file (where a comma separates each column), 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.

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 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, you simply need to select a column in the "Data preview" section. Then, choose the data type from the column in the "Column data format" box. One reason you might take this extra step is if you have a cell that contains numeric content, but you want to treat it as text.
Click Finish to complete the wizard.

The data appears in your worksheet, starting at cell A1.