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