Re: How to sort a flat text file on the 400.

[Scott Klement <klemscot@xxxxxxxxxxxx>]

On Thu, 30 Oct 2003, McGivern, Tom wrote:
>
> I have a rexx script that parses some data.. And generates some output.
> This run in batch generates a qprint file.

Why not have it output directly to a stream file and/or database file
as required?

> I've been doing a CPYSPLF to copy the file to a test library, and then
> download it to a unix system, where I can sort the darn thing, and
> continue with a rexx script on unix.

I don't know if it helps, but the standard unix "sort" command is
available in QShell on your iseries.  You can do sorts like:

   sort -t "," -k 2,3 < /path/to/myfile.csv > /path/to/sortedfile.csv

I thought this might be interesting to you.  Certainly, there are better
tools (SQL, OPNQRYF, FMTDTA, etc) available if you convert your file to
a database.

> I'd like to keep the whole thing on the 400, so I can schedule it to run
> regularly.

That's the nice thing about QShell, it's on the 400, but allows you to do
some of the unix commands that really help when working with stream files.


> I guess I'd like to get the data into a database file where I can then
> SELECT with an ORDER BY clause.

To do that, you'll want to create DDS that corresponds to the fields in
your stream file, and use CPYFRMIMPF to populate it.   Make sure that you
allow nulls on each field that can possibly be blank...

> Any suggestions on getting the following looking data into a database?
> "R12345","10/30/2003","10:20:15", etc.. (comma separated values format).
> "R12346","10/30/2003",".....

As I said, CPYFRMIMPF works for a lot of cases.   If that doesn't work,
you might consider writing a program in ILE C or ILE RPG that converts the
file as required.

I wrote a tutorial on reading/writing stream files in RPG that might be
useful if you just can't get CPYFRMIMPF to do the job:
   http://www.scottklement.com/rpg/ifs.html