Miscellaneous Excel

1. Miscellaneous Excel • Combining Excel and Access. • Importing, exporting and linking • Parsing and manipulating data.

2. Open files • COPY – DO NOT OPEN FILES FROM THE K DRIVE!! • k:IS201\IS201-Hilfer\LabClass\GamesLab.accdb to the u: drive or to your flash drive. • k:IS201\IS201-Hilfer\LabClass\AlaskaRetailers.txt to the u: drive or to your flash drive. • Open the GamesLab database in MS Access. • Use the “relationships” diagram in the Database Tools tab to familiarize yourself with the database. • Open Excel and create a new, blank workbook.

3. Importing data into Excel • Assumption is that data exists in a computerized form. • Avoid re-typing data that already exists.

4. Formats to import and export data • CSV: Comma separated values • Considered the “common denominator” among computer applications; almost any software can generated a CSV file. • MS Applications: Excel, SharePoint, Outlook, SQL Server, Access • XML: Extensible markup language. • Used to exchange data among different organizations. • Set of rules for encoding data; open standards. • ODBC: Open database connectivity • Standard interface among differing DBMS’s. • HTML: Hypertext markup language. • PDF: Portable document format.

5. Integrating Excel with other programs • When you integrate information between programs, the program containing the original information, or object, is called the source program, and the program in which you place the information created by the source program is called the destination program. • Importing/Copying: Contents are transferred from source to destination; no connection is made with source. Destination is in charge once the copy if complete. • Linking: Contents are transferred from source to destination, but an active connection is maintained to the source. Any changes made in the source are reflected in the contents.

6. Copying/Pasting data • In the Access database, open the customer table in datasheet view. • Copy the table. • Paste the table into Excel. • Note the format of the columns that are displayed in Excel. • The copies of the data are independent; changes made in Access or Excel will not affect the other.

7. Linking data from Access to Excel • Use a new worksheet in Excel. • On the data tab, select “Get External Data” and choose “From Access” • Locate your database as your data source and open it. • Look at the Data Link Properties and click OK. • Click OK for the OLE DB Initialization Information. • Choose the table called “Customer” and click OK. • Note that the data comes into Excel as a table • do not convert the table to range or the link will be lost. • Experiment with changing data between Access and Excel. • What can/can’t be changed? • What does the Refresh button do?

8. Access queries are a good way to link data • Write a query that combines all five tables in the Access database. • Create tab/query design • Create and save query as LabQuery. Look at it in datasheet view. • Link the data to Excel • Data tab/Get External Data/From Access • Locate LabQuery in database. • In Access, change Myrtle Johnson’s address to 123 Oak Street. • See the change in the query datasheet. • Look at the data in Excel. • Refresh the data in Excel and look again.

9. Key Points • Data can be copied and linked relatively easily between Access and Excel. • Must keep track of the source and destination. • The source can change data; the destination cannot. • Two way update of data is not automatic. Must re-upload or write a custom application to perform two way updates of data. • Let the two programs do what they do best. • Store and update data in Access; • Write queries in Access that will be uploaded and linked to Excel. • Do calculations and visualizations in Excel.

10. Parsing and Manipulating Data • Data parsing • Breaking data into smaller pieces by following a pre-defined set of rules. • Can parse based on context, construction, or other pre-defined method • Data manipulation • Moving or changing data content or structure based on a pre-defined set of rules. • Excel provides many functions for parsing and manipulating data

11. Manipulating Data • The first and last names of customers were entered into the database in all capital letters. We need to change the names so that only the first letter is capitalized (Proper format). • The Proper() function in Excel will accomplish this task • Upper() and Lower() functions convert all text to upper or lower case letters respectively.

12. Manipulation functions • Import data requiring manipulation • Data tab, Get External Data, click on From Text. • Locate the AlaskaRetailers.txt file and import it into Excel. • While this is a text file, it is not comma delimited; all data is stored on separate lines. This is a standard mailing label format – great for mailing labels, not as great for other purposes. • We are going to manipulate the data so that an address is on a single line divided into appropriate columns.

13. Concatenation • Combining data and literals together can be done with either the & or the CONCATENATION function. • Concatenate the data in A1 through A6 and place in cell B1. Separate each piece of data with commas. • Double click on the fill handle to fill the column. • Which data in column B is “right” and which is “wrong” for a CSV version of the data?

14. Extracting characters • The RIGHT and LEFT functions let you extract characters from the right and left side of a cell, respectively. • We are going to use the RIGHT and LEFT functions to better understand the contents of the data. • Syntax: =RIGHT(cell, number of characters) =LEFT(cell, number of characters)

15. Cleaning data • Sometimes data has blank spaces that must be eliminated before other rules can work correctly. • TRIM removes all the spaces in a cell except those between characters. =TRIM(CONCATENATE(A1, ", ", A2, ", ", A3, ", ", A4, ", ", A5, ", ", A6)) =CONCATENATE(TRIM(A1),", ",TRIM(A2),", ",TRIM(A3),", ",TRIM(A4),", ",TRIM(A5),", ",TRIM(A6)) • After cleaning the data with the TRIM function, then use the RIGHT function in column C to identify the last 5 characters of the contents of column B.

16. Finding text inside a field • Every correct row in column B includes a phone number as the last text string. That means the correct data has a dash as the first character in column C. • Column D will find that dash. =FIND("-",C1)

17. Sorting and deleting • Standard test procedure. We are about to delete rows from the worksheets. We don’t want to destroy our work (potentially…) so copy entire worksheet to a new worksheet. That way you can return and try again without having to start over. • Copy as values; do not copy the formulas. • Sort (data tab) by column D. • Delete all rows after the value 1 in column D. • Delete columns A, C, and D. • We now have comma delimited data in column A.

18. A wizard for conversion… • Select column A. • In the Data Tools group on the Data tab on the ribbon, click the Text to Columns button • Data type is delimited. • Data delimiter is a comma. • Preview the data. • Click the finish button. • Add a header row if desired.

19. Key Points • Excel provides many data manipulation functions. Just a few were discussed in class: • RIGHT, LEFT • TRIM • CONCATENATE • FIND (case sensitive locator) • SEARCH (not case sensitive) • LEN • EXACT • REPLACE • LOWER, UPPER, PROPER