How To Export A Set Into A Flat File

[mspearman]

I have a large set in a functional Alpha 5 database that I need to export into a complex flat file output. I will simplify by example what I need to do:

Create a standard header record with a few variables like time/date.
Create a level 1 record with data from the top level table in the set
Create Multiple level 2 records with data from child tables.
Create Multiple level 3 records with data from sub-child tables.
Create a standard footer record.

If a record is new mark the record as an addition.

If the record is an update, create a record with information about the record for deletion, then create the a record with the same information as a new addition would have. The only way that I will know that the record is an update will be a flag that I have in the table at the top level of the set.

Nearly every field's output will need actions done to change the format. For example, dates and times will be converted from Alpha 5 default format to YYYYMMDDHHMM format.

I'm definitely not looking for exact instructions, this is complex. Referrals to sections of the help file and/or other posts are fine. I am not having any luck finding anything related for the exception of how to export 1 table to simple flat file layout. I am looking for an approach to the problem. I do not have experience with Xbasic yet, but I understand programming. My first thought is that I will have to code all of this in Xbasic. Is there an easier way with Action Scripting?

My current approach is moving all of the fields that I need, reformatting them as needed, then moving them from temporary tables. From the temporary tables, I plan on making an extract process to export into the flat file format that I need. It's just a first guess at how to make this happen.

Any help is greatly appreciated.

[Tom Cone Jr]

Re: How To Export A Set Into A Flat File

Mark,

Here are some ideas:

a) Create the output text file with <file>.create(), and then use <file>.write_line() statements to build the text file one line at a time, using nested loops to work your way through the source set records. Use <file>.flush() and <file>.close() to commit the text to disk; or

b) Use SET.EXTERNAL_RECORD_CONTENT_GET() to build a crlf() list containing fields from the records in the set, and then use a combination of the <list> processing and <file> processing functions to create the necessary text file. I foresee a problem using this approach if the SET.EXTERNAL_RECORD_CONTENT_GET() expression cannot be written in fewer than 1023 characters because the number of your fields is too large or the table and fieldnames are too long; or

c) Create separate export operations, each based on a table in your set, to create separate text files containing the necessary data. Then stitch them together by reading through the text files one line at a time and writing the reformatted data to a single text file.

I think option (a) will be the most flexible and easiest to implement.

[mspearman]

Re: How To Export A Set Into A Flat File

Thank you so much Tom! This is exactly the info I was looking for. Option A looks good, there are some situations that may require C though.