Transcript Document

Miscellaneous Excel
• Combining Excel and Access.
– Importing, exporting and linking
• Parsing and manipulating data.
1
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.
2
Importing data into Excel
• Assumption is that data exists in a computerized
form.
• Avoid re-typing data that already exists.
3
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.
4
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.
5
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.
6
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?
7
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.
8
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 reupload 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.
9
Parsing and Manipulating Data
• Data parsing
– Breaking data into smaller pieces by following a predefined set of rules.
– Can parse based on context, construction, or other predefined 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
10
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.
11
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.
12
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?
13
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)
14
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.
15
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)
16
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.
17
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.
18
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
19