Exporting fields with array fields (and a date format question)

Exporting fields with array fields (and a date format question)

Post by John R Ramsde » Fri, 18 Oct 2002 03:14:26



I need to write a Progress 4GL '.p' script that exports Progress
table records to a CSV (comma-separated value) file, and I have
some questions which the Progress Language Reference Guide doesn't
seem to address.

To refer back to in the questions below, here is the code I will
be using (or something similar), for a table called say 'cust':

  OUTPUT TO cust.d:

  FOR EACH cust:
    EXPORT DELIMITER ',' recid cust.

  OUTPUT CLOSE.

The questions I have are:

  1. Will this 'recid cust' syntax dump for each record its
     'recid' value followed by those of the actual fields
     defined in the schema (and in the same order)? That's
     the intention.

     (I know recids are obsolete; but some tables include fields
     of type recid, which I presume reference records in other
     tables. So the recid values must all be saved, in order
     to maintain all these links. Horrible, but there you go!)

  2. If the table contains array fields, are these written
     as separate comma-separated fields? e.g. if field is
     a 3*array, is it just dumped as if it had been defined
     as three consecutive 'scalar' fields of the same type?

     If not, what is the format? Presumably if not it must
     be a delimited string, and in that case I just need to
     know the delimiter character.

  3. If the table contains date fields, what is the easiest
     way to ensure their values are written in a specified
     format such as dd/mm/yyyy or yyyy-mm-dd?

     (The program must actually dump more than one table.
     But as the same date format would be wanted for all
     tables, a program-wide default would be acceptable.)

     Same kind of thing applies to numeric fields - I don't
     want it 'pretty formatting' them with comma-separated
     thousands!

Many thanks in advance for any replies or pointers.

Cheers

---------------------------------------------------------------------------

---------------------------------------------------------------------------
"Eternity is a long time, especially towards the end."
  Woody Allen
---------------------------------------------------------------------------

 
 
 

Exporting fields with array fields (and a date format question)

Post by willia » Fri, 18 Oct 2002 08:55:20


See notes below. I am assuming V8 or better here. It is always a good
idea to include your Progress version and OS in any post.


> I need to write a Progress 4GL '.p' script that exports Progress
> table records to a CSV (comma-separated value) file, and I have
> some questions which the Progress Language Reference Guide doesn't
> seem to address.

> To refer back to in the questions below, here is the code I will
> be using (or something similar), for a table called say 'cust':

>   OUTPUT TO cust.d:

>   FOR EACH cust:
>     EXPORT DELIMITER ',' recid cust.

>   OUTPUT CLOSE.

> The questions I have are:

>   1. Will this 'recid cust' syntax dump for each record its
>      'recid' value followed by those of the actual fields
>      defined in the schema (and in the same order)? That's
>      the intention.

>      (I know recids are obsolete; but some tables include fields
>      of type recid, which I presume reference records in other
>      tables. So the recid values must all be saved, in order
>      to maintain all these links. Horrible, but there you go!)

This will give you two compile errors. you need to specify recid(cust),
and will probably need to put integer(recid(cust)), since some versions
of progress will not allow you to dump a raw recid value. The second
error will be something like "Unknown filed or variable cust". If you
want to dump a record you can only specify the table name, no other
values. You could try something like the following:

        for each cust:
            put unformatted integer(recid(cust)) ",".
            export delimiter "," cust.
        end.

The fields will be in the order that they are defined in the data
dictionary.

The larger problem of course is that, if you try and restore these
records to a progress database, they will all get new recid's, and your
links will be broken anyway.

Quote:>   2. If the table contains array fields, are these written
>      as separate comma-separated fields? e.g. if field is
>      a 3*array, is it just dumped as if it had been defined
>      as three consecutive 'scalar' fields of the same type?

>      If not, what is the format? Presumably if not it must
>      be a delimited string, and in that case I just need to
>      know the delimiter character.

Yes. the array fields will be dumped as separate values, with a comma
(or what ever character you specify) separating each.

Quote:>   3. If the table contains date fields, what is the easiest
>      way to ensure their values are written in a specified
>      format such as dd/mm/yyyy or yyyy-mm-dd?

The format is what ever is specified by the session:date-format
attribute which determines the sequence of year-month-day in the dump.
The actual format will be whatever is defined in the data dictionary for
the field, which is 99/99/99 by default.

Quote:>      (The program must actually dump more than one table.
>      But as the same date format would be wanted for all
>      tables, a program-wide default would be acceptable.)

>      Same kind of thing applies to numeric fields - I don't
>      want it 'pretty formatting' them with comma-separated
>      thousands!

Again format is as determined by the session:numeric-format and
session:numeric-decimal-point attributes. The format defined in the data
dictionary will also have an effect.

Hope this helps.

- Show quoted text -

> Many thanks in advance for any replies or pointers.

> Cheers

> ---------------------------------------------------------------------------

> ---------------------------------------------------------------------------
> "Eternity is a long time, especially towards the end."
>   Woody Allen
> ---------------------------------------------------------------------------


 
 
 

Exporting fields with array fields (and a date format question)

Post by Critta » Fri, 18 Oct 2002 17:43:43


John,

As William stated:

Quote:> The actual format will be whatever is defined in the data dictionary

Ifyou want to avoid the commas from the format then you would have to
go down to the field level and use PUT rather than EXPORT and over-ride
the format. An easier solution if you want to stick with EXPORT is to
use a different separator (| for example) and make sure that when you
import you specify the same separator.

--
Crittar

Chris Lawery
------------------------------------------------------------------------
Crittar's Profile: http://www.progresstalk.com/member.php?action=getinfo&userid=3271
View this thread: http://www.progresstalk.com/showthread.php?threadid=53177

 
 
 

1. Export Fixed Field Format Question

I am using the DTS Wizard (Import and Export Data utility) that is part
of MSSQL Server 7.0 for the purpose of dumping a flat fixed width
file.  The problem I am having is with some NULLable date format
fields.  If I am using delimiters the columns are correctly delimited
with the wizard, but in "Fixed Field" extracts the date fields that
contain NULLs are being dumped incorrectly.

Quick Example:
             (NULLable)          (NULLable)
SomeField1    Somedate1          Somedate2          Somefield2
(what I am getting)
BLAHBLAH1-1   1977-03-15 00:00:001977-03-15 00:00:00BLAHBLAH2-1
BLAHBLAH1-2   1977-03-15 00:00:00BLAHBLAH2-2
BLAHBLAH1-3   BLAHBLAH2-3

(what I want)
BLAHBLAH1-1   1977-03-15 00:00:001977-03-15 00:00:00BLAHBLAH2-1
BLAHBLAH1-2   1977-03-15 00:00:00                   BLAHBLAH2-2
BLAHBLAH1-3                                         BLAHBLAH2-3

So anyway, from the above you can see that I am not getting the desired
output.  Does anyone have an idea how to get this to format correctly
for me?  It is quite possible I am doing something wrong, which I hope
is the case.

Thanks in advance,

Sean

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Sundial Services PLEASE help

3. Export Formatted Memo Fields with formats

4. Some chars are replaced between Database and Recordset

5. Date format for Oracle Forms date field

6. Link Standard.exe to internet? & passwords???

7. Date Fields (Strings To Date Format)

8. dBASE IV Backups

9. Question BLINKing Fields and CLEARing Date field

10. Query datetime-field by date when field contains date + time

11. Replace part of Date field by another Date Field

12. Formatting Text fields for Export

13. Array row field by field