How can one test for a 'numeric overflow' field 2

[1421]

Hi All

I am importing data from Paradox & am getting a 'numeric overflow' message in the process, I have told the user to 'ignore' it. I persume that the data is being imported & the numeric field that caused the 'numeric overflow' message is initialized to astericks. This is a table with over 200,000 records. Is there a way to set a filter so that I can find the field that has caused this problem, which i persume will contain astericks?

Thanx

 

[alvechurchdata]

Try something like this:
[TT]
Set Exact off
Brow for Str(myField)="*"
[/TT]

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Imaginecorp]

If you are doing this Programmatically look up Vartype(), Type(), Isalpha(), Isdigit() etc.

 

[Olaf Doschke]

to prevent error messages at import, set the error to silence, eg with ON ERROR *.

And Geoff has the easiest solution to find overflowed fields.

Bye, Olaf.

 

[baltman]

Also watch for lost decimals. Overflow error isn't thrown until the integer portion doesn't fit; decimals can be rounded to 'protect' the integer portion.

Look for len(trans(int(MyField)))>=FieldLen-Decimals where Decimals>0

Brian

 

[1421]

Hi All,

Bad news & good news. I tried the above-mentioned (not yet the vartype(),.. solution) and it didn't find this field. It did find a numeric overflow field that I had put in myself, for test purposes, so it does work.
The On Error *, did do the trick, as far as not showing an error, so that the process completes w/o the user 'panicking'.......thanx again!

 

[dbMark]

I've modified Geoff's solution. Try this:

Set Exact off
Browse for "*" $ STR(myField) && finds even one asterisk in field

 

[1421]

i used the AT() command which should accomplish the same as
"*" $ STR(myField)

 

[baltman]

Thank you for the star. Since there seems to be some interest (and I have access to it now), here's a procedure to test a table.

Brian

DataLossTest("C:\Data\MyTable.dbf")

PROCEDURE DataLossTest
	LPARAMETERS tcFullPathTargetTable
	LOCAL ARRAY aFieldTest[1]
	LOCAL i, cField, cFailList 
	m.cFailList =""

	IF SELECT(JUSTSTEM(m.tcFullPathTargetTable))=0
		USE (m.tcFullPathTargetTable) IN 0 ;
			ALIAS (JUSTSTEM(m.tcFullPathTargetTable))
	ENDIF 

	SELECT (JUSTSTEM(m.tcFullPathTargetTable))

	AFIELDS(aFieldTest)

	FOR m.i = 1 TO ALEN(aFieldTest,1)
	  IF aFieldTest[m.i, 2] = "N" AND aFieldTest[m.i, 4]>0
	    m.cField = aFieldTest[m.i, 1]

	    LOCATE FOR LEN(TRANSFORM(INT(&cField))) > ;
	      aFieldTest[ASCAN(aFieldTest,m.cField,1,ALEN(aFieldTest,1),1,2)+2]-;
	      aFieldTest[ASCAN(aFieldTest,m.cField,1,ALEN(aFieldTest,1),1,2)+3]-1
	      
	    IF !EOF()
	      m.cFailList = m.cFailList + m.cField +", "
	    ENDIF 
	  ENDIF 
	ENDFOR

	IF !EMPTY(m.cFailList)
	  m.cFailList = LEFT(m.cFailList, LEN(m.cFailList)-2)
	  
	  MESSAGEBOX("Data Loss possible in the following fields. " + ;
	  	"Please Fix Before Continuing." + ;
	    CHR(13) + CHR(10) + m.cFailList, 0 + 16)
	ENDIF 
ENDPROC

 

[1421]

I ran this procedure (DataLossTest), but it only checks for numeric fields with decimal's & I came back with nothing. The other numeric fields I used again the
at('*',str(numeric_data)) command & came up with nothing. Is it possible that when VFP runs into this problem it stops importing (despite my 'ignore' selection) or is the field populated with something other than astericks?

 

[dbMark]

Is it just a message being displayed as an alert that doesn't stop the program?

If so, could the problem be that the value being imported or appended is too small for the numeric field?

I've done some testing and I'm not sure now. At first I thought VFP might be placing a zero value in the fields, so you wouldn't find an error indication in the fields themselves. If that were so, then if none of the values should be zero, you could then look for records with zero amounts. That is the case if a field with more decimals is being imported into one with fewer decimals, but no error is triggered.

A scientific notation value is imported as is, but still without an error.

I suggest you search the numeric fields for values less than what would normally be possible. For example, if it has no decimals search for a ABS(value)*1000 less than 1000 but not less than -1. If the field allows decimals, you will have to increase the multiplication factor. If two digits, use 10000

SET FILTER TO ABS(myField) < 1  && field has no decimals
SET FILTER TO ABS(myField)*10 < 1  && field has 1 decimal
SET FILTER TO ABS(myField)*100 < 1  && field has 2 decimals such as dollars
SET FILTER TO ABS(myField)*1000 < 1  && field has 3 decimals
and so on...

Please let me know if I'm barking up the wrong tree...

(In trusty ancient dBase 5 for DOS, if the field is only expecting two decimals and the incoming value is less than 0.005 or if it was expecting no decimals and the incoming value is less than 0.05, that error message is triggered but doesn't halt the application. dBase saves such a too-small value in the field as text in scientific notation - if the field is large enough to hold the scientific notation expression - a real headache - but at least it leaves you something to find!)

 

[dbMark]

As a followup to my message a couple minutes ago, you may wonder why we don't do a search in the STR(myField) for "E-" like we can see visually when browsing such a field. Well, doing a search with a STR() function converts it from the scientific notation you can visually see. Really tough, so that's why I came up with the idea above. If you browse the table after importing a field which has scientific notation in it, you will also see that when you tab into the field it changes the display to "0", the lowest valid value to display.

Now I'm really curious as to whether I way off on a tangent or nailed it. If this is the problem, such encounters may not halt the program. I don't know if that is good or bad.

 

[baltman]

I think the issue is likely IMPORT FROM ... TYPE PDOX specific. I haven't imported that data type. I was trying to replicate with csv and was unable to do so. When I usually see that error with a REPLACE command.

If your DB is small you could open the files with Excel and SUM the columns and compare the results to the post import VFP SUMs.

STRTOFILE('"abc",123,456.78,'+REPLICATE("a",200),"testinput.csv")

CREATE TABLE bigenough (f1 c(3), f2 n(3,0), f3 n(6,2), f4 c(210))
APPEND FROM testinput.csv TYPE DELIMITED 
BROWSE NOWAIT 

CREATE TABLE notbigenough1 (f1 c(2), f2 n(2,0), f3 n(5,2), f4 c(20))
APPEND FROM testinput.csv TYPE DELIMITED 
BROWSE NOWAIT 

CREATE TABLE notbigenough2 (f1 c(2), f2 n(2,0), f3 n(4,2), f4 c(20))
APPEND FROM testinput.csv TYPE DELIMITED 
BROWSE NOWAIT 

CREATE TABLE notbigenough3 (f1 c(2), f2 n(2,0), f3 n(3,1), f4 c(20))
APPEND FROM testinput.csv TYPE DELIMITED 
BROWSE NOWAIT 

CREATE TABLE notbigenough4 (f1 c(2), f2 n(2,0), f3 n(2,1), f4 c(20))
APPEND FROM testinput.csv TYPE DELIMITED 
BROWSE NOWAIT 

CREATE TABLE notbigenough5 (f1 c(2), f2 n(2,0), f3 n(2), f4 c(20))
APPEND FROM bigenough &&try a DBF
BROWSE NOWAIT

WAIT WINDOW "here it comes" TIMEOUT 2
REPLACE f2 WITH 111

 

[baltman]

I've updated the procedure to identify the obvious "*" overflow issue as well..

DataLossTest("C:\Data\notbigenough4.dbf")
DataLossTest("C:\Data\notbigenough5.dbf")

PROCEDURE DataLossTest
    LPARAMETERS tcFullPathTargetTable
    LOCAL ARRAY aFieldTest[1]
    LOCAL cAlias, i, cField, cFailList, CRLF
    cAlias = JUSTSTEM(m.tcFullPathTargetTable)
    STORE "" TO m.cFailList
    STORE CHR(13) + CHR(10) TO m.CRLF 
    
    IF SELECT(JUSTSTEM(m.tcFullPathTargetTable))=0
        USE (m.tcFullPathTargetTable) IN 0 ALIAS (m.cAlias)
    ENDIF

    SELECT (m.cAlias)
    AFIELDS(aFieldTest)

    FOR m.i = 1 TO ALEN(aFieldTest,1)
      IF aFieldTest[m.i, 2] = "N"
        m.cField = aFieldTest[m.i, 1]
        
        IF aFieldTest[m.i, 4]>0
          LOCATE FOR LEFT(TRANSFORM(&cField),1)="*" OR LEN(TRANSFORM(INT(&cField))) > ;
            aFieldTest[ASCAN(aFieldTest,m.cField,1,ALEN(aFieldTest,1),1,2)+2]-;
            aFieldTest[ASCAN(aFieldTest,m.cField,1,ALEN(aFieldTest,1),1,2)+3]-1
        ELSE
          LOCATE FOR LEFT(TRANSFORM(&cField),1)="*"      
        ENDIF   
        
        IF !EOF()
          m.cFailList = m.cFailList + m.cField +", "
        ENDIF
      ENDIF
    ENDFOR

    IF !EMPTY(m.cFailList)
      m.cFailList = LEFT(m.cFailList, LEN(m.cFailList)-2)
      MESSAGEBOX("Data Loss possible in the following fields. " + ;
          "Please Fix Before Continuing." + m.CRLF + m.cFailList, 0 + 16)
    ENDIF
ENDPROC

 

[dbMark]

Also, if a number is too large, it is stored in the numeric field as .123E+15, just in case you were wondering whether only minute numbers were converted to scientific notation.

 

[baltman]

Yes, I have seen the scientific notation before as well. Usually when VFP is pulling from Excel and Excel is already displaying it that way.

We could certainly add "E"$trans(&cField) to the test.

I pre-handle some of the conditions in my import phase which has left my procedure less than complete when applied to other situations.

Brian

 

[1421]

Thank you all again! DBMARK YOU DID IT! I SEARCHED FOR 'E'
USING BALTMAN'S PROCEDURE AND FOUND THE PROBLEM DATA. BELOW IS LISTED MY SLIGHT CHANGES TO BALTMAN'S PROCEDURE
===========================================================
LPARAMETERS tcFullPathTargetTable
clear
LOCAL ARRAY aFieldTest[1]
LOCAL cAlias, i, cField, cFailList, CRLF
cAlias = JUSTSTEM(m.tcFullPathTargetTable)
STORE "" TO m.cFailList
STORE CHR(13) + CHR(10) TO m.CRLF

IF SELECT(JUSTSTEM(m.tcFullPathTargetTable))=0
USE (m.tcFullPathTargetTable) IN 0 ALIAS (m.cAlias)
ENDIF

SELECT (m.cAlias)
AFIELDS(aFieldTest)

FOR m.i = 1 TO ALEN(aFieldTest,1)
IF aFieldTest[m.i, 2] = "N"
m.cField = aFieldTest[m.i, 1]
?m.cField
IF aFieldTest[m.i, 4]>0
LOCATE FOR LEFT(TRANSFORM(&cField),1)="*" OR LEN(TRANSFORM(INT(&cField))) > ;
aFieldTest[ASCAN(aFieldTest,m.cField,1,ALEN(aFieldTest,1),1,2)+2]-;
aFieldTest[ASCAN(aFieldTest,m.cField,1,ALEN(aFieldTest,1),1,2)+3]-1;
OR AT('E',STR(&cField))<>0
ELSE
LOCATE FOR LEFT(TRANSFORM(&cField),1)="*"
ENDIF

IF !EOF()
m.cFailList = m.cFailList + m.cField +", "
ENDIF
ENDIF
ENDFOR

IF !EMPTY(m.cFailList)
m.cFailList = LEFT(m.cFailList, LEN(m.cFailList)-2)
MESSAGEBOX("Data Loss possible in the following fields. " + ;
"Please Fix Before Continuing." + m.CRLF + m.cFailList, 0 + 16)
ENDIF
?'====done===='
ENDPROC
===========================================================

Thank You again!

 

[baltman]

The search of "E" should be in both the decimals and no decimal versions... glad you found it.

Brian