370 likes | 780 Views
Importing & Exporting Data. Applications of Spreadsheets. Importing and Exporting Data. Clearly, the ability to import, export, and otherwise exchange data can directly impact your productivity. There are important techniques for exporting your worksheets
E N D
Importing & Exporting Data Applications of Spreadsheets
Importing and Exporting Data • Clearly, the ability to import, export, and otherwise exchange data can directly impact your productivity. • There are important techniques for • exporting your worksheets • importing data from text files, alternate spreadsheet formats, and other external sources • sharing data among all of the Microsoft Office 2003 applications.
Saving and Exporting Structured Data – Selecting an Export Format for EX1041.xls
Importing Data using the Text Import WizardStep 1 of 3 Text files exported from other programs typically use commas, quotation marks, and tabs as delimiters between fields Location of the text file to be imported Preview of the file contents to be imported
Importing Data using the Text Import WizardStep 2 of 3 Select the delimiter used to separate the columns of data
Importing Data using the Text Import WizardStep 3 of 3 Click a column in the Data preview area and then select a data format
Importing Data From External Sources Launch the Data Connection Wizard to establish a connection to an external data source The previously created database and Web query files appearing in your list area may differ from these
Import Data Dialog Box Edit the data connection query parameters before importing the data. This is an advanced feature Specify the external data range in the worksheet Specify options for refreshing (re-importing) and formatting the data
Import Data Dialog Box The external data range begins at cell A1 and extends down and to the right until all records and fields are imported The External Data toolbar appears, allowing you to quickly refresh the data or to change the data range properties
External Data Range Properties Dialog Box You must save the query definition so that Excel 2003 knows how to refresh the data in the worksheet Specify when to run the query definition and refresh the data Use these options to specify how data is to be entered into the worksheet when refreshed As specified using these options, the data is imported with field names in row 1 and columns are formatted to their best-fit width
Opening a Data Connection in Microsoft Internet Explorer Microsoft Office Web Components are used to display the results of the ODC data file connection The ODC file is stored in the My Data Sources folder, beneath My Documents, and can be viewed using the File Open command
Importing Web Data Example Web query files installed with Microsoft Office Excel 2003
Executing a Web Query The four stock symbols (AMZN, ADBE, MSFT, and SEBL) are represented on these four rows
New Web Query Dialog Box Click this arrow icon to select the entire Web page for importing Each arrow icon represents a table area in the Web page that you may import into the workbook
Loading a Local Web Page Click this arrow icon to select the table of values for importing
Web Query Options Dialog Box Select either of these options to preserve formatting
Importing an HTML Table To edit the Web query, click this button to return to the dialog box
AutoCorrect Options Dialog Box Select this check box to enable the Smart Tags feature in your workbooks
Round-Tripping with HTML You will select this area of the Web page to copy to the Clipboard
Saving a Worksheet as an Interactive Web Page Specifying that the Web page include the Spreadsheet Office Web component
Displaying a Web Page with the Spreadsheet Office Web Component Spreadsheet Office Web component toolbar