How to add blank records

[JL_CAD]

I have this code below that imports external .csv files. The only problem that I have is when there are no more blank records available in the "toolwork" file in Filepro, the processing gets stuck in a loop and keeps importing the same file. Is there a way to add a blank record before importing the file?

::end:
@once:'@once::
::declare chosen_file(256),  handle_for_opendir(8,.0):
::declare foldername(256):
::foldername="f:/TWR/import":
loop_2:'loop_2::
::handle_for_opendir=opendir("*.csv",foldername):
:handle_for_opendir lt "0":exit:
::gosub do_it:
::handle_for_opendir=closedir():
::goto loop_2:
do_it:'do_it :: 
::n="1":
loop::import word  file=(foldername { "/" { @dirlist_filename[(n)]):
:not file:goto fin_imp:
::lookup new=toolwork  r=free:
::new(1)=file(1); new(2)=file(2); new(3)=file(3); new(4)=file(4);:
::new(5)=file(5); new(6)=file(6); new(7)=file(7); new(=file(;:
::new(9)=file(9); new(10)=file(10); new(11)=file(11):
::write new:
::n=n+1:
::goto loop:
fin_imp:'fin_imp::
::close file:
::declare handle_for_remove(8,.0):
::handle_for_remove=remove(foldername { "/" { @dirlist_filename[(n)]):
::return:
finish:'finish::

[kenbrody]

Re-examine your code.  You have an infinite loop:

loop_2:'loop_2::
::handle_for_opendir=opendir("*.csv",foldername):
:handle_for_opendir lt "0":exit:
::gosub do_it:
::handle_for_opendir=closedir():
::goto loop_2:

You open the directory, call do_it, close the directory, and execute that loop forever.

Next, your do_it subroutine returns after doing only the first file.  (When you hit EOF on the import, you go to fin_imp, which closes the import and executes a RETURN.  This RETURN returns back to loop_2.)

[JL_CAD]

I forgot to mention that I have to use a selection sentence as follows:

@rn "record number" eq to 1

This way my table does not go into an infinite loop unless there are no more blank records.

So my problem is when I run out of blank records to use.

[kenbrody]

I'm not sure I understand what you mean by "run out of blank records to use".  Your code posts to new records with a lookup "r=free", which gets a new record from the file.  If a free record does not exist, it will add a new one off the end of the file.

However, your code still never gets past the first file in the list.  The "do_it" subroutine processes the first file only.  As soon as it hits EOF on that first file, it goes to fin_imp (which removes some file other than the one you're currently importing) and returns.  Once you're back in the loop_2 loop, it goes back and starts the opendir() list again, and calls do_it, which again imports only the first file.  The only thing out of the loop_2 loop is for there to be no *.csv files in the directory.

[JL_CAD]

The following actions take place when I run the processing table with selection sentence:

• A .csv file from the specified directory is imported into Filepro "Toolwork" free record
• The .csv file is deleted
• This action repeats until there are no more .csv files

I use this processing everyday. The only problem I have had is when there are no more blank records (records that have been deleted) in "Toolwork". Again, it seems like the processing gets stuck in a loop and does not delete the .csv file, which causes the loop not to end. I've tested the processing on another file with no blank records and it did the same thing... Filepro adds a new record and imports the .csv file, but the .csv file never gets deleted.

[kenbrody]

What you are saying doesn't make sense based on the code you posted.

First, it doesn't matter if there are any free records in the Toolwork file, as the lookup "r=free" will add a new record off the end of the file if there are no existing free records.

Second, your code in "do_it" will only import from the first .csv file in the list, and then fin_imp will delete some other file.

Step through the code, starting at "do_it".  (I'll assume there are three lines in the .csv file for the sake of example.)

• n is set to "1"
• You start importing from the first .csv file.
• You get a free record from Toolwork.  (Remember -- if there are no free records, one will be added off the end of the file.)
• You write the import data to the Toolwork record.
• You increment n.  (It's now 2.)
• You loop back to "loop".
• You get the second record from the first .csv file.
• You get a free record, popuplate it with the import, and write it.
• You increment n to 3 and go back to "loop".
• You repeat the above with the third record from the .csv file, and increment n to 4.
• The import now fails, as there are no more records in the first csv file.
• You go to "fin_imp".
• You close the first .csv file.
• You delete the fourth .csv file in the list. (n is "4" at this point.)
• You return to the main "loop_2" loop.
• You close the opendir list, and create a new list, and repeat the above, which in all likelihood will start with the same .csv file you just imported.

Note that, even if there is only one record in the .csv file, the above sequence will still cause the second csv file to be deleted after importing the first.  (n will be "2".)

[JL_CAD]

Somehow the code works as long as I have free records available... it was written by John Esak last year.
Is there a debugger that will step through the code in Filepro?

[kenbrody]

Is there a debugger that will step through the code in Filepro?

Yes.  Add "-db" to the command line, or add "debug on" after the @ONCE label.

[JL_CAD]

OK,
I ran the processing with "debug on" and found that the first file in the directory is all that Filepro works with. I tried to describe what was happening best I could:

I'll start from loop2.

loop2:
Filepro checks that there is a file, if not... exit
If there is a file... go to sub do_it

do_it:
n="1" before the loop

loop:
If no file[n], goto fin_imp
If file[n] exists, Import the file [n]
write new
n=n+1
goto loop
Since n=2, the statement ":not file:goto fin_imp:" is true even if there are multiple files in the directory?
So, the code jumps to "fin_imp:'fin_imp::" and deletes the first file because n still  equals "1" outside of the loop.

return goes back to the line "gosub do_it"
which ends up going back to "loop2" and checking for a csv file.
If there are no more files, then the processing will exit and end.

If more files exist, it continues the processing on the next file.

[kenbrody]

First, a few misconceptions you seem to have:

• The IMPORT statement doesn't "import the file".  Rather, it imports one record from the file.  (Each subsequent execution will import successive records.)
• Also, once the import has started, simply changing the name passed to the export does not close the existing export and open a new one.  It will continue with the existing file until you CLOSE it.  (So the second time through the loop, the first file is still being imported, even through n is "2".)

Since n=2, the statement ":not file:goto fin_imp:" is true even if there are multiple files in the directory?

See the second item above.  You are still importing the currently-open file (ie: the first file).  If "not file" is true, it's because you have hit EOF.

So, the code jumps to "fin_imp:'fin_imp::" and deletes the first file because n still  equals "1" outside of the loop.

No, n is "2", so it deletes the second file.  You can verify this in the debugger, as well as by examining the directory and seeing which file gets deleted.

Returning to loop2, you now closedir/opendir and return to "do_it" which will once again import only the first file, and will delete the second file.  (Assuming that the files have one record in them, as appears to be the case, based on your description.)  It will continue doing this as infinitum, as you never delete the first file, meaning there will always be a file to import.

[JL_CAD]

No, n is "2", so it deletes the second file.  You can verify this in the debugger, as well as by examining the directory and seeing which file gets deleted.

I ran the code with the debugger on while watching the directory that contained the files. The first file in the directory was deleted each time it looped through fin_imp... so somehow n must =1 after exiting out of "loop:"

Is there a way to display the value of n after exiting the loop?

msgbox n ?

[kenbrody]

I just noticed:
  n = n + 1
This adds field 1 to n, not the value "1".  If field 1 is not numeric, n remains at its initial value of "1".

So, after importing all of the first file, n is still "1", so your fin_imp code deletes the first file.  The above bug in your code happens to allow the rest of the code to work.

However, your code still only imports the first file.  It happens to work because you then generate a new list of files, with a new first file, rather than simply working off the list you already generated.

[JL_CAD]

Since you agree that the code works (although it's not the cleanest code), why does Filepro go into a loop when there are no more free records? Does it have to do with my selector sentence?
@rn "record number" eq to 1

I have 3079 records in "Toolwork".
Record 1-2837 & 3030-3078 are used with 2838-3029 & 3079 being blank.
When I run the processing in FP, the imported records get added in front of the last set of records...
for example: if I import 3 files (1 record per file), the record numbers would be 3029,3028,&3027.

[kenbrody]

Yes, now that I caught your typo, which makes the errors cancel each other out, I can see that it should run as designed, if inelegantly.

As for why it doesn't work if there are no free records in the toolkit file, I have no idea.  Unless there is some other typo that I missed, the lookup "r=free" will create new records off the end of the file if there are no free records in the file.  Create a situation in which there are no free records and run it through the debugger to see what's going on.

[JL_CAD]

I'm going back to the subject of this post: "How to add blank records"

Would it be possible to add records to a file with an input processing table and then delete those records so that the file is expanded?... say from 1000 records to 5000.

Also, is there a way to compress the file to get rid of all blank records?