|
Home > Archive > Programming with dBASE > December 2005 > More generic versiont of this .PRG?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
More generic versiont of this .PRG?
|
|
| Gerald Lightsey 2005-11-27, 3:24 am |
| I began working on the following program using classic dBASE syntax
common to many of the older versions. After spending far too much time
with errors coming out of using field() and len() I modified the .PRG to
use the Array class' fields() method as shown below which works
successfully. Does anyone have a quick insight in how to successfully
use field() and len() in classic dBASE syntax to tell the program to
look for and replace either "\N" in a field with a length greater than 1
or "\" in a field with a length of 1? After handling the first field
correctly I kept getting an incorrect field length of zero being handed
to the inner IF...ENDIF clause.
* CleanupNulls.prg
* This program cleans up null representations, (i.e. "\N"), imported
into dBASE from .TXT files output from MySQL tables.
* GKL 11/23/05
close tables
clear
thisFile = getfile("*.dbf","Select a .DBF table for clean-up of
NULLS",true,".dbf")
use (thisFile) in select() alias target
select target
oRef = new array()
oRef.fields()
fileLength = oRef.size / 4
for i = 1 to fileLength
if oRef[i,2] = "C"
if oRef[i,3] = 1
command = "replace " + oRef[i,1] + " with null for " + oRef[i,1]
+ " = ''"
&command.
else
command = "replace " + oRef[i,1] + " with null for trim(" + oRef
[i,1] + ") = '\N'"
&command.
endif
?command
endif
next
close tables
oRef = null
Gerald
| |
| Marko Mihorko [dBVIPS] 2005-11-27, 8:23 pm |
| Hello Gerald!
"Gerald Lightsey" je napisal v sporocilo...
> Does anyone have a quick insight in how to successfully
> use field() and len() in classic dBASE syntax to tell the program to
> look for and replace either "\N" in a field with a length greater than 1
> or "\" in a field with a length of 1?
Ah, classic dBASE syntax, what fond memories...well, back to the problem. ;-)
And it was a quick one, indeed <g>. Namely, it ran successfully for the first
time I tried. Surprisingly... ;-)
Marko Mihorko [dBVIPS]
* CleanupNulls.prg
* This program cleans up null representations, (i.e. "\N"),
* imported into dBASE from .TXT files output from MySQL tables.
* GKL 11/23/05
close tables
clear
thisFile = getfile("*.dbf", ;
"Select a .DBF table for clean-up of NULLS",true,".dbf")
use (thisFile) in select() alias target
select target
for i = 1 to fldcount()
if type(field(i)) = "C"
/*
if flength(i) = 1
command = "replace " + field(i) + " with null for " + ;
field(i) + " = ''"
&command.
else
command = "replace " + field(i) + " with null for trim(" + ;
field(i) + ") = '\N'"
&command.
endif
*/
// or even better:
command = "replace " + field(i) + " with null for "+;
iif(flength(i) = 1, "", "trim(") + field(i) + ;
iif(flength(i) = 1, " = ''", ") = '\N'")
&command.
?command
endif
next
close tables
| |
| Gerald Lightsey 2005-11-28, 3:23 am |
| On Sun, 27 Nov 2005 21:58:19 +0100, in the dbase.programming group,
Marko Mihorko [dBVIPS] said...
>
> Ah, classic dBASE syntax, what fond memories...well, back to the problem. ;-)
>
> And it was a quick one, indeed <g>. Namely, it ran successfully for the first
> time I tried. Surprisingly... ;-)
Thanks Marko.
Since flength() is a function that did not appear until VdB 5.x I didn't
think of it. Your use of it properly responds to what I asked
unfortunately not to what I SHOULD have asked.
Just as an academic exercise How would you go about extracting the
length of unknown fields names as you traverse through them in dBASE 5
DOS and/or dBASE IV?
In the example that follows the first time the program reaches...
?len(&fieldName.)
....the correct length of the field is returned. After that it returns
zero. Why does this error occur and how would one work around it?
Gerald
* CleanupNulls00.prg
* This program cleans up null representations, (i.e. "\N"), imported
* into dBASE from .TXT files output from MySQL tables.
* GKL 11/27/05
*
close tables
clear
tableFile = getfile("*.dbf","Select a .DBF table for clean-up of
NULLS",true,".dbf")
use (tableFile) in select() alias target
select target
for i = 1 to fldcount()
public fieldName
fieldName = field(i)
?fieldName
if type(fieldName) = "C"
?len(&fieldName.)
if len(&fieldName.) = 1
command = "replace " + fieldName + " with null for " + fieldName +
" = ''"
else
command = "replace " + fieldName + " with null for trim(" +
fieldName + ") = '\N'"
endif
&command
?command
endif
next
close tables
| |
| Marko Mihorko [dBVIPS] 2005-11-28, 11:23 am |
| Hello Gerald!
"Gerald Lightsey" je napisal v sporocilo...
> Thanks Marko.
You are welcome.
> Since flength() is a function that did not appear until VdB 5.x I didn't
> think of it. Your use of it properly responds to what I asked
> unfortunately not to what I SHOULD have asked.
Well... ;-)
> Just as an academic exercise How would you go about extracting the
> length of unknown fields names as you traverse through them in dBASE 5
> DOS and/or dBASE IV?
Ah, I can't resist in any academic cases <bg>. So, see below.
> In the example that follows the first time the program reaches...
> ?len(&fieldName.)
> ...the correct length of the field is returned. After that it returns
> zero. Why does this error occur and how would one work around it?
For me the code's line <? len(&fieldName.)> didn't manage to return the
correct length of the field also for the first time (namely, it was not "1",
however, "1" was returned ;-).
So, I threw together something else. And I think that it should work even in
the storied "dBASE III Plus"... ;-)
Marko Mihorko [dBVIPS]
* CleanupNulls00.prg
* This program cleans up null representations, (i.e. "\N"), imported
* into dBASE from .TXT files output from MySQL tables.
* GKL 11/27/05
*
close tables
clear
tableFile = getfile("*.dbf",;
"Select a .DBF table for clean-up of NULLS",true,".dbf")
use (tableFile) in select() alias target
copy to temp_stru stru exte
use temp_stru in select() alias structure
select target
for i = 1 to fldcount()
public fieldName
fieldName = field(i)
?fieldName
if type(fieldName) = "C"
// ?len(&fieldName.)
// if len(&fieldName.) = 1
select structure
locate for field_Name = fieldName
select target
if structure->field_len = 1
command = "replace " + fieldName + " with null for " + ;
fieldName + " = ''"
else
command = "replace " + fieldName + " with null for trim(" + ;
fieldName + ") = '\N'"
endif
&command
?command
endif
next
close tables
delete file temp_stru.dbf
| |
| Marko Mihorko [dBVIPS] 2005-11-28, 1:23 pm |
| Hello Gerald!
"Gerald Lightsey" je napisal v sporocilo...
> Thanks Marko.
You are welcome.
> Since flength() is a function that did not appear until VdB 5.x I didn't
> think of it. Your use of it properly responds to what I asked
> unfortunately not to what I SHOULD have asked.
Well... ;-)
> Just as an academic exercise How would you go about extracting the
> length of unknown fields names as you traverse through them in dBASE 5
> DOS and/or dBASE IV?
Ah, I can't resist in any academic cases <bg>. So, see below.
> In the example that follows the first time the program reaches...
> ?len(&fieldName.)
> ...the correct length of the field is returned. After that it returns
> zero. Why does this error occur and how would one work around it?
For me the code's line <? len(&fieldName.)> didn't manage to return the
correct length of the field also for the first time (namely, it was not "1",
however, "1" was returned ;-).
So, I threw together something else. And I think that it should work even in
the storied "dBASE III Plus". Well, at least the part that I added, since I
suspect a little bit about the success of "getfile()" and "null" in the "dBASE
5 for DOS" and "dBASE IV" package, respectively. ;-)
Marko Mihorko [dBVIPS]
* CleanupNulls00.prg
* This program cleans up null representations, (i.e. "\N"), imported
* into dBASE from .TXT files output from MySQL tables.
* GKL 11/27/05
*
close tables
clear
tableFile = getfile("*.dbf",;
"Select a .DBF table for clean-up of NULLS",true,".dbf")
use (tableFile) in select() alias target
copy to temp_stru stru exte
use temp_stru in select() alias structure
select target
for i = 1 to fldcount()
public fieldName
fieldName = field(i)
?fieldName
if type(fieldName) = "C"
// ?len(&fieldName.)
// if len(&fieldName.) = 1
select structure
locate for field_Name = fieldName
select target
if structure->field_len = 1
command = "replace " + fieldName + " with null for " + ;
fieldName + " = ''"
else
command = "replace " + fieldName + " with null for trim(" + ;
fieldName + ") = '\N'"
endif
&command
?command
endif
next
close tables
delete file temp_stru.dbf
| |
| Gerald Lightsey 2005-11-29, 8:23 pm |
| On Mon, 28 Nov 2005 18:29:59 +0100, in the dbase.programming group,
Marko Mihorko [dBVIPS] said...
> For me the code's line <? len(&fieldName.)> didn't manage to return the
> correct length of the field also for the first time (namely, it was not "1",
> however, "1" was returned ;-).
>
> So, I threw together something else. And I think that it should work even in
> the storied "dBASE III Plus". Well, at least the part that I added, since I
> suspect a little bit about the success of "getfile()" and "null" in the "dBASE
> 5 for DOS" and "dBASE IV" package, respectively. ;-)
>
> Marko Mihorko [dBVIPS]
>
>
> * CleanupNulls00.prg
> * This program cleans up null representations, (i.e. "\N"), imported
> * into dBASE from .TXT files output from MySQL tables.
> * GKL 11/27/05
> *
> close tables
> clear
> tableFile = getfile("*.dbf",;
> "Select a .DBF table for clean-up of NULLS",true,".dbf")
> use (tableFile) in select() alias target
>
> copy to temp_stru stru exte
> use temp_stru in select() alias structure
>
> select target
> for i = 1 to fldcount()
> public fieldName
> fieldName = field(i)
> ?fieldName
> if type(fieldName) = "C"
> // ?len(&fieldName.)
> // if len(&fieldName.) = 1
>
> select structure
> locate for field_Name = fieldName
> select target
> if structure->field_len = 1
>
> command = "replace " + fieldName + " with null for " + ;
> fieldName + " = ''"
> else
> command = "replace " + fieldName + " with null for trim(" + ;
> fieldName + ") = '\N'"
> endif
> &command
> ?command
> endif
> next
> close tables
>
> delete file temp_stru.dbf
Marko,
This works as well which brings us up to about three ways, (up to this
point), to successfully handle the basic problem. I continued to have
an "academic" interest about why I couldn't use the len() function with
a macro to get reliable results. Here is what I found.
1. A level 7 table with nulls, (e.g. autoNullFields = true), does not
return accurate values when the len(function) is used. For example...
clear
use :dbaseSamples:sample
list structure
go top
edit colu
?len(:source folder:) // returns 30 instead of 45 for me.
This anomaly appears to be because the field was filled with "Samples"
followed by 23 space characters followed by 15 null characters in the
field. So len stops counting when it reaches the first null character.
I don't know why the fields in the Samples table are filled this way.
If I fill a table from a .txt file type delimited with autoNullFields =
false, I appear to get accurate values from len() in a level 7 table.
2. The use of the <memvar> in a for..endfor construct within a macro
does not work properly. This can be worked around by using a Do...While
construct.
* CleanupNulls000.prg
* This program cleans up null representations, (i.e. "\N"), imported
into dBASE
* from .TXT files output from MySQL tables.
* GKL 11/28/05
*
close tables
clear
tableFile = getfile("*.dbf","Select a .DBF table for clean-up of
NULLS",true,".dbf")
use (tableFile) in select() alias target
select target
FLD = 1
do while FLD <= fldcount()
fieldName = field(FLD)
?fieldName
if type(fieldName) = "C"
?len(&fieldName.)
if len(&fieldName.) = 1
command = "replace " + fieldName + " with " + '""' + " for " +
fieldName + " = ''"
else
command = "replace " + fieldName + " with " + '""' + " for trim("
+ fieldName + ") = '\N'"
endif
*&command
*?command
go top
endif
FLD = FLD + 1
enddo
close tables
I don't use level 7 tables often because of the need to retain
compatability with other programs and other versions of dBASE. In this
particular case, retaining MySQL field names longer than 10 characters
motivated me to set up some level 7 dBASE tables. I don't remember ever
seeing a report that len() when used with a character field's value
would not report the true length of the field structure.
So I learned something in the exercise. Thanks again for your
involvement.
Gerald
| |
| Marko Mihorko [dBVIPS] 2005-12-03, 1:23 pm |
| Hello Gerald!
"Gerald Lightsey" je napisal v sporocilo...
> I don't remember ever seeing a report that len() when used with a character
> field's value would not report the true length of the field structure.
Neither do I.
> So I learned something in the exercise. Thanks again for your
> involvement.
You are welcome.
Marko Mihorko [dBVIPS]
|
|
|
|
|