topic-14997

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Interesting problem
Posted: Mon Mar 23, 2009 06:29 PM

I have an interesting challenge that is not a FWH problem ... but the experts here can probably put me on the right track to solving it.

I have data on an Excel spreadsheet. I am converting it into a .dbf file within my program. Everything is fine except for one issue.

One column of the spreadsheet is treated as Numeric. The actual numeric values, on the spreadsheet, or right justified, but subdata in that column with alpha characters is left justified. So it looks something like this:

36AB
36CVD
36MMO
36SSD

I need to translate all of these values to strings. So, I read the value of the cell, and check its type. All of them come up with Numeric so I would want to convert them with a STR( ) function. The 36 translates fine, but the 36AB then throws an error because the STR( ) function can't convert the AB of the value.

Does anyone have experience with this type of problem ? Any thoughts on a solution ?

I cannot change the type of the column when it is provided. Its data generated from a large system and that is simply the way they do it.

Thanks for any ideas you can offer.

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

ukoenig

Posts: 4043

Joined: Wed Dec 19, 2007 06:40 PM

Re: Interesting problem
Posted: Mon Mar 23, 2009 07:33 PM

Hello Tim,

Maybe to use TRANSFORM() instead of STR().
TRANSFORM converts any Value to a formatted string.
I hope it solves You problem.

TRANSFORM(123456, "$999.999") // => $123,456
TRANSFORM("to upper", "@!") // => TO UPPER

Maybe another solution to test with VALTYPE( value )

Code (fw): Select all Collapse

IF VALTYPE( Value ) = N
   cValue := STR( Value )
ELSE
   cValue := Value
ENDIF

Regards
Uwe

Since 1995 ( the first release of FW 1.9 )

i work with FW.

If you have any questions about special functions, maybe i can help.

César E. Lozada

Posts: 128

Joined: Wed Oct 26, 2005 12:18 PM

Re: Interesting problem
Posted: Tue Mar 24, 2009 12:19 AM

Code (fw): Select all Collapse

xCell:=oExcel:Cell(i,j):Value
 IF ValType(xCell)="N"
    nValue:=xCell
 ELSEIF ValType(xCell)="C"
    xCell:=AllTrim(xCell)
    cValue:=""
    iPos:=1
    lDec:=.F.
    DO WHILE iPos<=Len(xCell) .and. xCell[iPos]$"0123456789"+if(iPos=1,"-","")+if(!lDec,".","")
      lDec:=!lDec .and. xCell[iPos]="."
      cValue+=xCell[iPos]
      iPos++
    ENDDO
    nValue:=Val(cValue)
ELSE
    nValue:=0
ENDIF

TimStone

Posts: 3022

Joined: Fri Oct 07, 2005 01:45 PM

Re: Interesting problem
Posted: Tue Mar 24, 2009 02:36 PM

Transform works ... sort of. Unfortunately I do a transform using "@!" and it translates a 36 to 36.00 of type C. At least I can work with that by parsing off the decimal and the 0's. Its just a bit annoying. The nice thing about a computer is we only have to write the code in once, and then it works forever !

You can't do a type check because it sees every value as a type N, including one like 36BDC.

Tim Stone
http://www.MasterLinkSoftware.com
http://www.autoshopwriter.com
timstone@masterlinksoftware.com
Using: FWH 23.10 with Harbour 3.2.0 / Microsoft Visual Studio Community 2022-24 32/64 bit

mmercado

Posts: 782

Joined: Wed Dec 19, 2007 07:50 AM

Re: Interesting problem
Posted: Tue Mar 24, 2009 03:23 PM

Hi Tim:

Try with: Str( Val( AllTrim( cValToChar( YourValue ) ) ) )

Regards.

Manuel Mercado

manuelmercado at prodigy dot net dot mx

FiveTech Support Forums

Continue the discussion