Data Integration in ExcelDr. Nitin ParanjapeMVP (Office System)Chairman and MD, Maestros

Version informationExcel 2007 for all demosMany features exist in 2003 as wellKeep Excel open to understand betterBut, dont try to do what I am showingUsing Beta versionComplicated process of broadcasting voicePlease bear with any crashes, disconnections

ObjectivesUnderstand available integration optionsMapping business scenarios to available featuresUnderstanding strengths and limitations of data handling

POLLHow many of you have used Excel for data handling in your applications?

Data integration areasData capture within ExcelGetting external data into ExcelAccessing Excel data from outsideBy Exporting it to another formatBy keeping it within Excel Processing data within ExcelUsing Excel as a report writer

Primary purpose of spreadsheet is to analyze data. NOT to store data.

Data sourcesODBC / OLEDBSPS listXMLOLAPTextQuery files

Import toolsWizard (only ODBC)MS Query (only ODBC)Text importText import VBA codeData connection wizard

Query files and connectionsThis facility has been available for yearsIt works on ODBC sources onlyUses either a wizard or MS QueryDemo (Wizard and MS Query)

Text ImportHighly complex text import possibleFixed width or DelimitedMultiple delimiters supportedDecide data types while importingManage multiple delimitersManage preceding negative signs Demo

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

Importing ODBC vs OLEDBImport External Data = OLEDBNew database query = ODBCWeb query = import of HTML tables from web sitesMore options added in 2007SQL Server directXMLAccess Analysis services

Connections dialog in Excel 2007This is a major improvementYou can see all available Existing connections in a single placeYou can filter local, network and file based connections in one dialogThis helps you reuse any past connections quicklyDemo

Deploying connections across an organizationYou can store Excel files in a special libraryConnection library in SharePointUsers can point to a SPS site and ask for connection informationThis ELIMINATES the need to deploy / send query files to multiple desktopsIt also provides centralized access control

Capturing data within ExcelVery commonly used by end usersCreates lots of problems Accuracy, validation, structure is missingUsers send files to each other creating confusion and multiple copies

Native features of Excel for capturing dataLinear data to auto-form : Demo (Ccard.xls)Validations : DemoForms & Control Toolbox controlsUser based editing : Demo

Understanding the importance of Excel ListWe work on blocks of data in ExcelWhen more data is added, formulas DO NOT refresh automaticallyThis can lead to lot of errorsList corrects that problemIn addition, it integrates with SPSIn 2003, it is two way syncIn 2007, it is one way (from SPS to Excel)Demo

Capturing data in SPS listBetter alternative Keeps data centralUsers can update individuallyShows only their own dataNo Manual consolidation requiredDEMO

Exporting dataSupported formatsDelimitedXML spreadsheetXMLSounds 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?

Excel as ODBC sourceExcel ODBC driver has been available for yearsCreate a named rangeCreate ODBC sourceThe Excel file is considered the databaseAnd Named ranges become tablesNow it can be used in any tool which supports ODBC. Including Excel!DEMO

Report generation using Excel Connect to data sourceProcess the dataAnalyze dataRender as reportFinally let user manipulate the read only snapshot of report in Excel

Report generation optionsRaw data with formulas and custom formattingYou make the report yourself by adding data from a recordset into ExcelUse SQL Reporting services to render it as ExcelPivot table : DemoCreate a base view of reportLet users do further analysis in a flexible way

Eliminating the row limit of ExcelThe 65000+ limit of Excel is a misconceptionThere is no need to get data in ExcelMost want to do analysis using Pivot TablePivot table can be created DIRECTLYIn this case, there is no practical limit on the number of rowsThe Pivot Cache is available OFFLINE automatically. Pivot drag drop does NOT fire database queriesOnly REFRESH button fires database level re-query

Pivot Table and dashboardsMultiple copies of Pivot tables showing different views of dataMultiple pivot tables from different sourcesPivot Tables and Pivot Graphs combined

Importance of learning Pivot TableExtremely powerfulEliminates complex SQL codeUsers can change format anytime eliminating further custom codeData connection libraries + AD security + pivot table is a very secure, zero code solution for reporting

GetPivotData functionInitially it is confusingFor Pivot based calculations outside the tableVery useful when you are managing Pivot Tales programmaticallyIn this case you cant see the Row, Column coordinates to put formulasHow to use it?: Demo

OLAP reporting in Excel2003 was primitive support2007 provides better supportMany more cube functions addedThe User interface is native and fastOffice web components are also more responsive even if the data is large

ConsolidationVery powerful when data comes with Row and Column headings (Cross tab format)Can be used programmatically Saves lots and lots of codingResults in Pivot Table: Demo

XMLXML based, open, Excel file formatServer side data crunching without Excel possibleFor Excel functions, use Excel ServicesCustom schema based editing, validation and import exportCustom actions and element sensitive Task Pane UI using .NET code attached to XML schema (Smart Documents)

Finally Excel Services based reporting and data captureRenders Excel data as pure web page in a secure mannerCreate Excel sheet. Name required ranges.Post it in SPS libraryConfigure library to use Excel servicesNow users can view Excel as a simple web pageInteractivity is maintainedIn-line editing is NOT possibleSpecific, named cell values can be captured Demo

Usage scenarios InteractiveProgrammatic

InteractiveComplex delimited text file importsConsolidation

ProgrammaticData capture forms / surveysReporting from OLTP / OLAP with end user level report format editing capabilitiesDashboardsSQL reporting services render as Excel by defaultSPS Lists based reportingSPS List Business Data Catalog Excel reportingEnterprise wide data connections in SPS listsOffice web component PivotTable to deliver reports on Web

SummaryLearn Excel firstThere are many ways in which Excel can be used for data capture, processing and reportingMap the application business needs to the appropriate data handling methodThis will make users happy and will eliminate lots of complex code we write everyday

ReferencesOld interface to new interface http://go.microsoft.com/?linkid=5174798Excel cell referencing (very useful while coding in VBA) http://www.expresscomputeronline.com/20021216/techspace1.shtml http://www.expresscomputeronline.com/20021223/techspace1.shtml Excel Help!

Thank younitin@maestros.netwww.nitinparanjape.com/blog Learn and Grow

Quiz

This is not a POLLYou have to send your answers to contact@erfolgcs.comFormat should be 1-A, 2-B and so on

Question 1: What is the number of rows available in Excel 2007?653651.5 million1.1 million1.04 million

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 tableChanges made in Excel change data in SPSChanges can be bi-directionalNo changes possible. It is a snapshot

Question 3:Technology used to expose business data in external databases in SharePoint 2007 is called

Excel ServicesBusiness Data CatalogData Connection LibraryNone of the above

Question 4:Which type of data Excel CAN NOT import?

DBFSYLKVisioMDB