Importing Data into Excel - s1093575.instanturl.net

Importing Data into Excel - s1093575.instanturl.net

Chapte 18 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. BUSINESS ANALYTICS: DATA ANALYSIS AND DECISION MAKING Importing Data into Excel Introduction Any statistical analysis presumes that you have the appropriate data in a format suitable for analysis. The data might exist: In an Excel filewhich might still need to be rearranged to get it in the form of a rectangular data set. In a text file (or ASCII file)which is any file that can be opened and read in a text editor such as Notepad; it can be imported into Excel using Excels text import wizard.

In a relational database (such as Access, SQL Server, Oracle) which can be imported into Excel by forming a query using the Microsoft Query package. A query specifies exactly which data you want to import. On the Webwhich can be imported into Excel by creating a query and then running it in Excel. Once the data is imported, it may need to be cleansed to fix wrong values. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Rearranging Excel Data Even if the data already exists in Excel, it may need to be rearranged in the form of a data seta rectangular array of data with observations in rows, variables in columns, and variable names in the top row.

Sometimes simple cutting and pasting works. In other cases, advanced Excel functions are required. In all cases, it is best to map out a plan and then decide how to implement it. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.1: Baseball Salaries Original.xlsx Objective: To rearrange the data from the baseball Web queries into a single data set. Solution: Data on baseball salaries was imported into Excel from a Web site, with a separate Web query for each of the 30 teams. The results for a typical team are shown to the right, with only a few players listed. To rearrange all of the data into four long columns with the headings Player, Team, Salary, and Position, follow these steps: 1. Insert a blank column before column B, and enter the label

Team in cell B2. 2. Cut the Arizona Diamondbacks team name from cell A1 and paste it next to the first Arizona player in cell B3. Then copy it down for the other Arizona players. 3. Repeat step 2 for each of the other teams. 4. Delete unnecessary rows of labels for the other teams. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.2: CPI.xlsx (slide 1 of 2) Objective: To rearrange the monthly data into two long columns, one with month-year and one with the CPI. Solution: Monthly data on the Consumer Price Index (CPI) was imported from the Web using a Web query. A few rows appear below. The desired results after rearranging are shown to the right. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.2:

CPI.xlsx (slide 2 of 2) To rearrange the data into two long columns, follow these steps: Create the range name Data (for all the CPI values, not the headings in row 1 or column A). Add a new worksheet for the rearranged data, create the column headings in row 1, enter 1 in cells A2 and B2, and enter 1913 in cell C2. To generate the recurring pattern of 1 to 12 in column B, enter the formula =IF(B2<12,B2+1,1) in cell B3 and copy this down as far as necessary. To generate the pattern in column A, enter the formula =IF(B3=1,A2+1,A2) in cell A3 and copy it down.

To generate the years in column C, enter the formula =IF(B3=1,C2+1,C2) in cell C3 and copy it down. To generate the month-year values in column D, enter the formula =DATE(C2,B2,1) in cell D2 and copy it down. To generate the CPI values in column E, enter the formula =INDEX(Data,A2,B2) in cell E2 and copy it down. Copy columns D and E and paste them over themselves as values. Then delete columns A-C. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Importing Text Data Most software packages store documents in a proprietary binary format that is readable only by that package. However, most software packages allow you to save a file as a text file, usually with a .txt extension. A text file can be fixed width or delimited. With fixed width, each variables value starts and stops at fixed positions (columns) in the line. Each

With delimited data, there is a delimiter character, usually a tab, comma, semicolon, or space, that separates the values in a line. The line of data has the same length, and the columns line up. lines are typically of different lengths and do not line up nicely. Excel can import either fixed-width or delimited formats with its text import wizard. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.3: srn_tmp.txt (slide 1 of 2) Objective: To import the fixed-width text file data into Excel by using Excels text import wizard. Solution: The text file srn_tmp.txt was downloaded from the Web. It contains state, regional, and national (srn) annual data (1895-2005) on temperature. A small portion of the data is shown below.

The Web site from which the text file was downloaded also has a data dictionary, srn_data.txt, that indicates what the variables are and how they are stored in columns. Part of this data dictionary is shown 2015 Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible toCengage the right. Example 18.3: srn_tmp.txt (slide 2 of 2) Open the srn_tmp.txt file within Excel, and the first step of the text import wizard appears. Select Fixed width. The second step of the wizard allows you to separate (or parse) the

columns as listed in the data dictionary. Click on the third, fourth, and fifth positions on the ruler. The last step of the wizard allows you to fine-tune the import, column by column, but bypass this step and simply click Finish. The data is imported into Excel, as shown below. Create column headings in row 1, using the data dictionary as a guide. Use Save As to save the .txt file as an .xlsx (or .xls) file. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.4: Mobile Subscriptions.txt (slide 1 of 2) Objective: To see how delimited text data can be imported into Excel with the import text wizard. Solution: Annual data by country on the number of mobile subscribers during 2002-2009 was downloaded from the Web into the file Mobile Subscriptions.txt. A portion of the file is shown below. This time there are column headings in row 1, but the ragged lines indicate that this file must be delimited, not fixed width.

2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.4: Mobile Subscriptions.txt Open the Mobile Subscriptions.txt file in Excel, and check the Delimited option in step 1 of the wizard. In step 2, Excel guesses that the file is tab-delimited, which is correct, so click on Finish to accept this. Some of the imported data is shown below. (slide 2 of 2) The Flags column contains no data, so it can be deleted. Column A can also be deleted because it includes a constant value, Mobile Subscribers. The numbers in column D can be reformatted to Numeric with 0 decimals. Use Save As to save the file in .xlsx (.xls) format.

2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Comments About Importing Text Data If a text file is comma-delimited and is saved as a .csv file, you can open it directly into Excel, without the import text wizard. Excel automatically parses the values between the commas into columns. Sometimes you will find a table of data on the Web where there is no option to save it in some type of format, text or otherwise. You can try copying and pasting the data into Excel, but it is possible that everything will be pasted into a single column. If this happens, highlight the data in this column and click on the Text to Columns button on Excels Data ribbon.

The purpose of this button is to parse delimited data in a single column into several columns. Whenever you parse text data into Excel columns, there is always the chance that the data will not line up properlythat is, the data will get into the wrong columns. Look closely at the parsed data before proceeding. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Importing Relational Database Data Database packages such as Access, SQL Server, Oracle, and many others are extremely complex and powerful packages. For database creation, querying, manipulation, and reporting, they have many advantages over spreadsheets. However, they are not nearly as powerful as spreadsheets for statistical analysis.

It is often necessary to import data from a database package into Excel, where the statistical analysis can be performed. Microsoft includes software called Microsoft Query in its Office suite that makes the importing relatively easy. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Introduction to Relational Databases The Excel databases are often called flat files or, more simply, tables. They are also called single-table databases, where table is the database term for a rectangular range of data, with rows corresponding to records and columns corresponding to fields. Flat files are fine for relatively simple database applications, but they are not powerful enough for more complex applications.

A relational database is a set of related tables, where each table is a rectangular arrangement of fields and records, and the tables are linked explicitly. The linked fields are called keys. A primary key must contain unique values, whereas a foreign key can contain duplicate values. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Using Microsoft Query There are two ways to import data into Excel in Excel 2007 or later. The first method uses the From Access button in the Get External Data group on the Data ribbon. It is limited to importing whole tables or saved queries.

The second method employs the Microsoft Query software, which allows you to import all or part of the data from many database packages into Excel. It comes with the Office package, but may need to be installed. Once Microsoft Query is installed, importing data from Access (or any other supported database package) is essentially a three-step process: 1. 2. 3. Define the source, so that Excel knows what type of database the data is in and where the data is located. Use Microsoft Query to define a query. Return the data to Excel. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.5: Shirt Orders.mdb (slide 1 of 3)

Objective: To illustrate how Microsoft Query can be used to import the results of queries on the Shirt Orders database into Excel. Solution: Fine Shirt Company has created an Access database file that has information on its sales to its customers during the period of 2005 through 2009. There are three related tables in this database, Customers, Products, and Orders, with a link between the CustomerID fields in the Customers and Orders tables and a link between the ProductID fields in the Products and Orders tables, as shown in the diagram below. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.5: Shirt Orders.mdb (slide 2 of 3) The entire import procedure is done within Excel and Microsoft Query, not Access. First, define a data source. Open a blank spreadsheet in Excel and select From Microsoft Query from the From Other Sources dropdown menu on the Data ribbon. Select the top item in the Choose Data Source dialog box and then click OK. Fill in the Create New Data Source dialog box, and then the ODBC Microsoft

Access Setup dialog box to indicate which database file you want to use. Click OK to return to the Choose Data Source dialog box. In the Choose Data Source dialog box, make sure the Shirt Orders item is selected and the bottom checkbox is unchecked, and click OK. This brings up the Add Tables dialog box and begins the second step, where you define the query. Specify which tables are relevant for the query, which fields you want to return to Excel, and which records meet the criteria you spell out. The final step is to get these data back into Excel. Select the Return Data to Microsoft Excel menu item from the File menu. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.5: Shirt Orders.mdb

The results of one query (Find all of the records in the Orders table that correspond to orders for at least 80 units made by the customer Shirts R Us for the product Longsleeve Tunic, and return the dates and units ordered for these orders) are shown to the right. Once the results of the query data are returned to Excel, you can then begin the statistical analysis of the data. (slide 3 of 3) The data are still linked to the query. This means that you can refresh the data in Excel if the Access data change. You can also get back to Microsoft Query so that you can edit your query. If your ultimate goal is to create a pivot table based on the database data, you can do this directly, as shown in the next example.

2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.5 (Continued): Shirt Orders.mdb (slide 1 of 2) Objective: To illustrate how Microsoft Query can be used to import data directly into a pivot table. Solution: Fine Shirt Company would like to break down revenue from its various customers and products by using pivot tables. To base a pivot table on external data, go through Microsoft Query, not through the usual PivotTable button on the Insert menu. Get into Microsoft Query and define a query. When you select the Return Data to Microsoft Excel menu item from the File menu, you see a dialog box where you can specify the type of report you want and where you want it. Select PivotTable Report (or PivotChart and PivotTable Report). From here, you can create any pivot tables in the usual way.

2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.5 (Continued): Shirt Orders.mdb (slide 2 of 2) One possible pivot table, showing total revenue broken down by product, customer (using the Report Filter area at the top), and quarter of year, is shown below. You have the option of obtaining corresponding pivot charts automatically. The pivot table is linked to the query. This means that you can go back to Microsoft Query, edit the query, and return to Excel to update the pivot table. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible SQL Statements Queries represent a large part of the power behind relational databases.

To standardize queries across packages, SQL (structured query language) was developed several decades ago. It is often called the language of databases. Behind each query developed in Microsoft Query is an SQL statement. The statements can be viewed by clicking the SQL button in the Query toolbar once you have created a query. The statements include keywords such as SELECT, FROM, WHERE, and AND, as shown in the example below. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Web Queries (slide 1 of 2) Web sites that contain data are structured in all sorts of ways, and the steps required to import the data into Excel for analysis vary greatly.

Many Web sites provide buttons that allow you to download the data directly into Excel. On some Web sites, the only way to get the data into Excel is to cut and paste. Web query is an Excel tool that lies between these two extremes. Web queries search for HTML

tags, find the corresponding data, and bring them into Excel in the usual row and column format. Many data sets on the Web import beautifully with Web queries, but some return virtually nothing. Sometimes you need to run several Web queries on the same basic site to get all of the data you want. In a URL, the part to the right of the question mark (if any) is called the query string, and it specifies exactly which data you want. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Web Queries (slide 2 of 2) To get data into Excel using a Web query:

Make sure you have an active connection to the Web, and open a new workbook in Excel. Click the From Web button on the Data ribbon. Fill in the dialog box, the most important part of which is the URL (the address of the page) at the top. Once you enter the URL, click Go. You will see the Web page with yellow arrows next to all of the tables. Click any of these yellow arrows to change them to green checkmarks. The selected tables will then be imported into Excel. After you click Import, specify where to place the results. A link to the Web page remains, so you can refresh to obtain the latest data. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Cleansing Data You cannot count on real-world data sets to be perfect.

This is especially the case when you obtain data from external sources such as the Web. It is your responsibility to correct any problems before you do any serious analysis. Cleansing data requires careful detective work to uncover all possible errors that might be present. Once an error is found, it is not always clear how to correct it (for example, missing data). Some subjectivity and common sense must be used when cleansing data sets. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.6: Data Cleansing.xlsx (slide 1 of 2)

Objective: To find and fix errors in this companys data set. Solution: The data file has data on 1500 customers of a particular company. A portion of these data appears below. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible Example 18.6: Data Cleansing.xlsx The data set has a number of problems, all of which you might encounter in real data sets: Duplicate SSNs Duplicate birthdates (as a result of the code 9999 being used for missing data and then formatted as a date)

Negative ages (as a result of the Y2K problem) Too many categories (as a result of a space being entered before a value in a text field) Quotes around numbers (causing them to be interpreted as text, not numbers) Suspiciously small incomes (as a result of incomes being entered without trailing zeroes) Suspicious outliers (as a result of the average spent being entered rather than the total amount spent) Use Excel tools to search for the suspicious data values. (slide 2 of 2) Sort on columns and look for unusual or duplicate values. Create a scatterplot of the data and look for suspicious outliers. Then use other Excel tools, such as Find and Replace, to fix the errors. 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible

Recently Viewed Presentations

  • The French and Indian War 1756-1763 - inetTeacher.com

    The French and Indian War 1756-1763 - inetTeacher.com

    Times New Roman Arial Tahoma Wingdings Verdana Balance The French and Indian War 1756-1763 Reasons for the War The Native Americans The Albany Plan of Union, 1754 The Albany Plan of Union, 1754 The British and Colonial Militia PowerPoint Presentation...
  • Musculoskeletal Injury Prevention Program SRH Workshop Module 3

    Musculoskeletal Injury Prevention Program SRH Workshop Module 3

    P. 33 in the participant's manual. ... Push off of the surface they are being transferred from with their arms (on the bed, on their thighs, on the arms of a chair/wheelchair) ... what count you will use, who will...
  • Trigonometric Applications and Models

    Trigonometric Applications and Models

    Trigonometric Applications and Models Solving A Right Triangle Given an Angle and a Side Example 1: Application Inverse Trigonometric Functions on a Calculator Trigonometric Applications and Models Solving A Right Triangle Given an Angle and a Side Example 1: Application...
  • Pre-conference Educational Workshop on Birth Defects Surveillance

    Pre-conference Educational Workshop on Birth Defects Surveillance

    Obstet Gynecol. 2010 Jan;115(1):109-15. ... Dellinger AM, Jamieson DJ. Maternal injuries during the periconceptional period and the risk of birth defects, National Birth Defects Prevention Study, 1997-2005. ... Pre-conference Educational Workshop on Birth Defects Surveillance
  • ThePerfectSociety.WebQuest.Instructions

    ThePerfectSociety.WebQuest.Instructions

    RESOURCES Students will use the Internet to access information. The final product is a collaborative writing report that sums up all of the members' interviews. The Interviews will be added to the appendix of the paper. A WebQuest on The...
  • Queensland School Bus Alliance National Disability Insurance Scheme

    Queensland School Bus Alliance National Disability Insurance Scheme

    Whilst support for the NDIS is universal, like many Government policies, it is felt that the general concept is there, but the detail is very grey and lacking. ... However, all remain in limbo and deeply concerned due to the...
  • Division of Polynomials - Cengage

    Division of Polynomials - Cengage

    Arial Times New Roman 1_Default Design Microsoft Equation 3.0 Division of Polynomials Dividing Polynomials Example: Divide & Check Example: Divide & Check Example: Division With Zero Remainder Example: Division With Nonzero Remainder
  • Identity Theft Prevention Program Red Flags Rules Fighting

    Identity Theft Prevention Program Red Flags Rules Fighting

    Don't forget portable devices and offsite locations, including employees' home computers. For example, an employee emails to a personal account, or copies to USB storage, sensitive information for use while working from home. Trace the flow of information from data...