Hands on: Import and export data from Excel spreadsheets

You are offered a wide range of options under Open on the File menu (Office, Open, in Excel 2007). You can import data straight from Access and dBase files. With other files just compare their extension with the numerous extensions offered by Excel. Some bring in files from other spreadsheets.

Depending on your version of Excel you might find options to import files from Lotus 1-2-3, Quattro Pro, Foxpro, Oracle, Paradox and Microsoft Works. Some formats are maintained from days gone by like SYLK, the Symbolic Link format used by Multiplan and DIF, the Data Interchange Format used by Visicalc. When all else fails most other programs will export a text file.

Choose the option, Text Files (*.prn; *.txt; *.csv), select the file and Excel will open the Text Import Wizard offering numerous options for laying out the file on a worksheet according to your wishes (see the attached picture). You’ll notice there are other options for importing data on the Data menu (Data tab in Excel 2007).

Data imported this way is linked to the original source, whereas data imported using File, Open is simply a copy of it. In versions of Excel prior to Excel 2007 you can interact with an HTML web page. You can open an HTML file in Excel 2007 but for security reasons and because the interactive feature didn’t work with browsers other than Internet Explorer, that’s it.

The interactive feature has been replaced by Excel Services, which requires Office Sharepoint Server 2007. For more information about this see, ‘Publish a workbook to Excel Services’ in the Excel 2007 Help files. If you want to import data from a source unrecognised by Excel you can probably find a driver or file converter from the publisher of the software.

Mail merging
One common way of exporting data from a spreadsheet is to use mail merging. It’s useful when you wish to send the same letter to a number of people, be they clients, club members, sales prospects or councillors. With Excel and Word it’s particularly easy because Office has a Mail Merge Wizard. Start by creating a mailing list on an Excel worksheet.

The top row will have headings such as Title, First Name, Surname, and so on. These are the fields of your database. Each row will be a record and contain the name and address of one recipient. It will be easier later if you include the house number in the same field as the street rather than make it a separate field. Save and close that file.

Complete a Word letter template and close that new document too. Open Word and choose Letters and Mailings, Mail Merge Wizard on the Tools menu. In Step 1 choose Letters. In Step 2 choose Start from existing document and pick your new letter from the displayed list. In Step 3 click, Use an existing list. Browse to your Excel file and then the right worksheet.

Oddly, the field names and their columns may be in a different order. You can drag them around into the correct order with the mouse but it hardly matters as Step 4 gives you an opportunity to match the field names Word expects in an address block with the field names you have used.
Follow through with the rest of the Mail Merge Wizard to Preview, Save and Print your letters.

Now hear this
The most common ways of exporting data from an Excel file are by saving it to a storage source or printing it out. But you can also have audio output. If your PC has a sound card, and your speakers are switched on, you can have Excel proof-read your cell entries back to you.
On the View menu choose Toolbars and then Text to Speech.

(By default, the facility isn’t available in Excel 2007 and is undocumented. To add it, click on the down arrow at the right of the Quick Access Toolbar at the top left of the screen. Choose More Commands, Commands Not in the Ribbon. Click Speak Cells and the Add button, then Stop Speaking, Add. Click OK.)

Highlight the range of cells you wish to have read back to you and, on the toolbar, click Speak Cells. If the voice is male and you would rather have a lady read to you go to Start, Control Panel and choose Speech. (Text to Speech in Excel 2007.) Under the Text to Speech tab you can change from Michael or Sam to Michelle. The quality is much better in Excel 2007.

In prior versions all the voices sound similar to Stephen Hawking’s familiar speech machine. The procedures described here may vary slightly in different versions of Excel but not in any significant way.