dump data with column headers

[jumbosafari]

hello.
from within data administration (progress 9.1d on windows xp)
if i go to admin->dump data->table contents i'm able to dump the tables into csv files however they are missing the column headers.

is there any way to include the headers in the data dump?

 

[Casper]

With this code you can dump any (or all) table(s) from your database with or without header information in delimited file.

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

HTH,

Casper

 

[jumbosafari]

tried it and it worked perfectly!
thanks a lot!

the only thing is if it is an extents field then the headers are not there.
if you know of a way to include the extents headers than it would be perfect.

still works great.

 

[Casper]

Details, details :awink:

Try this:

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 STREAM sOut.
 
/* comment this line to dump all tables or change tablename to dump another table */
&Scoped-define TABLE-NAME SalesRep 
 
/* change this if you want different Delimiter then semi-colon */
&SCOPED-DEFINE Delim ~';~'
 
/* 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 */
&SCOPED-DEFINE FieldNames *
&SCOPED-DEFINE FielLabels * 
 
/* output directory */
ASSIGN cDir = 'C:\temp\'.
 
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 hBuffer:BUFFER-FIELD(iTMp):EXTENT = 0 THEN
         PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):NAME {&Delim}.
         ELSE DO iTmp2 = 1 TO hBuffer:BUFFER-FIELD(iTMp):EXTENT:
            PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):NAME iTmp2 {&Delim}.
        END.
    END.
    PUT STREAM sOut SKIP.
 &ELSEIF DEFINED(FielLabels) &THEN
    DO iTmp = 1 TO hBuffer:NUM-FIELDS:
        IF hBuffer:BUFFER-FIELD(iTMp):EXTENT = 0 THEN
        PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):LABEL {&Delim}.
        ELSE DO iTmp2 = 1 TO hBuffer:BUFFER-FIELD(iTMp):EXTENT:
            PUT STREAM sOut UNFORMATTED hBuffer:BUFFER-FIELD(iTmp):LABEL iTmp2 {&Delim}.
        END.
    END.
    PUT STREAM sOut SKIP.
 &ENDIF
 
 /* now the data */
 DO WHILE NOT hQuery:QUERY-OFF-END:
     DO  iTmp = 1 TO hBuffer:NUM-FIELDS:
        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.
     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

 

[jumbosafari]

my friend your the best.

it's as good as gold.

 

[TomBascom]

If this is working it means that you do actually have a compiler license so the data dictionary dump routines also should have been working for you...