Importing Excel To a Dbf

[GeneB]

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

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   /////////////////////////////////////////////////////////////////

[D. Schuster]

Information

This is a very helpful function, thank's.
By importing data I had the following problem:
In the spreadsheet a column was defined as text, but the content of a cell could be a pure number e.g. 100,
in array aExcel appeared N 100.0000000 and in the dbf-file "100.0000000" !!!
(If the content of cell is 100.0 in array aExcel is correctly "100.0" !!)

By modifying the line xReturn := STR(xVar) to xReturn := LTRIM(STR(INT(xVar))) the result was correct.
Dieter

[skiman]

Hi,

Little suggestion to optimize:

Code: Select all

   FOR j:=1 TO LEN(aExcel[1])
      IF VALTYPE(aExcel[1,j]) <> "U"
         IF ( nElem := ascan( aFields, {|aSub| aSub[1]=aExcel[1,j] }) 
            xValue := ChangeVarType( aExcel[i,j], aFields[nElem,2] )
            dbffile->(fieldput(nElem,xValue))
         ENDIF
      ENDIF
   NEXT

[rdonnay]

Nice work. Thanks for that contribution.