>FUNCTION Excel2007Import( tcFile, tcItemAlias, tlStructureOnly ) > >LOCAL lnHandle, lcCmd, lnSuccess, lcConnStr, lcOldError, lcCursorName >LOCAL ARRAY laExcelError[1] > >lnSuccess = 0 > >* according to MS you cannot enclose the path in quotation marks for this connection string >IF LEFT( ALLTRIM( tcFile ), 1 ) == '"' > tcFile = STRTRAN( tcFile, '"', "" ) >ENDIF > >lcConnStr = "Driver= {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Provider=Microsoft.ACE.OLEDB.12.0;DBQ=" + tcFile + ";Extended Properties=Excel 12.0 Xml;HDR=YES" > >IF FILE( tcFile ) > > * Attempt a connection to the .XLSX WorkBook. > * NOTE: If the specified workbook is not found, > * it will be created by this driver! You cannot rely on a > * connection failure - it will never fail unless the xlsx driver is not on machine. > * So success is not checked here. Used FILE() instead. > > IF EMPTY( tcItemAlias ) > lcCursorName = "xlsxresult" > ELSE > lcCursorName = "c" + tcItemAlias > ENDIF > > lcOldError = ON( "ERROR" ) > llError = .f. > ON ERROR llError = .t. > > lnHandle = SQLSTRINGCONNECT( lcConnStr ) > > IF lnHandle > 0 > > * Connect successful if we are here. Extract data... > IF tlStructureOnly > lcCmd = "Select TOP 10 * FROM [Sheet1$]" && if a million records it is stupid to pull in all for a structure check > ELSE > lcCmd = "Select * FROM [Sheet1$]" > ENDIF > > USE IN SELECT( lcCursorName ) > > lnSuccess = SQLEXEC( lnHandle, lcCmd, lcCursorName ) > > IF llError OR lnSuccess < 0 > > AERROR( laExcelError ) > PostLog( laExcelError[3] ) > lcCursorName = "" > ENDIF > > ELSE > lcCursorName = "" > PostLog( "Unable to connect to file: " + tcFile ) > ENDIF > >RETURN lcCursorName >