********************************
*!* Simple Sample Usage
********************************
*!* DIMENSION aWrkSht(1), aCols(1)
*!* m.lcXlsFile = GETFILE("Excel:XLS,XLSX,XLSB,XLSM")
*!* IF FILE(m.lcXlsFile)
*!* CLEAR
*!* ?AWorkSheets(@aWrkSht,m.lcXlsFile,.T.)
*!* ?AWorkSheetColumns(@aCols,m.lcXlsFile,"Sheet1")
*!* AppendFromExcel(m.lcXlsFile, "Sheet1", "MyTable", "column1,column2,column3", "Recnum Is Not Null", "field1,field2,field3", "field1 > 14000")
*!* SELECT MyTable
*!* GO TOP IN "MyTable"
*!* BROWSE LAST NOWAIT
*!* ENDIF
*!* CopyToExcel("C:\Test.xlsx", "Sheet1", "MyTable") && try xls, xlsb, and xlsm as well
**********************************
FUNCTION AppendFromExcel(tcXLSFile, tcSheet, tvWorkarea, tcExcelFieldList, tcExcelWhereExpr, tcTableFieldList, tcTableForExpr, tlNoHeaderRow)
**********************************
* PARAMETER Information
* tcXLSFile := a string specifying an excel file (*.xls, *.xlsx, *.xlsm, *.xlsb) on disk
* tcSheet := a string specifying the name of a worksheet within the excel workbook (can also be a range Sheet1$A1:C20 for instance)
* tvWorkarea [optional] := the Alias, Work Area, or File Name of the table you want the worksheet result set appended to (default is currently selected Alias)
* tcExcelFieldList [optional] := a comma delimited list of columns you want from the worksheet (default is '*' - all columns)
* tcExcelWhereExpr [optional] := a valid SQL Where clause to be used when querying the worksheet (default is '1=1')
* tcTableFieldList [optional] := a comma delimited list of fields you want the worksheet result set inserted into (default is '*' - all fields)
* tcTableForExpr [optional] := a valid VFP Where clause to be used when querying the worksheet result set (cursor) (default is '.T.')
* tlNoHeaderRow [optional] := pass .T. if the worksheet does not contain a header row, .F. is the default which specifies that a header row does exist
*
* RETURN Information
* returns numeric, the number of records inserted into tvWorkArea
*
* Provider Information
* the default provider being used in the SQLStringConnect function can be downloaded and installed from:
* [URL unfurl="true"]http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en[/URL]
**********************************
LOCAL lnSelect, laErr[1], laTableFields[1], laExcelFields[1], lnFieldCounter, ;
lcSQLAlias, lnResult, lcInsertValues, lcFieldList, lcNvlFieldList, ;
lcFieldType, lcExcelFieldType, lcNvlFieldName, lcTempAlias, loExc, lnReturn, ;
lcHeaderRow, llOpenedtvWorkArea, loException as Exception
lnSelect = SELECT(0)
lnReturn = 0
IF NOT FILE(m.tcXLSFile)
ERROR 1, m.tcXLSFile
ENDIF
IF !USED(m.tvWorkarea) AND TYPE("m.tvWorkArea") = "C" AND FILE(DEFAULTEXT(m.tvWorkarea,"DBF"))
SELECT 0
USE (DEFAULTEXT(m.tvWorkarea,"DBF")) SHARED AGAIN
tvWorkarea = ALIAS()
llOpenedtvWorkArea = .T.
ELSE
IF !USED(m.tvWorkarea)
tvWorkarea = ALIAS()
ENDIF
ENDIF
IF TYPE("m.tvWorkArea") = "N"
tvWorkArea = ALIAS(m.tvWorkArea)
ENDIF
tcSheet = ALLTRIM(EVL(m.tcSheet,"Sheet1$"))
IF AT("$",m.tcSheet) = 0
tcSheet = m.tcSheet + "$"
ENDIF
tcExcelFieldList = EVL(m.tcExcelFieldList,"*")
tcExcelWhereExpr = EVL(m.tcExcelWhereExpr,"1=1")
tcTableFieldList = EVL(m.tcTableFieldList,"*")
tcTableForExpr = EVL(m.tcTableForExpr,".T.")
lcSQLAlias = SYS(2015)
lcTempAlias = SYS(2015)
lnSQL = -1
lcHeaderRow = IIF(EMPTY(m.tlNoHeaderRow), "Yes", "No")
TRY
SELECT (m.tvWorkarea)
Local loConn as ADODB.Connection, loRS as ADODB.Recordset, loCursor as CursorAdapter
loConn = CreateObject("ADODB.Connection")
loConn.ConnectionString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source="] + m.tcXLSFile + [";Extended Properties="Excel 12.0;HDR=] + m.lcHeaderRow + [;";]
=CURSORSETPROP("FetchMemo",.t.,0)
try
loConn.Open()
If loConn.State = 1 && connection open
loRS = CreateObject("ADODB.Recordset")
loRS.ActiveConnection = loConn
loCursor = CreateObject("CursorAdapter")
loCursor.Alias = m.lcSQLAlias
loCursor.DataSourceType = "ADO"
loCursor.DataSource = loRS
loCursor.FetchMemo = .t.
loCursor.SelectCmd = "SELECT " + m.tcExcelFieldList + " FROM [" + m.tcSheet + "] Where " + m.tcExcelWhereExpr
lnSQL = 0
If !loCursor.CursorFill()
lnSQL = - 1
aerror(laErr)
*ERROR m.laErr[2]
oAppObj.write_log('ADODB Connection CursorFill method resulted in error for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + CHR(13) + ;
'Error message is: ' + m.laErr[2], PROGRAM(),'E')
EndIf
ELSE
lnSQL = - 1
oAppObj.write_log('ADODB Connection can not be opened for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + CHR(13) + ;
'Error message is: ' + m.laErr[2], PROGRAM(),'E')
EndIf
CATCH TO loException
lnSQL = - 1
oAppObj.write_log('ADODB Connection can not be opened for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + CHR(13) + ;
'Error message is: ' + loException.Message, PROGRAM(),'E')
endtry
if m.lnSQL < 0
lnSQL = sqlstringconnect([Provider=Microsoft.ACE.OLEDB.12.0;Data Source="] + m.tcXLSFile + [";Extended Properties="Excel 8.0;HDR=] + m.lcHeaderRow + [;";])
*!* Alternate using DSN that comes with Office install (MSDASQL = OLEDB wrapper for ODBC)
*!* m.lnSQL = SQLSTRINGCONNECT("Provider=MSDASQL.1;" ;
*!* +"Persist Security Info=False;" ;
*!* +"DSN=Excel Files;" ;
*!* +"DBQ="+FULLPATH(m.tcXLSFile)+";" ;
*!* +"DriverId=790;" ;
*!* +"MaxBufferSize=2048;" ;
*!* +"PageTimeout=5;")
*!* Try a few other drivers that may be on the user's machine
if m.lnSQL < 0
aerror(laErr)
oAppObj.write_log('Connection with Provider=Microsoft.ACE.OLEDB.12.0 is failing for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + chr(13) + ;
'Error message is: ' + m.laErr[2], program(),'E')
lnSQL = sqlstringconnect("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" ;
+ "DBQ="+fullpath(m.tcXLSFile)+";")
if m.lnSQL < 0 and upper(alltrim(justext(m.tcXLSFile))) == "XLS" && can we try using the older driver?
aerror(laErr)
oAppObj.write_log('Connection with Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)} is failing for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + chr(13) + ;
'Error message is: ' + m.laErr[2], program(),'E')
if m.lnSQL < 0
lnSQL = sqlstringconnect("Driver={Microsoft Excel Driver (*.xls)};" ;
+ "DBQ="+fullpath(m.tcXLSFile)+";")
endif
endif
if m.lnSQL < 0
aerror(m.laErr)
oAppObj.write_log('Connection with old Driver={Microsoft Excel Driver (*.xls)} is failing for ' + m.tcXLSFile + ' sheet ' + m.tcSheet + chr(13) + ;
'Error message is: ' + m.laErr[1,2], program(),'E')
error m.laErr[2]
endif
endif
endif
IF m.lnSQL >=0 AND NOT USED(m.lcSQLAlias)
lnResult = SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias)
IF m.lnResult < 0
AERROR(m.laErr)
ERROR m.laErr[2]
ENDIF
ENDIFlcConnString = [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\your\excel\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";]
loConn = CreateObject('ADODB.Connection')
loConn.Mode = 1 && adModeRead
loConn.Open( lcConnString )
loSchema = loConn.OpenSchema(20) && adSchemaTables
loDOM = CreateObject('MSXML.DOMDocument')
loSchema.Save(loDOM, 1)
Xmltocursor(loDOM.XML, "curTables")
loSchema.Close()
lcTable = Alltrim(curTables.Table_Name)
loRs = loConn.Execute(RTRIM(Textmerge("Select * From [<<lcTable>>]")))
loDOM = CreateObject('MSXML.DOMDocument')
loRS.Save(loDOM, 1)
Xmltocursor(loDOM.XML, "curExceldata")
loRS.Close()lo = CREATEOBJECT("excel.application")
lo.Workbooks.Open("c:\path\myfile.xlsx")
lnRow = 1
DO WHILE lnRow >= 1
FOR lnCol = 1 to FCOUNT('myTable')
lcCell = CHR(lnCol + ASC('A') - 1) + ALLTRIM(STR(lnRow,4,0))
lxValue = lo.Range(lcCell).Value
IF ISNULL(lxValue)
* No more data on this row
lnRow = 0
EXIT
ENDIF
* If we get here, there's data for this field
* We can either do an explicit replace,
* or build an insert command, populate an array (probably fastest),
* or whatever
NEXT
* Move to next row
lnRow = lnRow + 1
ENDDO
lo.Workbooks.Close()
lo.Quit()
* When we get here, we have our data stored to a table, or in
* an array, and it can be handled
