Data Integration in Excel - PowerPoint PPT Presentation

1 / 41
About This Presentation
Title:

Data Integration in Excel

Description:

Decide data types while importing. Manage multiple delimiters. Manage preceding negative signs ... Custom schema based editing, validation and import export ... – PowerPoint PPT presentation

Number of Views:143
Avg rating:3.0/5.0
Slides: 42
Provided by: drnitinp
Category:

less

Transcript and Presenter's Notes

Title: Data Integration in Excel


1
Data Integration in Excel
  • Dr. Nitin Paranjape
  • MVP (Office System)
  • Chairman and MD, Maestros

2
Version information
  • Excel 2007 for all demos
  • Many features exist in 2003 as well
  • Keep Excel open to understand better
  • But, dont try to do what I am showing
  • Using Beta version
  • Complicated process of broadcasting voice
  • Please bear with any crashes, disconnections

3
Objectives
  • Understand available integration options
  • Mapping business scenarios to available features
  • Understanding strengths and limitations of data
    handling

4
POLL
  • How many of you have used Excel for data handling
    in your applications?

5
Data integration areas
  • Data capture within Excel
  • Getting external data into Excel
  • Accessing Excel data from outside
  • By Exporting it to another format
  • By keeping it within Excel
  • Processing data within Excel
  • Using Excel as a report writer

6
Primary purpose of spreadsheet is to analyze
data. NOT to store data.
7
Data sources
  • ODBC / OLEDB
  • SPS list
  • XML
  • OLAP
  • Text
  • Query files

8
Import tools
  • Wizard (only ODBC)
  • MS Query (only ODBC)
  • Text import
  • Text import VBA code
  • Data connection wizard

9
Query files and connections
  • This facility has been available for years
  • It works on ODBC sources only
  • Uses either a wizard or MS Query
  • Demo (Wizard and MS Query)

10
Text Import
  • Highly complex text import possible
  • Fixed width or Delimited
  • Multiple delimiters supported
  • Decide data types while importing
  • Manage multiple delimiters
  • Manage preceding negative signs
  • Demo

11
Text import programmatically!
  • With ActiveSheet.QueryTables.Add(Connection"TE
    XTC\temp\test.txt", _
  • DestinationRange("A1"))
  • .Name "test"
  • .FieldNames True
  • .RowNumbers False
  • .FillAdjacentFormulas False
  • .PreserveFormatting True
  • .RefreshOnFileOpen False
  • .RefreshStyle xlInsertDeleteCells
  • .SavePassword False
  • .SaveData True
  • .AdjustColumnWidth True
  • .RefreshPeriod 0
  • .TextFilePromptOnRefresh False
  • .TextFilePlatform 437
  • .TextFileStartRow 1
  • .TextFileParseType xlDelimited
  • .TextFileTextQualifier
    xlTextQualifierDoubleQuote
  • .TextFileConsecutiveDelimiter False
  • .TextFileTabDelimiter True
  • .TextFileSemicolonDelimiter False
  • .TextFileCommaDelimiter True
  • .TextFileSpaceDelimiter False
  • .TextFileColumnDataTypes Array(1, 4, 3,
    9, 1)
  • .TextFileTrailingMinusNumbers True
  • .Refresh BackgroundQueryFalse
  • End With

12
Importing ODBC vs OLEDB
  • Import External Data OLEDB
  • New database query ODBC
  • Web query import of HTML tables from web sites
  • More options added in 2007
  • SQL Server direct
  • XML
  • Access
  • Analysis services

13
Connections dialog in Excel 2007
  • This is a major improvement
  • You can see all available Existing connections in
    a single place
  • You can filter local, network and file based
    connections in one dialog
  • This helps you reuse any past connections quickly
  • Demo

14
Deploying connections across an organization
  • You can store Excel files in a special library
  • Connection library in SharePoint
  • Users can point to a SPS site and ask for
    connection information
  • This ELIMINATES the need to deploy / send query
    files to multiple desktops
  • It also provides centralized access control

15
Capturing data within Excel
  • Very commonly used by end users
  • Creates lots of problems
  • Accuracy, validation, structure is missing
  • Users send files to each other creating confusion
    and multiple copies

16
Native features of Excel for capturing data
  • Linear data to auto-form Demo (Ccard.xls)
  • Validations Demo
  • Forms Control Toolbox controls
  • User based editing Demo

17
Understanding the importance of Excel List
  • We work on blocks of data in Excel
  • When more data is added, formulas DO NOT refresh
    automatically
  • This can lead to lot of errors
  • List corrects that problem
  • In addition, it integrates with SPS
  • In 2003, it is two way sync
  • In 2007, it is one way (from SPS to Excel)
  • Demo

18
Capturing data in SPS list
  • Better alternative
  • Keeps data central
  • Users can update individually
  • Shows only their own data
  • No Manual consolidation required
  • DEMO

19
Exporting data
  • Supported formats
  • Delimited
  • XML spreadsheet
  • XML
  • Sounds like a limitation?
  • How do you pick up data from Excel and put it
    directly into your database?
  • How do you use it with ETL tools like DTS?

20
Excel as ODBC source
  • Excel ODBC driver has been available for years
  • Create a named range
  • Create ODBC source
  • The Excel file is considered the database
  • And Named ranges become tables
  • Now it can be used in any tool which supports
    ODBC. Including Excel!
  • DEMO

21
Report generation using Excel
  • Connect to data source
  • Process the data
  • Analyze data
  • Render as report
  • Finally let user manipulate the read only
    snapshot of report in Excel

22
Report generation options
  • Raw data with formulas and custom formatting
  • You make the report yourself by adding data from
    a recordset into Excel
  • Use SQL Reporting services to render it as Excel
  • Pivot table Demo
  • Create a base view of report
  • Let users do further analysis in a flexible way

23
Eliminating the row limit of Excel
  • The 65000 limit of Excel is a misconception
  • There is no need to get data in Excel
  • Most want to do analysis using Pivot Table
  • Pivot table can be created DIRECTLY
  • In this case, there is no practical limit on the
    number of rows
  • The Pivot Cache is available OFFLINE
    automatically.
  • Pivot drag drop does NOT fire database queries
  • Only REFRESH button fires database level re-query

24
Pivot Table and dashboards
  • Multiple copies of Pivot tables showing different
    views of data
  • Multiple pivot tables from different sources
  • Pivot Tables and Pivot Graphs combined

25
Importance of learning Pivot Table
  • Extremely powerful
  • Eliminates complex SQL code
  • Users can change format anytime eliminating
    further custom code
  • Data connection libraries AD security pivot
    table is a very secure, zero code solution for
    reporting

26
GetPivotData function
  • Initially it is confusing
  • For Pivot based calculations outside the table
  • Very useful when you are managing Pivot Tales
    programmatically
  • In this case you cant see the Row, Column
    coordinates to put formulas
  • How to use it? Demo

27
OLAP reporting in Excel
  • 2003 was primitive support
  • 2007 provides better support
  • Many more cube functions added
  • The User interface is native and fast
  • Office web components are also more responsive
    even if the data is large

28
Consolidation
  • Very powerful when data comes with Row and Column
    headings (Cross tab format)
  • Can be used programmatically
  • Saves lots and lots of coding
  • Results in Pivot Table Demo

29
XML
  • XML based, open, Excel file format
  • Server side data crunching without Excel possible
  • For Excel functions, use Excel Services
  • Custom schema based editing, validation and
    import export
  • Custom actions and element sensitive Task Pane UI
    using .NET code attached to XML schema (Smart
    Documents)

30
Finally Excel Services based reporting and data
capture
  • Renders Excel data as pure web page in a secure
    manner
  • Create Excel sheet. Name required ranges.
  • Post it in SPS library
  • Configure library to use Excel services
  • Now users can view Excel as a simple web page
  • Interactivity is maintained
  • In-line editing is NOT possible
  • Specific, named cell values can be captured
  • Demo

31
Usage scenarios
  • Interactive
  • Programmatic

32
Interactive
  • Complex delimited text file imports
  • Consolidation

33
Programmatic
  • Data capture forms / surveys
  • Reporting from OLTP / OLAP with end user level
    report format editing capabilities
  • Dashboards
  • SQL reporting services render as Excel by
    default
  • SPS Lists based reporting
  • SPS List Business Data Catalog Excel
    reporting
  • Enterprise wide data connections in SPS lists
  • Office web component PivotTable to deliver
    reports on Web

34
Summary
  • Learn Excel first
  • There are many ways in which Excel can be used
    for data capture, processing and reporting
  • Map the application business needs to the
    appropriate data handling method
  • This will make users happy and will eliminate
    lots of complex code we write everyday

35
References
  • Old interface to new interface http//go.microsoft
    .com/?linkid5174798
  • Excel cell referencing (very useful while coding
    in VBA)http//www.expresscomputeronline.com/20021
    216/techspace1.shtml http//www.expresscomputeron
    line.com/20021223/techspace1.shtml
  • Excel Help!

36
Thank you
  • nitin_at_maestros.net
  • www.nitinparanjape.com/blog
  • Learn and Grow

37
Quiz
  • This is not a POLL
  • You have to send your answers to
     contact_at_erfolgcs.com
  • Format should be
  • 1-A, 2-B and so on

38
Question 1 What is the number of rows available
in Excel 2007?
  • 65365
  • 1.5 million
  • 1.1 million
  • 1.04 million

39
Question 2 In Office 2007, the data How does
the data synchronization between Excel 2007
Table and SPS 2007 List work?
  • Changes in SPS change data in Excel table
  • Changes made in Excel change data in SPS
  • Changes can be bi-directional
  • No changes possible. It is a snapshot

40
Question 3Technology used to expose business
data in external databases in SharePoint 2007 is
called
  • Excel Services
  • Business Data Catalog
  • Data Connection Library
  • None of the above

41
Question 4Which type of data Excel CAN NOT
import?
  • DBF
  • SYLK
  • Visio
  • MDB
Write a Comment
User Comments (0)
About PowerShow.com