>********************************************************************** >* Program....: XLSXIMPORT.PRG >* Version....: >* Author.....: M.A. Savage >* Date.......: 16 December 09, 16:21:06 >* Notice.....: Copyright © 2009, Mike's Computing Service. >* : All Rights Reserved. >* Compiler...: Visual FoxPro 09.00.0000.2412 for Windows >* Abstract...: >* Changes....: Michael Savage, Created 16 December 09 / 16:21:06 >* Parameters.: >* called by..: >* Purpose....: Excel Importer >*********************************************************************** >*----------------------------------- >*- Original Author - Mods by M.A. Savage >* AUTHOR: Trevor Hancock >* CREATED: 02/15/08 04:55:31 PM >* ABSTRACT: Code demonstrates how to connect to >* and extract data from an Excel 2007 Workbook >* using the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" >* from the 2007 Office System Driver: Data Connectivity Components >*----------------------------------- >LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ; > lcSQLCmd AS STRING, lnSuccess AS INTEGER, ; > lcConnstr AS STRING >CLEAR > >lcXLBook = GETFILE('XLSX') && [C:\SampleWorkbook.xlsx] >IF EMPTY(lcXLBook) > =MESSAGEBOX("Must select an Excel File",16,"CANNOT PROCEED") > RETURN >ENDIF >lcConnstr = [Driver=] + ; > [{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ; > [DBQ=] + lcXLBook > >IF !FILE( lcXLBook ) > ? [Excel file not found] > RETURN .F. >ENDIF >*-- 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. Ergo, success >*-- is not checked here. Used FILE() instead. >lnSQLHand = SQLSTRINGCONNECT( lcConnstr ) > >*-- Connect successful if we are here. Extract data... >lcSQLCmd = [Select * FROM "Sheet1$"] >lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] ) >? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' ) >IF lnSuccess < 0 > LOCAL ARRAY laErr[1] > AERROR( laErr ) > ? laErr(3) > SQLDISCONNECT( lnSQLHand ) > RETURN .F. >ENDIF > >ON ERROR >SELECT xlResults >*- Save the results >SELECT * FROM xlResults INTO TABLE (JUSTSTEM(lcXLBook)+'.dbf') >SQLDISCONNECT( lnSQLHand ) > >DO xlxstrans WITH STRTRAN(JUSTSTEM(lcXLBook),' ','_') >*- MAS CLOSE TABLES all >*-----------------------------------------* >PROCEDURE xlxstrans > LPARAMETERS lcTableToFix > LOCAL lnCnt,lcFldName,lnFldCount,lcFirstChar,llIsChar > SELECT (lcTableToFix) > lnFldCount=FCOUNT(lcTableToFix) > FOR lnCnt=1 TO lnFldCount > lcFldName=FIELD[lnCnt] > WAIT WINDOW lcFldName NOWAIT > lcFirstChar=LEFT(lcFldName,1) > llIsChar=INLIST(lcFirstChar,'_','A','B','C','D','E','F','G','H','I','J','K','L','M','N') > IF llIsChar > *- Do Nothing - Valid Field Name - : TO DO - To check that no duplicte names exist > LOOP > ELSE > llIsChar=INLIST(lcFirstChar,'O','P','Q','R','S','T','U','V','W','X','Y','Z') > ENDIF > IF llIsChar > *- Do Nothing - Valid Field Name - : TO DO - To check that no duplicte names exist > ELSE > lcNewFldName=LEFT('_'+lcFldName,10) > * Here is where Iwant to change the field name - see below for more info > ALTER TABLE (lcTableToFix) RENAME COLUMN (lcFldName) TO (lcNewFldName) > ENDIF > ENDFOR >ENDPROC >>lcTableToFix is the table to fix