{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}


Ch5-1_ClassNotes_fall10-1-1 - Chapter 5 Part 1 of 2...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter 5, Part 1 of 2: Importing & Exporting Data Chapter 5, Part 1 Skills Understand and apply the critical guidelines for list development that enable the automatic Excel list features to work correctly. Import delimited data into Excel. Use formulas and functions to "clean" or "scrub" imported data. Use Web queries to import dynamic Web data into Excel. Import static Web data into Excel. Chapter Vocabulary Excel "list" (of data) a record (row) an attribute (column) field (2 meanings) delimited file formats and common delimiters "cleaning" or "scrubbing" imported data Web Query Web table literal (text or numeric) value Introduction to List Management Critical List Guidelines: 1. No blank columns or rows within the list (blank cells are okay) 2. Column header row formatted differently from data rows. 3. Blank column and row around list (except at worksheet edges), nothing stored in cells adjacent to list. Chapter Demonstration 1: Importing Delimited Data Formats into Excel Be sure to right-click and save the following files from Scholar to your computer. Do not attempt to open them from Scholar. Animal.csv Customer.csv City.txt Employee.txt A. Import a comma-delimited file into Excel. 1. Open the file Animal.csv file in Notepad ( Right-click on the file Open With Notepad ). Note that commas are separating the fields of each row of data. A paragraph mark indicates the end of a row of data. Close the Notepad file. 2. Open the Animal.csv file in Excel in one of the following ways: 2.a.Open Excel, then open the file from inside Excel using Office Button Open . 2.b. Right click on the file, choose Open with Microsoft Office Excel 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
2.c.Double click on the file. (When Excel is available, it is the default application for opening a file with .csv extension.) 3. Improve the Animal list in Excel by doing the following: 3.a.Make sure the values in the DateBorn column are recognized by Excel as dates (they may already be). If needed, select the column and apply a date format. 3.b. Re-format that column header to General or Text format. 3.c.Format the entire column header row to distinguish it from the data rows. 3.d. Auto-size all the columns at once to fit the data. (May also select entire worksheet to accomplish this.) 4. If the worksheet name does not appear as Animal , rename the worksheet to that name. 5. Name the workbook PetStore.xlsx . (Use the default Excel file extension since this file does not need to support macros.) B. Importing special number fields as text data type 1. Open the Customer.csv file in Excel as you did the previous file. What happened to the zip codes? 2. Without saving, close the Customer workbook you just imported. Now try opening the file using the Text Import Wizard in Excel to import it into the same workbook as the Animal data from the previous exercise.
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}