character fields to numeric  
Author Message
kuuku





PostPosted: Visual FoxPro General, character fields to numeric Top

I have a table with some fields like as below which are characters. i want to convert them to numeric and store them in another table.

can someone assist me please

field_1 field_2 field_3

000000057770.00 000000057770.00 000000000000.00

000000154948.00 00000-279204.00 000000434152.00

000000069766.00 000000016928.00 000000052838.00




Visual FoxPro2  
 
 
CetinBasoz





PostPosted: Visual FoxPro General, character fields to numeric Top

Simple way would be:

SELECT * from myTable INTO TABLE newTable
ALTER TABLE newTable ;
alter COLUMN field_1 b(2) ;
alter COLUMN field_2 b(2) ;
alter COLUMN field_3 b(2)

However your data as I see it has some content questionable (ie: 00000-279204.00). You might need to do some processing based on its meaning. ie: If it meant -279204:

SELECT * from myTable INTO TABLE newTable
ALTER TABLE newTable ;
alter COLUMN field_1 b(2) ;
alter COLUMN field_2 b(2) ;
alter COLUMN field_3 b(2)

SELECT * from myTable INTO TABLE newTable

FOR ix=1 TO FCOUNT()
lcField = FIELD(m.ix)
replace ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
ENDFOR

ALTER TABLE newTable ;
alter COLUMN field_1 b(2) ;
alter COLUMN field_2 b(2) ;
alter COLUMN field_3 b(2)


 
 
kuuku





PostPosted: Visual FoxPro General, character fields to numeric Top

Thanks for the help but i still have a problem. The table was updated alright but the negative value were all replaced with zeros.

What do i do



 
 
CetinBasoz





PostPosted: Visual FoxPro General, character fields to numeric Top

Oh I thought you understood the code and corrected the typo:

SELECT * from myTable INTO TABLE newTable

FOR ix=1 TO FCOUNT()
lcField = FIELD(m.ix)
replace ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
ENDFOR

ALTER TABLE newTable ;
alter COLUMN field_1 b(2) ;
alter COLUMN field_2 b(2) ;
alter COLUMN field_3 b(2)


 
 
kuuku





PostPosted: Visual FoxPro General, character fields to numeric Top

Yes did it as stated but the negatitives showed as zeros. However i did it this way and it worked.

mtot = TRANSFORM(EVALUATE(field_1))
mnth = TRANSFORM(EVALUATE(field_2))
marr = TRANSFORM(EVALUATE(field_3))

then replaced the variables in amother field


REPLACE field_4 WITH val(mtot)
REPLACE field_5 WITH val(mnth)
REPLACE field_6 WITH val(marr)

i'm using vfp6




 
 
kuuku





PostPosted: Visual FoxPro General, character fields to numeric Top

I did this also and it worked. Just changed the FieldType , FieldWidth and the Precision as below

SELECT * from mytable INTO TABLE new_table

FOR ix=1 TO FCOUNT()
lcField = FIELD(m.ix)
REPLACE ALL (m.lcField) WITH TRANSFORM(EVALUATE(&lcField))
ENDFOR

ALTER TABLE new_table ;
alter COLUMN field_1 N(15 , 2);
alter COLUMN field_2 N(15 , 2) ;
alter COLUMN field_3 N(15 , 2)