1 / 41

Data Integration in Excel

Data Integration in Excel. Dr. Nitin Paranjape MVP (Office System) Chairman and MD, Maestros. Version information. Excel 2007 for all demos Many features exist in 2003 as well Keep Excel open to understand better But, don’t try to do what I am showing Using Beta version

kolton
Télécharger la présentation

Data Integration in Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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, don’t 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:="TEXT;C:\temp\test.txt", _ Destination:=Range("$A$1")) .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 BackgroundQuery:=False 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/?linkid=5174798 • Excel cell referencing (very useful while coding in VBA)http://www.expresscomputeronline.com/20021216/techspace1.shtmlhttp://www.expresscomputeronline.com/20021223/techspace1.shtml • Excel Help!

  36. Thank you nitin@maestros.net www.nitinparanjape.com/blog Learn and Grow

  37. Quiz • This is not a POLL • You have to send your answers to contact@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 3:Technology 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 4:Which type of data Excel CAN NOT import? • DBF • SYLK • Visio • MDB

More Related