Importing Excel To a Dbf
Posted: Fri Jun 25, 2010 4:51 pm
I import price updates from Excel spreadseets into our software, and need more control over the data types that Excel2WorkArea() provides. Every spreadsheet is different from each manufacturer. I found that when importing a text cell containing all numerical characters, the data type transferred is numerical, but if the cell contains any alpha characters or symbols it transferrs as a character string, all from the same spreadsheet column.
This routine allows me to pass the excel spreadsheet name, the name of the dbf to create, and an array of field names and structure parameters to give more control over the dbf file created. The dbf created doesn't contain all of the useless columns that are always found on the donor spreadsheets.
Anyone is welcome to use or modify it. I hope some find it useful.
GeneB in Albuquerque
This routine allows me to pass the excel spreadsheet name, the name of the dbf to create, and an array of field names and structure parameters to give more control over the dbf file created. The dbf created doesn't contain all of the useless columns that are always found on the donor spreadsheets.
Anyone is welcome to use or modify it. I hope some find it useful.
GeneB in Albuquerque
Code: Select all
FUNCTION Excel2Dbf(cExcelFile,cDbfName,aFields) ////////////////////////////////////
// pass name of an existing excel file
// column in Excel file must have the same name as dbf field for data to transfer
//
// pass dbf file name to create
//
// pass an array of fields to construct the dbf file
//
// example: aFields := { {"Name", C, 30, 0} ;
// , {"Open", L, 1, 0} ;
// , {"Balance", N, 8, 2} ;
// , {"Date" , D, 8, 0} }
//
//
//
// sample array created by DC_Excel2WorkArea() :
//
// aExcel := { {"Name", "Open", "Balance" , "Date" } ;
// , {"Jones ", .T., 123.45 , 04/04/99 } ;
// , {"Anderson", .F., 0.00 , 01/01/08 } ;
// , {"Smith ", .T., 1000.00 , 03/03/09 } }
//
local i, j, aExcel, cField, xValue, nElem
// create dbf from aFields ----------
DbCreate( DC_CurPath() + "\" + cDbfName + ".dbf", aFields )
USE cDbfName ALIAS "dbfFile" EXCLUSIVE NEW
// import excel spreadsheet ----------
aExcel := DC_Excel2Array( DC_CurPath() + "\" + cExcelFile )
// transfer data from array to dbf ---------
FOR i:=2 TO LEN(aExcel) // step through elements in aExcel to transfer data
SELECT dbfFile
APPEND BLANK
FOR j:=1 TO LEN(aExcel[1])
IF VALTYPE(aExcel[1,j]) <> "U"
IF FIELDPOS(ALLTRIM(aExcel[1,j])) > 0
nElem := ArrayFind( aFields, aExcel[1,j] )
xValue := ChangeVarType( aExcel[i,j], aFields[nElem,2] )
cField := aExcel[1,j]
repl dbfFile->&cField WITH xValue
ENDIF
ENDIF
NEXT
NEXT
RETURN NIL //////////////////////////////////////////////////////////
STATIC FUNCTION ChangeVarType( xVar, cType ) ////////////////////////
// converts any variable to a specified variable type
local xReturn:=xVar
cType := UPPER(cType)
DO CASE
CASE VALTYPE(xVar)=="U"
IF cType=="C"
xReturn := " "
ELSEIF cType=="N"
xReturn := 0
ELSEIF cType=="D"
xReturn := CTOD(" / / ")
ELSEIF cType=="L"
xReturn := .F.
ENDIF
CASE VALTYPE(xVar)=="C"
IF cType=="N"
xReturn := VAL(xVar)
ELSEIF cType=="D"
xReturn := CTOD(xVar)
ELSEIF cType=="L"
xReturn := IF(xVar$"yY",.T.,.F.)
ENDIF
CASE VALTYPE(xVar)=="N"
IF cType=="C"
xReturn := STR(xVar)
ENDIF
CASE VALTYPE(xVar)=="D"
IF cType=="C"
xReturn := DTOC(xVar)
ENDIF
CASE VALTYPE(xVar)=="L"
IF cType=="C"
xReturn := IF(xVar,"Y","N")
ENDIF
ENDCASE
RETURN xReturn ////////////////////////////////////////////////////////////
STATIC FUNCTION ArrayFind( aFields, cStr ) ////////////////////////////////////
local i
cStr := ALLTRIM(UPPER(cStr))
FOR i:=1 TO LEN(aFields)
IF VALTYPE(aFields[i,1]) == "C"
IF ALLTRIM(UPPER(aFields[i,1])) == cStr
RETURN i
ENDIF
ENDIF
NEXT
RETURN 0 /////////////////////////////////////////////////////////////////