DBF to Excel Sheet, without Excel, using ADO ?

[nageswaragunupudi]

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

Looks like you did not see my working code patiently and jumping at conclusions too fast.

While creating Excel Table, ADOX can not create more than 7 fields at a stretch and the field MARRIED is the 8th field.
That is the reason why I added the table after the 7th field and then continued to add other fields.
This is the important part of the code I posted, dealing with the limitation of 7 fields.

Code: Select all  Expand view

       if i == 7
            oCat:Tables:Append( oTable )
            oTable := oCat:Tables( cTable )
         endif
 

Please see my full working code again, posted earlier

Code: Select all  Expand view

function FW_ADOX_CreateExcelTable( oCn, cTable, aStruct )

   local oCat  := CreateObject( "ADOX.Catalog" )
   local oTable, aFld, n, cType
   local oCol, oCol2
   local nType, nLen

   oCat:ActiveConnection   := oCn

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

   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
      > )
return nil
 

Kindly note that whatever code I post in the forums, is only after fully testing at my end. It has to work 100% if used exactly as I posted.

[nageswaragunupudi]

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

Time and again I was saying that this is the behavior or ADO with Excel.
You keep pointing out as if it is a defect with FWH functions.

Not at all please.

Please run this program as it is without any changes:

Code: Select all  Expand view

#include "fivewin.ch"
#include "adodef.ch"

function Main()

   local cTable, oCn, cStr, oRs
   local oCat, oTable, oCol
   local lHeaders := .t.
   local aTypes, n

   SET DATE GERMAN
   SET CENTURY ON

   cTable   := "limits"
   FERASE( cTable + ".xlsb" )

   cStr     :=  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ;
                cTable + ;
                ';Extended Properties="Excel 12.0;HDR=' + ;
                IF( lHeaders, 'Yes";', 'No";' )

   oCn   := FW_OpenAdoConnection( cStr )

   oCat  := CreateObject( "ADOX.Catalog" )
   oCat:ActiveConnection := oCn

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

   WITH OBJECT oTable
      :Columns:Append( "fldint",  adInteger )
      :Columns:Append( "fldtext", adVarWChar, 20 )
      :Columns:Append( "fldbool", adBoolean )
      :Columns:Append( "fldDate", adDate )

   END
   oCat:Tables:Append( oTable )

   ? "created"

   oRs   := FW_OpenRecordSet( oCn, "select * from " + cTable )
   oRs:AddNew( { "fldint", "fldtext", "fldbool", "fldDate" }, ;
               { 99, "FiveWin", .t., hb_DateTime() } )
   oRs:MoveFirst()

   aTypes   := {}
   for n := 0 to oRs:Fields:Count - 1
      WITH OBJECT oRs:Fields( n )
         AAdd( aTypes, { :Name, :Value, :Type, cAdoType( :Type ), ;
               :DefinedSize, :Precision, :NumericScale } )
      END
   next
   XBROWSER aTypes TITLE "ADO FIELD TYPES" SETUP ;
      oBrw:cHeaders := { "Field","Value","Type","cType","DefinedSize","Precision","NumericScale" }

   XBROWSER oRs

   oRs:Close()
   oCn:Close()

return nil

static function cAdoType( n )
return FW_Decode( n, 5, "adDouble", 202, "adVarWChar", 11, "adBoolean", 7, "adDate", "???" )
 

Please see the above screen-shot.
All this information about the field attributes like Type, DefinedSize, etc are given by the ADO of Microsoft, not at all by any FWH function.

Though we defined the type as adInteger it is returned as adDouble and though we speicified the size of adVarWChar field as 30, ADO returns the oField:DefinedSize to be 255 not 30.

Also adDate accepts both Dates and DateTime values.

We need to understand the behavior of ADO with Excel tables, which is different from other datasources, and work accordingly.
This is what I have been saying repeatedly many times.
Had you done some tests at your end, you would have noticed this yourself.

i like to REQUEST to enhance function FWAdoFieldStruct() to use Value of
Quote:
oField:DefinedSize

when use Excel and o:DefinedSize is valid

FWAdoFieldStruct() already and always uses oField:DefinedSize as reported by ADO.
This proves that there is absolutely no need to revise the existing FWH function.

The above sample also proves that using .T. for boolean fields work with oRs:AddNew() and will work the same way with oRs:Update() too.
I said this before and you were not agreeing. I assert again that it works. Please test and comment.

[Jimmy]

hi,

While creating Excel Table, ADOX can not create more than 7 fields at a stretch and
the field MARRIED is the 8th field.

where do you read it about 7st Element

as i can say your CODE is wrong.
you need a "fresh" ADOC.Column for every Column

Code: Select all  Expand view

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

   oCat:ActiveConnection := oCn

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

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

      cName := aStruct[ i ] [ DBS_NAME ]
      DO CASE
         CASE aStruct[ i ] [ DBS_TYPE ] == "C"   ; nType := adVarWChar
         CASE aStruct[ i ] [ DBS_TYPE ] $ "DT=@" ; nType := adDate
         CASE aStruct[ i ] [ DBS_TYPE ] == "L"   ; nType := adBoolean
         CASE aStruct[ i ] [ DBS_TYPE ] $ "+N"   ; nType := adDouble
      ENDCASE
      nLen := aStruct[ i ] [ DBS_LEN ]

      ocol:Name := cName
      ocol:Type := nType
      IF aStruct[ i ] [ DBS_TYPE ] == "C"
         ocol:DefinedSize := nLen
      ENDIF
      ocol:ParentCatalog := oCat

      oTable:Columns:Append( ocol )

      oCol := NIL
   NEXT
   oCat:Tables:Append( oTable )
   oTable := oCat:Tables( cTable )
RETURN NIL

[Jimmy]

hi,

You keep pointing out as if it is a defect with FWH functions.

i do not say it is a "Defect" but it is missing so that Fivewin Function can NOT work with Excel "this Way" yet

when debug CODE i saw often that "cDbms" is empty so it can not get Value and use "default"
it happens when use these FW Function which confuse me

---

i can not say what will be when all Function "know" Excel as i wish
i have not used it this Way before and most People thought it is not possible ...

but you are right about limitation of Excel as it is not a Database System
thx for your help

[nageswaragunupudi]

I am not able to write or read images to/from excel files using ADO.
Is it possible?

[Jimmy]

hi,

I am not able to write or read images to/from excel files using ADO.
Is it possible?

have not try ADO Type adTypeBinary yet

---

have found Sample which "read"

Code: Select all  Expand view

  objStream := CreateObject( "ADODB.Stream" )
   objStream:Open()
   objStream:Type := 1   //adTypeBinary
   objStream:LoadFromFile( cFile )
   aStream := objStream:Read()
   objStream:Close()
 

and "write"

Code: Select all  Expand view

  objStream:Open()
   objStream:Type := 1   //adTypeBinary
   objStream:Write( aStream )
   objStream:SaveToFile( cFile + ".bak" )
   objStream:Close()

---

i have read : when use Type adTypeBinary

use Method Read() instead() of ReadText()
same with Write() instead() of WriteText()