[ic] Export Specific Columns and Rows

Jon interchange-users@icdevgroup.org
Sun Nov 3 08:35:01 2002


>
> > >
> > > I don't use SQL yet.  Thanks to Ed LaFrance, this code selects all pending
> > > orders and exports certain fields of their's to a txt file:
> > >
> > > [loop search="
> > >   co=yes
> > >         fi=transactions
> > >         st=db
> > >         tf=code
> > >
> > >         sf=status
> > >         se=pending
> > >         op=rm
> > >         nu=0
> > >
> > >         sf=deleted
> > >         se=1
> > >         nu=0
> > >         op=!~
> > > "][tmp pending_order][scratch pending_order]"[loop-code]",
> > > "[loop-data transactions fname] [loop-data transactions lname]",
> > > "[loop-data transactions company]",
> > > "[loop-data transactions fname] [loop-data transactions lname]",
> > > "[loop-data transactions address1]",
> > > "[loop-data transactions address2]",
> > > "[loop-data transactions city]",
> > > "[loop-data transactions state]",
> > > "[loop-data transactions zip]",
> > > "[loop-data transactions phone_day]",
> > > "[loop-data transactions fname] [loop-data transactions lname]",
> > > "[loop-data transactions email]",
> > > "Thanks for shopping at TrippyStore.com!",
> > > "Email = [data base=userdb field=email_copy key='[loop-data transactions
> > > usernam
> > > e]']",
> > > "Res. = [loop-data transactions delivery_type]"
> > >
> > > [/tmp][/loop]
> > > [calc]
> > >         $Scratch->{pending_order} =~ s/\",\n/\",/g;
> > >         $Scratch->{pending_order} =~ s/\n/\r/g;
> > >         return;
> > > [/calc]
> > > [log
> > > file="orders/pending/pending_orders.txt"
> > > type=text
> > > interpolate=1
> > > hide=1
> > > create=1
> > > umask=022][scratch pending_order][/log]
> > >
> > > Hope it helps!
> >
> >     Yes it does.  A whole lot !  Between what you've provided and what
> > Mike has given me I continue to learn... so much more to go.  Anyway.
> >
> >     I like your approach because it provides more flexibility in how the
> > data is stored in the flat file, however, the application I'm dealing with
> > seems to want column headings which Mike's approach provides.
> > I believe what is happening is Mike's approach is creating a log with the
> > contents as specified by the search/loop parameters and by default(?) column
> > headings are added.  Where I believe what your approach does is store
> > everything in the scratch area and then dumps it to the log with needed
> > modifications via perl. So I think if I could get the column headings that
> > should do it.   Any thoughts ?      Thanks again.
> >
> >
>
>  What I've wound up doing is using the above approach to create the
> initial CSV file.  Then I hacked a perl script, which I manually invoke, to add
> the headers, and remove blank lines utilizing a temporary file. yuk . . . but it
> works.
>
>     I got to thinking about my needs and was wondering if there is a simple way
> to modify export_table.html to provide a record filter so it doesn't export all
> the
> records in a given table.   Anyone have an easy change ?
>

    In case anyone is interested I did a couple of things that has gotten me pretty
much
what I want.  A slight change to the above solution is to add the column headings
to the [log] tag just before the [scratch pending_order] is dumped into the
specified file.   Like this

umask=022]ordernumber,name,company,street,address2,city,state,zip,country,phone_day,email,shipmode,numitems,residence

[scratch pending_order][/log]

Plus I did a little tweaking to the perl stuff inside of the [calc] tags.

Jon