FiveTech Software tech support forums

www.FiveTechSoft.com
https://forums.fivetechsupport.com/

DBF to Excel Sheet, without Excel, using ADO ?

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=43572

Page 1 of 4

DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Tue Aug 08, 2023 6:37 am
by Jimmy
hi

METHOD ToExcel() need Excel
MsgAlert( FWString( "Excel not installed" ), FWString( "Alert" ) )

did Fivewin already have a Function to use ADO to "create" a XLSx Sheet :?:

---

Idea :!:

it seem function FW_OpenADOExcelBook( cFile, lHeaders ) return a "Connection" so after create Structure
Code: Select all  Expand view
  CREATE TABLE XXX ( ...


i want to do this
Code: Select all  Expand view
  oRs   := FW_OpenRecordSet( oCn, "XXX" )

   USE CUSTOMER NEW ALIAS CUST SHARED READONLY VIA 'DBFCDX'

   do while ! eof()
      oRs:AddNew( { "CUSTNAME", "MARRIED", "AGE", "SALARY" }, ;
                  { Left( Trim( CUST->FIRST ) + ' ' + Trim( CUST->LAST ), 30 ), ;
                    CUST->MARRIED, CUST->AGE, CUST->SALARY } )
      SKIP
   enddo

   CLOSE CUST
   oRs:Close()

will this work :?:

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Tue Aug 08, 2023 11:36 am
by Jimmy
hi,

does FW_AdoImportFromDBF() work when use
Code: Select all  Expand view
  cTable := LOWER( cFileNoExt( cDbf ) )

   oCon    := TOleAuto() :New( "ADODB.Connection" )
   oCon:Open( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ;
                   cTable + ';Extended Properties="Excel 12.0;HDR=' + ;
                   If( lHeaders, 'Yes";', 'No";' ) )

   FW_AdoImportFromDBF(oCon, cDbf, cTable, cColPrefix, nMultiRowSize, aFields, cAutoIncFld )

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Tue Aug 08, 2023 4:24 pm
by nageswaragunupudi
does FW_AdoImportFromDBF() work when use


Did you not try?

Well, this works:
Code: Select all  Expand view
function foo()

   local oCn, oRs

   oCn   := FW_OpenADOExcelBook( TrueName( "some.xlsx" ) )
   if oCn == nil
      ? "failed to open"
      return nil
   else
      if FW_AdoImportFromDBF( oCn, "states.dbf", , , 1, , .f. )
         oRs   := FW_OpenADOExcelSheet( oCn, "states" )
         XBROWSER oRs
         oRs:Close()
      else
         ? "failed to create new sheet"
      endif
      oCn:Close()
   endif

return nil

Values 1 and .f. for parameters 5 and 7 are important.

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Tue Aug 08, 2023 5:00 pm
by nageswaragunupudi
METHOD ToExcel() need Excel

Did you try on a PC, where Excel is not installed, if these two functions FW_OpenADOExcelBook(...) and FW_OpenADOExcelSheet(...) are working?
If they are not working and if you have access to a PC where Excel is not installed can you please do this test?
We are now using these command strings
Code: Select all  Expand view
        if lAce
            AAdd( aStr, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ;
                        cFile + ';Extended Properties="Excel 12.0;HDR=' + ;
                        If( lHeaders, 'Yes";', 'No";' ) )
            if cExt == "xlsm"
               aStr[ 1 ] := StrTran( aStr[ 1 ], "12.0;HDR", "12.0 Macro;HDR" )
            elseif cExt == "xlsx"
               aStr[ 1 ] := StrTran( aStr[ 1 ], "12.0;HDR", "12.0 Xml;HDR" )
            endif
         endif

         if cExt == "xls"
            AAdd( aStr, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
                        cFile + ';Extended Properties="Excel 8.0;HDR=' + ;
                        If( lHeaders, 'Yes";', 'No";' ) )
         endif
 

Can you try any alternative connection string to make it work on a PC, where Excel is not installed?

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 2:02 am
by Jimmy
hi,

thx for Answer
nageswaragunupudi wrote:Well, this works:

Values 1 and .f. for parameters 5 and 7 are important.

using your Sample i got
Image

---

than i create "some.xlsx" but with 0-Byte and i got "wrong Structure"
Image

---

so i try to create Structure of Table, using DbStruct(), but go next Error about Structure of Excel Table
Code: Select all  Expand view
  cQuery := "CREATE TABLE " + cTable + " ( "

   iMax := LEN( aDbfStruct )
   i = 1
   FOR i = 1 TO iMax
      cQuery += aDbfStruct[ i, DBS_NAME ]

      DO CASE
         CASE aDbfStruct[ i, DBS_TYPE ] = "C"
            cQuery += " character(" + ALLTRIM( STR( aDbfStruct[ i, DBS_LEN ] ) ) + "), "

         CASE aDbfStruct[ i, DBS_TYPE ] = "N"
            cQuery += " numeric(" + ALLTRIM( STR( aDbfStruct[ i, DBS_LEN ] ) ) + ',' + ALLTRIM( STR( aDbfStruct[ i, DBS_DEC ] ) ) + "), "

         CASE aDbfStruct[ i, DBS_TYPE ] = "D"
            cQuery += " date, "

         CASE aDbfStruct[ i, DBS_TYPE ] = "M"
            // IF lUseBlob = .T.
            //    cQuery += " bytea, "
            // ELSE
            cQuery += " text, "
            // ENDIF

         CASE aDbfStruct[ i, DBS_TYPE ] = "L"
            cQuery += " boolean, "

         CASE aDbfStruct[ i, DBS_TYPE ] = "V"
            // store as HEX String
            cQuery += " bytea, "
      ENDCASE
   NEXT
   cQuery += " )"

fwlog cQuery

   Try
      oCon:Execute( cQuery )
   Catch oError
      MsgInfo( oError:Description, cTable )
      Return .F.
   End try


---

than i open a existing *.XLSx and look at Structure
Code: Select all  Expand view
  objRS := FW_OpenADOExcelSheet( cPathcFile, cSheet, cRange, lHeaders )
   aStruct := FWAdoStruct( objRS )
fwlog var2char(aStruct)

and got
var2char(aStruct) = "{{ARTNR, C, 255, 0, 202, .T.},
{ARTIKEL, C, 255, 0, 202, .T.},
{VERPACKUNG, C, 255, 0, 202, .T.},
{EINHEIT, C, 255, 0, 202, .T.},
{APREIS, N, 17, 2, 5, .T.},
{BESTAND, N, 17, 2, 5, .T.},
{DATLETZAB, C, 255, 0, 202, .T.},
{WARENGRUPE, N, 17, 2, 5, .T.},
{MWST, N, 17, 2, 5, .T.},
{VKGESAMT, N, 17, 2, 5, .T.},
{EPREIS, N, 17, 2, 5, .T.},
{DATLETZZU, C, 255, 0, 202, .T.},
{KKPREIS, N, 17, 2, 5, .T.},
{STEINH, C, 255, 0, 202, .T.},
{STSTUECK, N, 17, 2, 5, .T.},
{STPREIS, N, 17, 2, 5, .T.},
{CODE, C, 255, 0, 202, .T.},
{GEWICHT, N, 17, 2, 5, .T.},
{ORGBESTAND, N, 17, 2, 5, .T.},
{DIFF, N, 17, 2, 5, .T.},
{WERT, N, 17, 2, 5, .T.},
{NEUBESTAND, N, 17, 2, 5, .T.},
{BPREIS1, N, 17, 2, 5, .T.},
{BPREIS2, N, 17, 2, 5, .T.},
{BPREIS3, N, 17, 2, 5, .T.},
{LAGEREIN, N, 17, 2, 5, .T.},
{LAGERAUS, N, 17, 2, 5, .T.},
{KTKGEGAL, L, 1, 0, 11, .T.},
{LASTREF, C, 255, 0, 202, .T.},
{CHINAART, C, 255, 0, 202, .T.},
{MINDEST, N, 17, 2, 5, .T.},
{AUFMONATE, N, 17, 2, 5, .T.},
{LASTPLATZ, C, 255, 0, 202, .T.},
{LASTMHD, D, 8, 0, 7, .T.}}"

it have 5th Element ??? and 6th Element .T. :shock:

---

where does 202 = "C", 5 = "N", 11 = "L" and 7 = "D" come from :?:
how can i "add" Column to DbStruct() to get FWAdoStruct() Format :?:

so how to create Table to use with Excel :?:

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 3:38 am
by Jimmy
hi,

found
Code: Select all  Expand view
#define adDouble                        5
#define adDate                          7
#define adBoolean                       11
#define adVarWChar                      202

but how to use these Constant to create Table for Excel ...

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 7:40 am
by nageswaragunupudi
using your Sample i got


Obviously the file should be an existing and valid xlsx file. Otherwise with what ADO connects?
If the file is a valid xlsx file, FW_AdoImportFromDBF() works.

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 7:46 am
by nageswaragunupudi
Please try using FWH built-in function and save your time.
Code: Select all  Expand view
FWAdoCreateTable( cTable, aStruct, oCn, .f. ) // --> lSuccess


Let us leave the drudgery of converting DBF structure to ADO structure to FWH, instead of wasting our time and energies.

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 7:51 am
by nageswaragunupudi
Notes:
1. I do not have access to PC without Excel installed. I like somebody to test if these functions work on such a PC.
I remember Mr. Vilian worked on it long before.

Mr. Vilian, If you see this post, can you share your experiences?

2. To the best of my knowledge we can not create a valid xlsx file on our own, without Excel. Or that may be very difficult.
I suggest creating one blank xlsx file and then copy it and use it to create our ado tables as sheets

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 9:35 am
by Marc Venken
I can test, but have you a version that can be compiled in samples ?

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 9:45 am
by Marc Venken
nageswaragunupudi wrote:I suggest creating one blank xlsx file and then copy it and use it to create our ado tables as sheets


Interesting .... Maybe code inside the sample ?

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Wed Aug 09, 2023 10:20 am
by Jimmy
hi,

i have try Fivewin Function like
Code: Select all  Expand view
   FWAdoCreateTable( cTable, aStruct, oCn, .f. ) // --> lSuccess

but it is not for Excel
it have to with "Datatype" which Excel use
https://learn.microsoft.com/en-us/sql/odbc/microsoft/microsoft-excel-data-types?view=sql-server-ver16

---

i "think" i got it for Type "C","N","D" and "L"
Code: Select all  Expand view
  cQuery := "CREATE TABLE " + cTable + " ( "
   iMax := LEN( aDbfStruct )
   FOR i = 1 TO iMax
      cQuery += aDbfStruct[ i, DBS_NAME ]
      DO CASE
         CASE aDbfStruct[ i, DBS_TYPE ] = "C"
            cQuery += " VARCHAR(" + ALLTRIM( STR( aDbfStruct[ i, DBS_LEN ] ) ) + ") "
         CASE aDbfStruct[ i, DBS_TYPE ] = "N"
            IF aDbfStruct[ i, DBS_DEC ] = 0
               cQuery += " INT "
            ELSE
               cQuery += " DOUBLE "
            ENDIF
         CASE aDbfStruct[ i, DBS_TYPE ] = "D"
            cQuery += " DATE "
         CASE aDbfStruct[ i, DBS_TYPE ] = "L"
            cQuery += " BIT "
      ENDCASE

      IF  i <> iMax
         cQuery += ", "
      ENDIF
   NEXT
   cQuery += " )"

   Try
      oCon:Execute( cQuery )
   Catch oError
      MsgInfo( oError:Description, cTable )
      Return .F.
   End try

   oRs := FW_OpenRecordSet( oCon, cTable )
   do while ! eof()
      oRs:AddNew( aFields , GetRecordValue(aFields,aDbfStruct) )
      SKIP
   enddo
   oRs:Close()
 

cQuery = "CREATE TABLE testtype ( TEST_C VARCHAR(10) , TEST_N INT , TEST_D DATE , TEST_L BIT )"

---

instead of *.XLSX i got *.XLSB ... :?
Excel can load it but i´m not sure if "other" can use it

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Thu Aug 10, 2023 5:49 am
by Jimmy
hi,

have found other Constant which seem to work "better"
Code: Select all  Expand view
       Case adBoolean                                       // 11
            sSQL = sSQL & "LOGICAL"
        Case adDBTimeStamp, adDate, adDBDate, adDBTime      // 135, 7, 133, 134
            sSQL = sSQL & "DATETIME"
        Case adBigInt, adInteger, adSmallInt, adTinyInt, _  // 3, 2, 15
                adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt, _ // 21, 19, 18, 17
                adDouble, adSingle, _                       // 5, 4
                adDecimal, adNumeric, adCurrency            // 14, 131, 6
            sSQL = sSQL & "NUMBER"
        Case Else
            sSQL = sSQL & IIf(oFld.DefinedSize > 255 Or oFld.DefinedSize < 0, "MEMO", "TEXT")
        End Select

---

have import DBF ( 66 MB ) with 26 FIELDs into Excel *.XLSB under 32 Bit OS
cIn = "records in dbf: 351287"
cIn = "imported recs: 351287"
cIn = "Sec 00:07:17"
cIn = "Rec/Sec 803.08"

*.XLSB are only 20 MB :!: while *.XLSX are 53 MB

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Sat Aug 12, 2023 3:49 pm
by Jimmy
hi,

have found out that TDataRow():New() does not like Type DATETIME, only DATE

Re: DBF to Excel Sheet, without Excel, using ADO ?

PostPosted: Sat Aug 12, 2023 11:05 pm
by nageswaragunupudi
Jimmy wrote:hi,

have found out that TDataRow():New() does not like Type DATETIME, only DATE


Is it?

Test:
Code: Select all  Expand view
#include "fivewin.ch"

function Main()

   DBCREATE( "testx.dbf", { ;
      { "ID", "+", 4, 0 }, { "NAME", "C", 4, 0 }, ;
      { "DATE", "D", 8, 0 }, { "UPDT", "=", 8, 0 } }, ;
      "DBFCDX", .T., "TESTX" )

   FW_ArrayToDBF( { { "ABCD", DATE()-10 }, { "DEFG", DATE() } }, ;
                  "NAME,DATE" )

   GO TOP

   XEDIT()

return nil


Image

Still think so?
All times are UTC
Page 1 of 4
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/