Transcript Document

by Mary Anne Poatsy, Keith
Mulbery, Eric Cameron, Jason
Davidson, Rebecca Lawson,
Linda Lau, Jerri Williams
Chapter 10
Imports, Web Queries,
and XML
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
1
• Import data from external sources
• Create a Web query
• Manage connections
• Convert text to columns
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
2
• Manipulate text with functions
• Use Flash Fill
• Understand XML syntax
• Import XML data into Excel
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
3
• Importing—inserting external data into current
application
– Embedded files
• Edited in Excel
• Changes not reflected in source
– Linked files
• Changes made in source
• Refresh required to update Excel data
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
4
• Most common imported file types:
– Text files
– Access database files
• Text files
– Have .txt file extension
– Contain characters: letters, numbers, and symbols
– Data delimited by special characters
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
5
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
6
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
7
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
8
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
9
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
10
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
11
• Linked data:
– Data on a Web page or external database can change
– Changes are not reflected in Excel
– Connections should be periodically refreshed
• Click Refresh All in the Connections group
• Click the Refresh All arrow in the Connections group
• Right-click in a range of data and select Refresh
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
12
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
13
Imported text may not be structured correctly
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
14
• Excel text functions:
– CONCATENATE
=CONCATENATE(text1,text2)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
15
• Excel text functions:
– PROPER =PROPER(text)
– UPPER =UPPER(text)
– LOWER =LOWER(text)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
16
• Excel text functions:
– SUBSTITUTE =SUBSTITUTE(text,old_text,new_text,
instance_num)
– TRIM =TRIM(text)
– LEFT or RIGHT =LEFT(text,num_chars) or
=RIGHT(text,Num_chars)
– MID =MID(text,start_num,num_chars)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
17
• Flash Fill
– Enter data in 1 or 2 cells to establish a pattern
– Excel completes the data entry
– Can be used generate new columns
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
18
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
19
• eXtensible Markup Language (XML)
– Developed by World Wide Web Consortium (W3C)
– Standardized file format for exchanging data across
• Applications
• Operating systems
• Hardware
– Uses tags
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
20
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
21
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
22
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
23
• External data imported into Excel
– Embedded
• Changes made directly in Excel
– Linked (connected)
• Changes in source are not reflected in Excel
• Data link must be refreshed
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
24
• External data file sources
– Text files
• Web queries
• CSV
• XML
– Text functions
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
25
• External data file sources
– Access databases
• Tables
• Queries
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
26
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
27
All rights reserved. No part of this publication may be reproduced, stored in a retrieval
system, or transmitted, in any form or by any means, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of the publisher. Printed in the
United States of America.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
28