Converting any Excel sheet to .DBF file

[arpan]

Hi,

I have a small project where database file needed and the records
comes from Excel sheet
Can any one help me to convert excel sheet to database file. I am now
using xharbour 1.1.0

Thanks

[Ella]

Hello,

Below is a solution to read in an .xls sheet in an array - in case of
huge sheets I think preallocating the array is also necessary.

Ella

function main()

local a, i, j, cItem
local cFile:="C:\CNVS\excel\cp.xls", cTemp:="C:\CNVS"
local cSheetName:="Sheet1"

a:=adoxls2array( cFile, cTemp, cSheetName )

for i:=1 to len( a )
cItem:=""
for j:=1 to len( a[i] )
cItem+=" "+xstr( a[i][j] )
next
? cItem
next

return NIL

function adoxls2array( cFile, cTemp, cSheetName )

local oConn, oComm, oRs, oErr
local i:=0, nCols, cConn, aRs:=array(0), aRows

if .t. // the header ( HDR ) option can be set to yes or no
cConn:="Provider=Microsoft.Jet.OLEDB.4.0;"
cConn+="Data Source="+cFile+";"
cConn+='Extended Properties="Excel 8.0;HDR=No;IMEX=1";'
else // this driver can't identify a "header" record
cConn :="Driver={Microsoft Excel Driver (*.xls)};"
cConn+="DriverId=790;"
cConn+="Dbq="+cFile+";"
cConn+="DefaultDir="+cTemp+";"
endif

try

oConn:=CreateObject( "ADODB.Connection" )
with object oConn
:ConnectionString:=cConn
:Open()
end
oRs:=oConn:Execute( "select * from ["+cSheetName+"$]" )

with object oRs
nCols:=:Fields:Count
aRows:=array( nCols )
do while !:EOF
for i:=0 to nCols-1
aRows[i+1]:=:Fields[i]:Value
next
aadd( aRs, aclone( aRows ) )
:MoveNext()
enddo
end

oRs:Close()
oConn:Close()
oRs:=NIL
oConn:=NIL

catch oErr
alert( "Error: " + oErr:Operation + " " + oErr:Description )
end

return ( aRs )

[Robert Campsmith]

Thanks Ella,

Do you have a definition for Function xStr( uVar ) ??

Thanks,
-- Robert

[Robert Campsmith]

Hello Ella,

I tried your magic black box.

Microsoft Excel sent me this message...
'TEXTX.DBF' cannot be accessed. The file may be read-only, or you
may be trying to access a read-only location. Or, the server the
document is stored on may not be responding.
Retry Cancel

The full path of the output filename is
s:\sturec\Adrian\Import\testx.dbf

Thanks,
-- Robert

[Robert Campsmith]

Hello Ella,

I got your routine to work, by cleverly passing it the correct
parameters, but my source spreadsheet which consisted of 189 rows
by 8 columns came out as 3 rows by 3 columns.

What part may I be missing?

Thanks,
-- Rwc

[Robert Campsmith]

Ella,

Kindly disregard previous three posts from me.

Your blackbox pops up an Alert box instantly with the message
"Error: EXECUTE S_OK" and the button Ok I took this to mean that
it executed without errors (and very quickly).

However, no exported .dbf file is created anywhere.

The function "adoxls2array()" is supposed to return an array, but
the array that is returned is zero length.

Maybe I do not understand the comment

// the header ( HDR ) option can be set to yes or no

Since .t. is always True, the first half of the IF statement
probably always executes. So I tried setting the .t. to .f. and
got the same result - no exported .dbf file.

Thanks,
-- Robert

[Ella]

Robert,

I'm learned with xstr(...) function in VxH, but you can use cstr(...)
instead.
The idea is to convert whatever cell content to a string, even if it's
undefined ( NIL )

Here cFile should be set as complete file name with absolute path of
an existing Excel sheet.

> >    local cFile:="C:\CNVS\excel\cp.xls", cTemp:="C:\CNVS"

And here cSheetName should be set to an existing sheet ib cFile ( see
the tabs in the Excel workbook )

> >    local cSheetName:="Sheet1"
>

Here the content of the sheet is read in a 2D array, and is the
programmer's option, what to do with the data ( maybe he'she wants to
filter the content, and to save certain columns, each converted to a
predefined datatype )

> >    a:=adoxls2array( cFile, cTemp, cSheetName )

>
> >    for i:=1 to len( a )
> >       cItem:=""
> >       for j:=1 to len( a[i] )
> >          cItem+="  "+xstr( a[i][j] )
> >       next
> >       ? cItem
> >    next
>
> > return NIL
>
> > function adoxls2array( cFile, cTemp, cSheetName )
>
> >    local oConn, oComm, oRs, oErr
> >    local i:=0, nCols, cConn, aRs:=array(0), aRows
>

Here are two possible alternatives for a connection string:
- in the IF branch there is a connection string via OLEDB
- in the ELSE branch there is a connection string via ODBC

Some Excel sheets do have "header line", others not.

After deciding which is more appropiate, the options might need to be
adjusted to the concrete environment.

> >    if .t.   // the header ( HDR ) option can be set to yes or no
> >       cConn:="Provider=Microsoft.Jet.OLEDB.4.0;"
> >       cConn+="Data Source="+cFile+";"
> >       cConn+='Extended Properties="Excel 8.0;HDR=No;IMEX=1";'
> >    else    // this driver can't identify a "header" record
> >       cConn :="Driver={Microsoft Excel Driver (*.xls)};"
> >       cConn+="DriverId=790;"
> >       cConn+="Dbq="+cFile+";"
> >       cConn+="DefaultDir="+cTemp+";"
> >    endif
>

Personally I hate Office, because there are always problems with
extracting the data, when migrating to a normal database.

Ella

[seandc]

Arpan:
Why not export the data from Excel in CSV format and read into dBase
format using APPEND FROM...DELIMITED WITH ","
Sean