DBF to Excel Sheet, without Excel, using ADO ?

[karinha]

Mr. Karinha

Do you know where can we get the famous TExcel class?

I don't remember who sent me. See if it helps.

No recuerdo quién me envió. A ver si te ayuda.

UTILFIVE, download: take it easy, there are many folders about fivewin. Including TEXCEL.

https://mega.nz/file/5c8k0BLR#T7dwZBqIufsH_7fuzEmJjINtmWW-3FjiwxAAdtQFWmM

Texcel, download:

https://mega.nz/file/5c8k0BLR#T7dwZBqIufsH_7fuzEmJjINtmWW-3FjiwxAAdtQFWmM

Regards, saludos.

[nageswaragunupudi]

Mr. Karinha
Thanks

[nageswaragunupudi]

sorry i´m still a Newbie and do not know what Fivewin already have or can do

No problem at all. We understand and we are here to assist you.
SecToTime() and TString() are (x)Harbour functions, not FWH.
I thought as profuse user of HMG, you might be aware of them.
Its ok, we are here to provide support in every way

[karinha]

Mr. Karinha
Thanks

I located the source, who sent me the link, only GOD knows... hahahaha many thanks.

Localicé la fuente, quien me envió el link, solo DIOS lo sabe... jajajaja muchas gracias.

I ask for an example, they send me a "truck" hahahahaha

pido un ejemplo me mandan un "camión" jajajajajajajaja

https://app.box.com/s/lck9effuyn?page=1

Regards, saludos.

[nageswaragunupudi]

it is Quick & Dirty but it seems to work this Way

Excellent Mr. Jimmy.
Not dirty at all. This is very useful.
This creates ".XLSB" file, if not ".XLSX" file. That is quite good enough where Excel is not installed.
I did not know this before and this is a great learning for me and thank you.

Now.
For this quick test, you have used ACE.
We know ACE is to be installed by the user and is not installed by default.
So, I tried with Jet OLEDB. This created ".XLS" file. That is great too.

We can now try

Code: Select all  Expand view  RUN

TRY
   use ACE
CATCH
   TRY
      use Jet
   CATCH
       ? "FAIL"
   END
END

Now, even if Excel is not installed, we can create xlsb if ACE is installed or .xls otherwise.
This is very useful.

Now FWH has ExportToExcel functions and methods in XBrowse, RowSet class and direct functions.
All these functions/methods work only if Excel is installed.
We can now extend this export facility even if Excel is not installed.
We will work on this.

Again many thanks.

[nageswaragunupudi]

Modified FW_OpenADOExcelBook()
Now we can use this function with cTable, instead of using our own connection string.

[nageswaragunupudi]

FW_XLCreateTableSQL2

Can you please share with us what modifications did you make?

[Jimmy]

hi,

FW_XLCreateTableSQL2

Can you please share with us what modifications did you make?

FW_XLCreateTableSQL2() is the same as FW_XLCreateTableSQL()
i just have rename it while i had test both Version which you have show us

[Jimmy]

hi,

Modified FW_OpenADOExcelBook()
Now we can use this function with cTable, instead of using our own connection string.

GREAT, thx

[Jimmy]

about FW_ADOX_CreateExcelTable() :
i wonder that FW_ADOX_CreateExcelTable() crash at FIELD "MARRIED" which is not 1st or Last FIELD in DBF

FIELD "MARRIED" is Type "L", adBoolean but FUNCTION DateCheck() seems not to check for Type "L"

[ 1] = C MARRIED
[ 2] = N 11

in DBF i have .T. / .F. but under SQL it can be TRUE / FALSE or 1 / 0 ...

---

i have try to include ADOX again and it work so far ...

Code: Select all  Expand view  RUN

FUNCTION FW_ADOX_CreateExcelTable( oCn, cTable, aStruct )
LOCAL oCat   := CreateObject( "ADOX.Catalog" )
LOCAL oTable, aFld, n, cType
LOCAL oCol
LOCAL nType, nLen, i, iMax, cName

   oCat:ActiveConnection := oCn

   cTable := LOWER( cTable )
   oTable := CreateObject( "ADOX.Table" )
   oTable:Name := cTable

#ifdef Use_AEVAL                  // old CODE
   AEVAL( aStruct, < | aFld, i |
LOCAL nType := ;
           IF( aFld[ 2 ] == "C", adVarWChar, ;
           IF( aFld[ 2 ] $ "DT=@", adDate, ;
           IF( aFld[ 2 ] == "L", adBoolean, ;
           IF( aFld[ 2 ] $ "+N", adDouble, adLongVarWChar ) ) ) )

   oTable:Columns:Append( aFld[ 1 ], nType )

   IF i == 7
      oCat:Tables:Append( oTable )
      oTable := oCat:Tables( cTable )
   ENDIF

   RETURN nil
   > )

#else                              // new CODE

   iMax := LEN(aStruct)
   FOR i := 1 TO iMax
      oCol := CreateObject( "ADOX.Column" ) // need for every Column

      cName := aStruct[i][ 1 ]
      DO CASE
         CASE aStruct[i][ 2 ] == "C"
            nType := adVarWChar
         CASE aStruct[i][ 2 ] $ "DT=@"
            nType := adDate
         CASE aStruct[i][ 2 ] == "L"
            nType := adBoolean
         CASE aStruct[i][ 2 ] $ "+N"
            nType := adDouble
      ENDCASE
      nLen := aStruct[i][ 3 ]

fwlog i , cName, nType, nLen

      ocol:Name := cName
      ocol:Type := nType
      IF aStruct[i][ 2 ] == "C"
         ocol:DefinedSize := nLen
      ENDIF
      oTable:Columns:Append( ocol )

   NEXT

fwlog i , Var2char(oCat), Var2char(oTable), Var2char(oCat:Tables), Var2char(oTable:Columns)
*  IF i == 7
     oCat:Tables:Append( oTable )
     oTable := oCat:Tables( cTable )
*  ENDIF

#endif

RETURN nil

i can SET ocol:DefinedSize := nLen and oTable:Columns:Append( ocol ) but FWAdoStruct( oRs ) still give me 255

---

i´m not sure what cDbms := FW_RDBMSName( oRs:ActiveConnection, .f. ) will give with Excel

i like to REQUEST to enhance function FWAdoFieldStruct() to use Value of

oField:DefinedSize

when use Excel and o:DefinedSize is valid

[nageswaragunupudi]

Code: Select all  Expand view  RUN

in DBF i have .T. / .F. but under SQL it can be TRUE / FALSE or 1 / 0 ...
 

Assigning .T. is working for me here.

[nageswaragunupudi]

Whatever DefinedSize we specify while creating the table (via sql or adox), when the table is read using ADO, the field object is showing oField:DefinedSize as 255 only

[Enrico Maria Giordano]

This is not true:

Code: Select all  Expand view  RUN

#include "Fivewin.ch"
#include "Ado.ch"


FUNCTION MAIN()

    LOCAL cMdb := "MyTest.mdb"

    LOCAL cCns := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cMdb

    LOCAL oCt, oCn, oRs

    oCt = CREATEOBJECT( "ADOX.Catalog" )

    FERASE( cMdb )

    oCt:Create( cCns )

    oCn = CREATEOBJECT( "ADODB.Connection" )

    oCn:Open( cCns )

    oCn:Execute( "CREATE TABLE Test ( Test VARCHAR ( 30 ) )" )

    oCn:Close()

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    oRs:Open( "SELECT * FROM Test", cCns, adOpenForwardOnly, adLockOptimistic )

    oRs:AddNew()

    oRs:Update()

    ? oRs:Fields( "Test" ):DefinedSize

    oRs:Close()

    RETURN NIL

[Jimmy]

hi Enrico,

it is IMHO while "MSACCESS" is "known" but not "EXCEL" as Provider by Fivewin

that is why i ask to enhance FWAdoStruct() / FWAdoFieldStruct() / FW_RDBMSName() for

Code: Select all  Expand view  RUN

cDbms = "MS JET EXCEL 12.0"

and ACE Version

---

is it possible to "integrate" a Progressbar in MsgRun() when append Data

[nageswaragunupudi]

This is not true:

Yes, not true for MsAccess, MSSQL and ALL other RDBMS with ADO.
But true for Excel ADO recordset