Exporting Field Names?

[doom1701]

I don't do a lot of Progress development; mainly just quick and dirty apps for supporting our ERP system. Occasionally we have need of exporting an entire record from the ERP database to try to find some oddball piece of data. So we use:

output to <file>.
for each <table> where <criteria>:
export delimiter "," <table>.
end.

Works just fine, other than the fact that many of the ERP tables have hundreds of columns. We find that one thing that looks totally oddball...but we don't have a good way of knowing what field it actually is without going to the data dictionary and counting down fields.

Is there any way to export field names with this type of export? Obviously the perfect solution would be having a header row, but I'd even settle for exporting the field names (in order) separately or perhaps just preceeding each field with "fieldname:" or something.

 

[Casper]

Well then this is helpfull.


This program:

• exports 1 table or all tables
• use any delimiter you like
• can put field labels or field names as header
• can output a predefined list of fields

Comments are propably self explanatory.
Now it's time for weekend

DEFINE VARIABLE cDir AS CHARACTER  NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE     NO-UNDO.
DEFINE VARIABLE iTmp AS INTEGER    NO-UNDO.
DEFINE VARIABLE iTmp2 AS INTEGER     NO-UNDO.
DEFINE VARIABLE cFieldList AS CHARACTER NO-UNDO.
/* These fields will be exported if SomeFields preprocessor is defined comment preprocessor if you want to dump all fields */
ASSIGN cFieldList = 'custnum,name'.
&SCOPED-DEFINE SomeFields * 
/* comment/uncomment eiter of next two statements to put in the first row fieldnames or fieldlabels*/
/* if you don't want either of them comment both */
/* So if you want a header with the fieldnames then comment FieldLabel */
/* If you want a header with Field Labels then comment FileNames */
/* If you dont want header comment both  */
/* &SCOPED-DEFINE FieldNames *  */
&SCOPED-DEFINE FieldLabels *
 
/* change this if you want different Delimiter then semi-colon */
&SCOPED-DEFINE Delim ~';~'
/* comment this line to dump all tables or change tablename to dump another table */
&Scoped-define TABLE-NAME customer
/* output directory change this to antoher output directpry if needed */
ASSIGN cDir = 'C:\temp\'.
 
DEFINE STREAM sOut.
 
FOR EACH _file WHERE   &IF DEFINED(TABLE-NAME)
                       &THEN
                       _file._file-name = '{&TABLE-NAME}' AND
                       &endif
                       _file._hidden = NO NO-LOCK:
 
    OUTPUT STREAM sOut TO VALUE(cDir + _file._file-name + '.csv').
 
 
    CREATE BUFFER hBuffer FOR TABLE _file._file-name.
 
    CREATE QUERY hQuery.
 
    hQuery:ADD-BUFFER(hBuffer).
 
    hQuery:QUERY-PREPARE("FOR EACH " + hBuffer:NAME + " NO-LOCK").
 
    hQuery:QUERY-OPEN().
 
    hQuery:GET-FIRST().
 
    &IF DEFINED(FieldNames) > 0 &THEN
        DO iTmp = 1 TO hBuffer:NUM-FIELDS:
            &IF DEFINED (Somefields) > 0 &THEN
                IF LOOKUP(hBuffer:BUFFER-FIELD(iTmp):NAME,cFieldList) > 0 THEN
            &ENDIF
            PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):NAME {&Delim}.
        END.
        PUT STREAM sOut SKIP.
    &ELSEIF DEFINED(FieldLabels) > 0 &THEN
        DO iTmp = 1 TO hBuffer:NUM-FIELDS:
            &IF DEFINED (Somefields) > 0 &THEN
                IF LOOKUP(hBuffer:BUFFER-FIELD(iTmp):NAME,cFieldList) > 0 THEN
            &ENDIF
                PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):LABEL {&Delim}.
        END.
        PUT STREAM sOut SKIP.    
    &ENDIF
 
    DO WHILE NOT hQuery:QUERY-OFF-END:
 
        DO  iTmp = 1 TO hBuffer:NUM-FIELDS:
        &IF DEFINED(SomeFields) > 0 &THEN
          IF LOOKUP(hBuffer:BUFFER-FIELD(iTmp):NAME,cFieldList) > 0 THEN DO:
        &ENDIF
            IF hBuffer:BUFFER-FIELD(iTmp):EXTENT = 0
            THEN DO:
                IF hbuffer:BUFFER-FIELD(iTmp):DATA-TYPE = "CHARACTER"
                THEN PUT STREAM sOut UNFORMATTED '"' hBuffer:BUFFER-FIELD(iTmp):BUFFER-VALUE '"' {&Delim}.
                ELSE PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):BUFFER-VALUE {&Delim}.
            END.
            ELSE DO iTmp2 = 1 TO hBuffer:BUFFER-FIELD(iTmp):EXTENT: /* it is an extent field so we walk through the extents */
                IF hBuffer:BUFFER-FIELD(iTmp):DATA-TYPE = "CHARACTER"
                THEN PUT STREAM sOut UNFORMATTED '"' hBuffer:BUFFER-FIELD(iTmp):BUFFER-VALUE(iTmp2) '"' {&Delim}.
                ELSE PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):BUFFER-VALUE(iTmp2) {&Delim}.
            END.
        &IF DEFINED(SomeFields) > 0 &THEN
           END.
        &ENDIF
        END.
        PUT STREAM sOut SKIP.
 
        hQuery:GET-NEXT().
    END.
 
    /* cleanup and close */
    hQuery:QUERY-CLOSE().
    DELETE OBJECT hBuffer.
    DELETE OBJECT hQuery.
    OUTPUT STREAM sOut CLOSE.
 
END.

Casper.

 

[doom1701]

Cool...I think I can see what it is doing. You're my hero for the day.

It looks like I can probably add criteria for what records get exported by adding a variable (sCriteria) for my criteria and then just modifying this line

hQuery:QUERY-PREPARE("FOR EACH " + hBuffer:NAME + " NO-LOCK").

to this

assign sCriteria = "WHERE Table.Field = some value and...".
 
hQuery:QUERY-PREPARE("FOR EACH " + hBuffer:NAME + sCriteria + " NO-LOCK").

Does that sound right?

 

[Casper]

Yes, but don't forget to put spaces in between:

assign sCriteria = "WHERE Table.Field = some value and...".
 
hQuery:QUERY-PREPARE("FOR EACH " + hBuffer:NAME + "  "  + sCriteria + " NO-LOCK").

or start criteria with spaces

Casper

 

[cotton31]

Sorry for ressurrecting an old thread!

I am a complete n00b at Progress development - where would I run the above program? Do I need to change anything in relation to my database?

I only need a list of all table/field names in my database.

Cheers,