Importing and Exporting Files

Excel gracefully accepts proprietary data created in many other applications. Excel also makes it easy to import data from text files and helps you parse it into worksheet columns.

Note 

Mountains of very specific, sleep-inducing technical details are available about importing and exporting files. If you need such detail for conversion issues such as transferring hundreds of macro-driven Lotus files into Excel, you should consult the Microsoft Office 2007 Resource Kit, available from Microsoft Press. Also, for information regarding sharing data with other Microsoft Office 2007 applications and working with external databases, see Chapter 31, "Using Excel Data in Word Documents," and Chapter 23, "Working with External Data."

Using the Open and Save As Commands to Import and Export Files

To import a file from another application or from an earlier version of Excel, click the Microsoft Office Button, click Open, and select the file you want to import from the list of files in the Open dialog box. To narrow the list of files and zero in on a specific file type you want to import, use the File Name drop-down list at the bottom of the Open dialog box, shown in Figure 2-38. When you choose a file type, the Open dialog box displays only files of that type in its list. Keep in mind that it is not necessary to include a file name extension when you import a file because Excel determines the format of the file by examining the file's contents, not its name.

Figure 2-38: Use the File Name drop-down list to specify files created by a particular application.

To export an Excel file to another application or to an earlier version of Excel, click the Microsoft Office Button, and click Save As. Then select the application you're exporting to in the Save As Type drop-down list, which is similar to the File Name drop-down list shown in Figure 2-38, except it contains somewhat different format options for saving. For example, although Text Files is the only text format listed in the Open dialog box, you can save an Excel file in many different flavors of text format, including tab-delimited text, Unicode text, formatted text, and more.

Sharing Data with Excel for the Macintosh

Excel for the Macintosh since 1998 has been using the same file format as Windows versions of Excel from 97 through 2003. You can share these files with Macintosh users by simply transferring files from one computer to the other.

To save an Excel 2007 file to share with someone using the Macintosh version of Excel, click the Microsoft Office Button, click Save As, and click the Microsoft Excel 97-2003 Workbook option in the Save As Type drop-down list.

To import Macintosh files to your PC, you first need to transfer the file to your PC via a cable, a disk, a network, an e-mail, a Web site, or a tool such as MacOpener. Exporting files from a Windows version of Excel to the Macintosh version is just as easy as importing Macintosh files. Simply transfer the file from the Macintosh computer to Windows using your method of choice, and then use the Open command to load it into Excel.

Sharing Data Beyond Excel

Yes, some people don't use Excel, and you might meet one someday. Seriously, plenty of reasons exist for making Excel-based data accessible outside the program, whether or not Excel is available at the destination. Posting data to a Web site or creating data sets for proprietary analysis software are two possible applications where you might want data that can fly free, independent of the Excel mother ship.

Adjusting Date Values

Although the Windows and Macintosh versions of Excel share many characteristics and capabilities, they do not use the same date system. In the Windows version of Excel, the base date is January 1, 1900. In the Macintosh version, the base date is January 2, 1904. When you transfer files either to or from the Macintosh, Excel maintains the date type by selecting or clearing the 1904 Date System option in the When Calculating This Workbook area in the Advanced category in the Excel Options dialog box. This technique is usually acceptable, but it can cause problems when a date from a Macintosh file is compared with a date from a Windows file. For this reason, we suggest you use the same date setting on all your machines.

Using Web File Formats

Two options in the Files Of Type drop-down list in the Save As dialog box produce files that you can use as Web pages: Web Page (HTM, HTML) and Single File Web Page (MHT, MHTML). They produce essentially the same result, the important difference being that the Web Page format saves not only a main HTML file but also a folder containing supporting files that must travel with the main file. As you might expect, the Single File Web Page format manages to cram it all into a single file without using the supporting folder. Single File Web Page has the advantage of being more portable, but Web Page gives you more control over individual elements. A separate cascading style sheet is created using the Web Page format, along with individual HTML files for each worksheet in the workbook. Figure 2-39 shows the contents of the supporting folder that is created after saving a seven-sheet workbook entitled Humongous using the Web Page file format.

Figure 2-39: The Web Page file format creates a folder full of supporting files to go with the main Web page.

If you are an HTML aficionado, you can open the supporting files in other programs. For example, if the original workbook contains any graphics, Excel saves them as separate image files (JPEG, PNG, or GIF) that you could modify with an image-editing program. Or you could change the fonts used by editing the cascading style sheet using a text editor such as Notepad. This is not the kind of work for the timid, of course. The slightest editing error in the HTML code of any of the files has the potential to render them all unusable.

Importing and Exporting Text Files

To export an Excel file as a text file, click the Microsoft Office Button, click Save As, and select one of the following eight text formats from the Save As Type drop-down list. In all of these formats, Excel saves only the current worksheet. Number formatting is preserved, but all other formatting is removed.

• Formatted Text (Space Delimited) (*.PRN) This creates a file in which column alignment is preserved by means of adding space characters to the data in each column so each column is always filled to its maximum width.

• Text (Tab Delimited) (*.TXT) This separates the cells of each row with tab characters.

• Unicode Text (*.TXT) This is a worldwide standard text format that stores each character as a unique number; Unicode defines a number for every character in every language and on any computer platform.

• CSV (Comma Delimited) (*.CSV) This separates the cells of each row with commas. Comma-delimited text files are preferable to tab-delimited files for importing into database management programs. (Many database management programs can accept either form of text file, but some accept only .csv files.) Also, many word-processing applications can use .csv files to store the information for mail merge operations.

• Text (Macintosh) (*.TXT) This saves the current worksheet as a tab-delimited text file using the Macintosh character set.

• Text (MS-DOS) (*.TXT) This saves the current worksheet as a tab-delimited text file, compatible with the character-based MS-DOS interface.

• CSV (Macintosh) (*.CSV) This saves a comma-delimited text file using the Macintosh character set. The differences between the normal, Macintosh, and MS-DOS variants of each file type have to do only with characters that lie outside the normal 7-bit ASCII range.

• CSV (MS-DOS) (*.CSV) The MS-DOS options use the IBM PC extended character set. (You might see this also referred to as OEM text.) Select one of these options if you intend to import your text file into a non-Windows-based application.

Note 

Office Excel 2007 uses a file format that is incompatible with previous Excel versions. And Excel 2003 shares with its predecessors (Excel 2002, Excel 2000, Excel 97) a file format that is incompatible with even older Excel versions. However, you can use the Save As command to export Excel 2007 workbooks that will play nicely with older versions of Excel, using two formats in the Save As Type list: Excel 97-2003 Workbook and Microsoft Excel 5.0/95 Workbook.

If you regularly share files with colleagues using different vintages of the program, you might want to read "Ensuring File Compatibility with Previous Versions of Excel" on page 54.

Other File Formats

You can use a few other file formats, most of which you won't even need unless you have a particular program with which you want to share data. Data Interchange Format (DIF) is a legacy format that allows the specification of data in rows and columns, saves only the active worksheet, and does not process graphic content. Symbolic Pink (SYLK), a format that dates back to the days of VisiCalc and Multiplan, is a sort of "rich-text format" for spreadsheets that saves only the active worksheet and does not process graphic content.

Portable Document Format (PDF) and XML Paper Specification (XPS), two formats that allow accurate visual representations of documents to be easily shared across platforms, were going to ship with the 2007 Microsoft Office system, but legal entanglements at the time of this writing have jeopardized this plan. Fear not-one or both of these formats should be downloadable from the Office Update Web site (www.officeup-date.com) if they are not available in your copy of Excel.