Another export question

[Brian K. White]

----- Original Message ----- 
From: "Matthew Williams" <matthew.d.williams at gmail.com>
To: "FilePro Mailing List" <filepro-list at lists.celestial.com>
Sent: Monday, January 17, 2005 11:42 AM
Subject: Another export question


> This question follows up on what I had previously written last week
> about dynamically naming the export file based off a record field.
>
> What I need to do:
> I have a file with a bunch of records sitting on them, they are sorted
> by "RepCode", a simple 3 digit code.  In a single output process I
> would like to:
>
> Look at the first record and copy its "RepCode" into a variable.
> Execute the export command using that variable as the output filename
> Export any records that belong to that RepCode into the file until all
> records are exported that belong to that RepCode
> When I reach a record that has a new RepCode it jumps back to the
> beginning and copies the new RepCode into the variable, runs the
> export again with the new filename and dumps all corresponding
> records.
>
> So when the process is finished running I will have a dozen or so
> outputted delimited files (filename matching the RepCode) each with
> however many records correspond to them
>
> I think that makes sense.  I'm just not familiar with how I would
> write the loop?
>
> Then: aa(3,*,g)=3;goto export
>
> new
> If: aa ne 3
> Then: aa=3;goto export
>
> export
> Then: export invoice = /upss/(aa).txt <arguments>
>
> dump
> Then: invoice(1)=1.........
> If: aa ne 3
> Then: goto new
> Then: goto dump
>
>
> Ugly code, I know but I need to start somewhere.
>
> Any ideas on the propper way to do this?  Help would be appreciated!


I'd make a processing-only report sorted on field 3

Then processing like this:
---top---
if: rc{"" ne 3{"" and rc ne ""
then: close inv
if:
then: rc(3,*,g) = 3 ; of = "/upss/" { 3 { ".txt"
if:
then: export inv = (of) args...
if:
then: inv(1) = 1
if:
then: ...
---end---

The first line tests if the current repcode is the same as the previous 
record before the export command
It's careful to make sure that "rapid" and "rapid freight" don't get treated 
as equivalent.
The rc ne "" part is to handle the special case of the very first record.

Whenever the repcode changes, the current export file is closed so that when 
the export command comes for this record we don't just keep opening up new 
files until we reach the OS limit of how many files a user can have open at 
once.

Then we fill the rc variable with the current repcode every time so that we 
can check it in the next record.

Then the export command will just naturally come out the same every time for 
every record that has the same repcode.

Thn just assign fields at will and that's the end of the table


There should really be a few more things to deal with various not uncomon 
bad cases.
What if 3 has embeded spaces?
What if 3 is empty?
What if 3 has characters that are inconvenient or even dangerous for use in 
a filename like *?/\ etc...
for all of that you would make a gosub that uses xlate to translate any bad 
characters into safe ones and put the result in a variable and use that 
variable in the filename instead of 3
The test for the first record should really probably be done in @once but 
every time I post something that uses @once the person doesn't have it. 
(Everyone should get 5.0 unless there is an unavoidable _technical_ reason. 
Like your code fails on 5.0 or 5.0 is not available for your platform. We 
want support from fp right?)
What if the data in some field hapens to have a double-quote in it? Strictly 
speaking that should be safe but in reality various windows programs that 
import csv files fail if the data has a double quote in it even though it 
should be looking only for the quote-comma together or the bare comma if the 
starting comma didn't have a quote next to it.

another possible twist that would be more efficient and simpler if fp works 
the way I'm thinking...
add a subtotal break on 3, and put the close statement in @wbrk3 and don't 
even have the rc test or the rc variable at all.
This would work if @wbrk happens after the other normal per record 
processing, in that case, rather than testing for a change the first thing 
every record, we let fp do it automatically the last thing every record. No 
special worries about the first record this way either.
I don't know if it actually works that way or not though.

like this:
---top---
if:
then: of = "/upss/" { 3 { ".txt"
if:
then: export inv = (of) args...
if:
then: inv(1) = 3
if:
then: ...
if:
then: end
@wbrk3 : if:
then: close inv ; end
---end---

Here are plausible "cleaner" gosubs for filenames and for data:

cleanf  : If: ' clean for filenames
       Then: n = xlate(n,chr("34"){"`'/*? "&chr("92"),"") ; return
cleanq  : If: ' clean for data
       Then: n = xlate(n,chr("34"),"'") ; return

use them like this:
if:
then: n = 3 ; gosub cleanf ; of = "/upss/" { n { ".txt"
...
if:
then: n = 6 ; gosub cleanq ; inv(6) = n

You would only want to use cleanq on fields that have a good chance of 
having unsafe characters like names, addresses, and descriptions/notes.

The cleanq allows everything, just converts " to '

The cleanf deletes " ` ' / * ? \ and spaces


Brian K. White  --  brian at aljex.com  --  http://www.aljex.com/bkw/
+++++[>+++[>+++++>+++++++<<-]<-]>>+.>.+++++.+++++++.-.[>+<---]>++.
filePro BBx  Linux SCO  Prosper/FACTS AutoCAD  #callahans Satriani