How do I extract the data with column labels ?

[jchellap]

Hi all,

I want to do a simple extract from MFGPRO table so_mstr with all column labels.

The query i tried here is

output to so_mstr.csv.
/* Here I have to write the columns labels of so_mstr" */
output close.

Output to so_mstr.csv append.
for each so_mstr no-lock:
export delimiter "," so_mstr.
end.
output close.

Can anyone help me? Thanks in advance !

 

[rzr]

how many columns do you have in so_mstr table? If it's <15 or 20 you can manually "PUT" or "EXPORT" the column labels.
It is a large list then use the _file & _field tables to dynamically export the column-labels. I think the field to export will be _field._Col-label

 

[nineinch]

You have to take into account that so_mstr has some array fields like so_slspsn and so_comm_pct with only one label. Your query will export 4 fields instead of one for so_slspsn.

 

[Peter E]

DEFINE VARIABLE hBuf AS HANDLE NO-UNDO.
DEFINE VARIABLE hDBFld AS HANDLE NO-UNDO.

DEFINE VARIABLE llCheckFields AS LOGICAL NO-UNDO.
DEFINE VARIABLE lcFields AS CHARACTER NO-UNDO.
DEFINE VARIABLE lcElemExt AS CHARACTER NO-UNDO.
DEFINE VARIABLE liCnt AS INTEGER NO-UNDO.
DEFINE VARIABLE liCntExt AS INTEGER NO-UNDO.

DEFINE STREAM strExport.

/* *************************** Main Block *************************** */

hbuf = BUFFER so_mstr:HANDLE.

OUTPUT STREAM strExport TO "<filename>".

/* Print Header */
RUN ipHeader.


OUTPUT CLOSE.

/* ************ Internal Procedures *************** */
PROCEDURE ipHeader:
FOR FIRST so_mstr NO-LOCK
:
blFields:
REPEAT licnt = 1 TO hBuf:NUM-FIELDS
:
hDBFld = hBuf:BUFFER-FIELD(licnt).

/* Headers for the extents */
IF hDBFld:EXTENT GT 0 THEN
DO liCntExt = 1 TO hDBFld:EXTENT:
ASSIGN
lcElemExt = hDBFld:NAME + STRING(liCntExt).

PUT STREAM strExport UNFORMATTED
lcElemExt "<delimiter>".
END.
ELSE
PUT STREAM strExport UNFORMATTED
hDBFld:NAME "<delimiter>".
END.
/* SKIP to start next record on a new line. */
PUT STREAM strExport UNFORMATTED
SKIP.
END.
END.