Interesting problem

[TimStone]

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:

36
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.

[ukoenig]

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: Select all  Expand view

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

Regards
Uwe

[César E. Lozada]

Code: Select all  Expand view

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]

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.

[mmercado]

Hi Tim:

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

Regards.

Manuel Mercado