using Excel COM to read Sheet into Array -> DBF/SQL

Discuss anything else that does not suite other forums.

Moderator: Rathinagiri

Post Reply
User avatar
AUGE_OHR
Posts: 458
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 49 times
Been thanked: 92 times

using Excel COM to read Sheet into Array -> DBF/SQL

Post by AUGE_OHR » Thu Jan 09, 2020 3:46 am

hi,

Part1

i have prepare 1st Demo to show how to get a Excel Sheet using COM into Array and show it in GRID
it also include This.CellColIndex BUG which show Col+1 :shock:

Array get from Excel are Column so we need to convert it to Row :!:

Code: Select all

   iMax := LEN( aExcel )
   FOR i := 1 TO iMax
      AADD( aHead, aExcel[ i ] [ 1 ] )     
      AADD( aWidths, aWide[ i ] * 10 )    // twips ...
   NEXT

   iMax := LEN( aExcel )                  // Rows
   jMax := LEN( aExcel[ 1 ] )             // Columns
   j := 1
   FOR j := 1 TO jMax                     // every Column
      aLine := {}
      i := 1
      FOR i := 1 TO iMax                  // build Line
         IF NIL = aExcel[ i ] [ j ]       // if NIL -> ""
            aExcel[ i ] [ j ] := ""
         ENDIF
         AADD( aLine, aExcel[ i ] [ j ] ) // Line Array
      NEXT
      //  IF J <> 1                       // no Headerline
      AADD( aNew, ACLONE(aLine))
      //  ENDIF
   NEXT
   nEnde := LEN( aNew )
as you seen i create a new Array which i can "manipulate" later but have "Original" in Background.

now we got Data in GRID but it might be that not all belong to "Data" which we want to transfer.
Programmer "expect" that in 1st ROW are Header ... but what it 1st ROW contain "other" data ?
also End must not be last Row so we need nStart, nEndRow ... and nEndCol (click in Header to show BUG)

if we got the Area we still can have Problem with FIELDPUT() while VALTYPE() might incorrect e.g. NIL
not sure about International Version of Excel : does it use "," or "." for Decimal :?:
i have to figure out how to get "Nation" Sign e.g. for "empty" Date like " . . " or " / / "
Excel_Demo1.jpg
Excel_Demo1.jpg (66.86 KiB) Viewed 466 times
hb_EXCEL_Demo1.ZIP
(10.29 KiB) Downloaded 28 times
to be continue ...
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 458
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 49 times
Been thanked: 92 times

Post by AUGE_OHR » Sun Jan 12, 2020 6:36 am

Part2

i have change Layout and add more Button and Textbox.
it is need when Excel Sheet have "Different" Layout or Data Type.
Excel_Demo5.JPG
Excel_Demo5.JPG (54.83 KiB) Viewed 415 times
Excel can use all Type with "Standard" but it will got NIL if Excel can't recognize Type.
this happens e.g. when use a Numeric and use "." instead of "," (or "," instead of "." ?)

i avoid those Problem it is recommend to use

Code: Select all

   oSheet:Columns( i ) :NumberFormat := "#0,00"
   oSheet:Columns( i ) :NumberFormat := "TT.MM.JJJJ"
this might be a Nation Problem while i use a German Excel Version.
so please check it for your Language also Nation Date Format " . . " or " / / "

Question : are there any harbour / HMG Function for Nation Message :?:

---

as Layout a Programmer "expect" Header in 1st line and Data at 2nd Line.
but what if User have used "other" Layout and Header begin in other Line.
for this i include F5-F8 to "set" Line to work and F9 is same for Data Type

look at Sample 1st Data Row.
1st Column "seem" Type "N" but if you scroll down you will see A-Z so this Column ist Type "C"
Column 3 & 4 have NIL and it seems Type "N" but how long (LEN) and how many Decimal :?:
Excel_Demo3.JPG
Excel_Demo3.JPG (96.93 KiB) Viewed 415 times
if i use VAR2CHAR(Number) to get a String and count LEN() i got 6 but when scroll down you will see bigger Number so it is 7.
for LEN() of Type "C" i have try to use "wide" of Excel Column (look at "o:AutoFit()" ) but i'm not sure how to convert Twips :idea:


how to :
to use F5 - F9 first click on Button or use F-Key and than DblClick into "Data-Area" of GRID to mark line.

F8 does NOT work that Way while it try to get "last Column" (there can be Infomation right side which are no Data)
you have to click on Header of Column but This.CellColIndex have a BUG and give Col+1 so type it manuell if need

! Note : if you have used F5-F8 please press F9 again before press F4 else you got to

Code: Select all

Procedure CompareTypeFormat() 
which is only a Dummy which i want to explain in Part3

next Windows show Structure
Excel_Demo4.jpg
Excel_Demo4.jpg (82.38 KiB) Viewed 415 times
here you still can change Structure before start transfer but i do NOT check e.g. FIELD Name (A-Z, 0-9 and NO Number on Front)
i recommend to use F9 and use other line in "Data-Area" for Structure.

btw. ESC will close Window

here is the EXE
it is still under construction. Comments welcome
hb_EXCEL_EXE_1.ZIP
(1.27 MiB) Downloaded 20 times
will be continue ...
have fun
Jimmy

User avatar
bpd2000
Posts: 1117
Joined: Sat Sep 10, 2011 4:07 am
Location: India
Has thanked: 213 times
Been thanked: 114 times

Post by bpd2000 » Sun Jan 12, 2020 8:54 am

Nice creation, continue
After you finish Com version I will use your code to create tool w/o com
i.e. using .dll to read excel file
I am working on it and I will save my time to develop such tool
BPD
Convert Dream into Reality through HMG

User avatar
AUGE_OHR
Posts: 458
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 49 times
Been thanked: 92 times

Post by AUGE_OHR » Mon Jan 13, 2020 1:46 am

hi,

i found a Problem in last Version

Code: Select all

   BUTTON oBtTestType ;
      CAPTION "F9: Data Type" ;
      NOTRANSPARENT ;
      TOOLTIP "activate Row to test data type" ;
      ACTION { || WhatToDo( @cGoesTo, "TESTTYPE" ), GetDataType( @aData, @aType, aNew, nColEnd, nTestRowType ) }
as you can see i have 2 Function in Codeblock but only 1st Function will be called :shock:
so GetDataType() will never called again ... is this the normal harbour / HMG Way :?:
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 458
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 49 times
Been thanked: 92 times

Post by AUGE_OHR » Tue Jan 14, 2020 5:28 am

hi,

i was sure it have work before but neverless now i use it later an work like i want
also Problem to show Row Number i found a Solution. it is when you get a Error Msg and line is 951 in Array ...
! Tip most it is a "bigger" Numeric than your FIELD

press F9 and type in wanted Row or navigate to Row and DblClick or edit manual DBF Structure when show before transfer.

here EXE v0.16
hb_EXCEL_EXE_2.ZIP
(1.27 MiB) Downloaded 14 times
if all is fine i will prepare Source to release so please try if all work with "your" Excel Sheet.
have fun
Jimmy

User avatar
AUGE_OHR
Posts: 458
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 49 times
Been thanked: 92 times

Post by AUGE_OHR » Wed Jan 15, 2020 5:15 am

hi,

here now Source of Part 1 & 2
hb_DBFEXCEL_SOURCE17.ZIP
(18.08 KiB) Downloaded 22 times
now you got the Source to get a Excel Sheet into DBF / SQL but it only work with "optimal" Sheet.
i have got o:Numberformat for every Column from Excel but i only use it to show it on Header.

Part 3 is try to "automate" F5-F9 to find Start/Data/End and right Structure.

when have a bad Structure you will get a Error Message which point to Line in Row.
you have use that Row Number for "next try" to find a Row which match all.

when have o:Numberformat in some case it will "confirm" what get with VALTYPE()
... but it also can "confuse" you so the Question is what is right :roll:

so i don't want to release Part 3 "as Code" while there a too many Ways so we have to discuss if a Way will produce right Value or fail

Code: Select all

   DO CASE
      CASE ccType = NIL // Excel does not recognice Type
         // ---------- safe Type C ----------*
      CASE ccType = "@" .AND. cType = "C"
      CASE ccType = "@" // can have any in a String
         // ---------- safe Type N ----------*
      CASE ccType = "#0,00"
      CASE ccType = "#0.00"
      CASE ccType = "#0"
         // ---------- safe Type D ----------*
      CASE ccType = "TT.MM.JJJJ"  // German
      CASE ccType = "TT.MM.JJ"     // German
         // ---------- safe Type L ----------*
      CASE cType = "L"
         nLen := 1
         // ---------- Type other ------*
      CASE ccType = "Standard"  // most Type
i'm not sure if it make real Sence while it can be a very long list and many User do not format Column "manual".
there a many more o:Numberformat for Type "N" and Type "D" depend on Nation Setting.

to write Part 3 those Information are need so please post what "you" have in Header of GRID and what VALTYPE() say

hope you like the Code which i wrote with harbour / HMG and Help of User from this Forum, thx
have fun
Jimmy

User avatar
danielmaximiliano
Posts: 2193
Joined: Fri Apr 09, 2010 4:53 pm
Location: Argentina
Has thanked: 202 times
Been thanked: 34 times
Contact:

Post by danielmaximiliano » Wed Jan 15, 2020 5:48 pm

AUGE_OHR wrote:
Mon Jan 13, 2020 1:46 am
hi,

i found a Problem in last Version

Code: Select all

 ACTION { || WhatToDo( @cGoesTo, "TESTTYPE" ), GetDataType( @aData, @aType, aNew, nColEnd, nTestRowType ) }
as you can see i have 2 Function in Codeblock but only 1st Function will be called :shock:
so GetDataType() will never called again ... is this the normal harbour / HMG Way :?:
try

Code: Select all

 ON KEY F2		     OF Principal ACTION { {|| Fincom()}, {||Stock()  }}
I had the same problem and trying this way it worked
*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`.HMG : It's magic !
(¸.·``··*

Saludos / Regards
DaNiElMaXiMiLiAnO

Whatsapp. : +54901169026142
Telegram Name : DaNiElMaXiMiLiAnO

User avatar
dragancesu
Posts: 729
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 25 times
Been thanked: 164 times

Post by dragancesu » Wed Jan 15, 2020 6:43 pm

Maybe without ||
this work

ON KEY F8 ACTION ( FiltRec := .F., RecordStatus_3228(), DeleteRecord_3228(), Nil )

User avatar
AUGE_OHR
Posts: 458
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 49 times
Been thanked: 92 times

Post by AUGE_OHR » Wed Jan 15, 2020 11:53 pm

hi,

thx for Answer. i will check it again.

as you got the Source you can see where i place GetDataType() now which seems a better place.
have fun
Jimmy

Post Reply