data integration in excel

《data integration in excel》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、DataIntegrationinExcelDr.NitinParanjapeMVP(OfficeSystem)ChairmanandMD,MaestrosVersioninformation§Excel2007foralldemos§Manyfeaturesexistin2003aswell§KeepExcelopentounderstandbetter§But,don’ttrytodowhatIamshowing§UsingBetaversion§Complicatedprocessofbroad

2、castingvoice§Pleasebearwithanycrashes,disconnectionsObjectives§Understandavailableintegrationoptions§Mappingbusinessscenariostoavailablefeatures§UnderstandingstrengthsandlimitationsofdatahandlingPOLL§HowmanyofyouhaveusedExcelfordatahandlinginyourapplica

3、tions?Dataintegrationareas§DatacapturewithinExcel§GettingexternaldataintoExcel§AccessingExceldatafromoutside–ByExportingittoanotherformat–BykeepingitwithinExcel§ProcessingdatawithinExcel§UsingExcelasareportwriterPrimarypurposeofspreadsheetistoanalyzedat

4、a.NOTtostoredata.Datasources§ODBC/OLEDB§SPSlist§XML§OLAP§Text§QueryfilesImporttools§Wizard(onlyODBC)§MSQuery(onlyODBC)§Textimport§TextimportVBAcode§DataconnectionwizardQueryfilesandconnections§Thisfacilityhasbeenavailableforyears§ItworksonODBCsourcesonl

5、y§UseseitherawizardorMSQuery§Demo(WizardandMSQuery)TextImport§Highlycomplextextimportpossible§FixedwidthorDelimited§Multipledelimiterssupported§Decidedatatypeswhileimporting§Managemultipledelimiters§Manageprecedingnegativesigns§DemoTextimportprogrammati

6、cally!WithActiveSheet.QueryTables.Add(Connection:="TEXT;C:temptest.txt",_.TextFileParseType=xlDelimitedDestination:=Range("$A$1")).TextFileTextQualifier=.Name="test"xlTextQualifierDoubleQuote.FieldNames=True.TextFileConsecutiveDelimiter=False.RowNumbe

7、rs=False.TextFileTabDelimiter=True.FillAdjacentFormulas=False.TextFileSemicolonDelimiter=False.PreserveFormatting=True.TextFileCommaDelimiter=True.RefreshOnFileOpen=False.TextFileSpaceDelimiter=False.RefreshStyle=xlInsertDeleteCells.TextFileColumnDataTy

8、pes=Array(1,4,3,9,1).SavePassword=False.TextFileTrailingMinusNumbers=.SaveData=TrueTrue.AdjustColumnWidth=True.RefreshBackgroundQuery:=False.RefreshPeriod=0EndWith.TextFilePromptOnRefresh=False.TextFilePlatform=437.TextFileStartR