[ic] Export Specific Columns and Rows

Jon interchange-users@icdevgroup.org
Tue Oct 29 20:16:01 2002


Jon wrote:

> Grant wrote:
>
> > >Grant wrote:
> > >
> > >> >    Well, what I'm looking to do is take the transaction table to create
> > >> >a specific probably CSV type file to use for importing.  I like what the
> > >> >export
> > >> >function in the admin UI does except it dumps all rows and columns.
> > >> >Would the above example place commas between fields ? Thanks Mike.
> > >> >
> > >> >Jon
> > >>
> > >> I've had something like that set up so I can import the file it
> > >creates into
> > >> another program.  It used a search to search for the right rows and then
> > >> just pulled data from the appropriate columns though.  That might be an
> > >> easier way to go....
> > >>
> > >
> > >    Did you use the SQL facility or what ?   Can you give me a snippet of
> > >your code to work from ?  Thanks.
> > >
> > >Jon
> >
> > 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 ?

Jon