If you ever want to export an entire TABLE to a .CSV, Progress makes this simple to do. But exporting column headings for 100 or so fields is a PITA. This is a routine I wrote to generate the headings from the data dictionary metaschema.
I put this into an include file {pny/expHeadings.i} which I include wherever I need it.
def var vcHeading as char format "x(12)" extent 300 no-undo.
def var i as int no-undo.
procedure exp_headings.ip:
def input param pFile as char no-undo.
assign vcHeading = "" i = 0.
find _file where _file._file-name = pFile no-lock no-error.
if not avail _file then return error "Invalid filename: " + pFile.
for each _field no-lock of _file by _Order:
i = i + 1.
vcHeading[i] =
(if trim(_field._col-label) > "" then _field._col-label else
if trim(_field._label) > "" then _field._label else
_field._field-name).
if i >= 300 then leave.
end.
end procedure.
Syntax to export a table, e.g. customer ... I like to use named streams for exports to keep that data separate from the standard output:
DEF STREAM s-exp.
OUTPUT STREAM s-exp to "chris.csv".
RUN exp_headings.ip ("CUSTOMER").
/* write out the column headings */
EXPORT STREAM s-exp DELIMITER ",'" vcHeading.
/* export the corresponding data. Progress exports table fields in _order sequence */
FOR EACH customer NO-LOCK:
EXPORT STREAM s-exp DELIMITER "," CUSTOMER.
END.
OUTPUT STREAM s-exp CLOSE.
Note that if exporting a temp-table the DBRowIdent ROWID field cannot be exported directly (need STRING(DbRowIdent) to do this but then you would have to name every field. Progress makes this easy to do ...
EXPORT STREAM s-exp DELIMITER "," ttCustomer EXCEPT dbRowIdent.
You could list other fields to exclude too.
I often export temp-tables while testing to verify that the ROWMOD field has what I expect in a data or method directive, to see what the GetNew method populated, compare vs. a trace, etc.
I am working on a similar routine which will generate the column-headings for a temp-table by "walking the widget tree". Not ready for prime time yet. Most temp-tables are defined LIKE the DB table with some added fields. So to export ttCustomer RUN exp_headings.ip ("customer") and you'll have all but the extra fields. You can find out what they are if necessary by examining server/bo/customer/customer_ds.i.
One last little trick. By using preprocessor definition you can easily make the code above reusable for multiple tables, e.g. orderhed, orderdtl, orderrel. Like this:
&scoped-define dumpfile ShipHead
run exp_headings.ip ("{&DumpFile}").
output stream s-exp to value (vcEpiData + "{&DumpFile}.csv").
export stream s-exp delimiter "," vcHeading.
for each {&DumpFile} no-lock: export stream s-exp delimiter "," {&DumpFile}. end.
output stream s-exp close.
&scoped-define dumpfile ShipDtl
run exp_headings.ip ("{&DumpFile}").
output stream s-exp to value (vcEpiData + "{&DumpFile}.csv").
export stream s-exp delimiter "," vcHeading.
for each {&DumpFile} no-lock: export stream s-exp delimiter "," {&DumpFile}. end.
output stream s-exp close.
vcEpiData is defined in my standard.i include file (every program gets this) :
/* directives */
&global-define epidata \\erp-app1\EPICORDATA<file:///\\erp-app1\EPICORDATA>
&global-define epienv pilot
&global-define default_company PNY01
/* standard vars */
def var vcEpiData as char no-undo initial "{&epidata}\{&epienv}\".
So by changing the epienv definition vcEpiData will point to ..\pilot\, ..\prod\, ..\test\, etc. without changing any code.
Cheers!
Christopher Heins
Sr. Progress Programmer/Analyst
[cid:image001.png@01CC97B6.65916050]
Desk# 973-560-5370
Cell# 908-256-3662
Skype: cheins.nj
cheins@...<mailto:cheins@...>
________________________________
NOT INTENDED AS A SUBSTITUTE FOR A WRITING
NOTHING IN THIS E-MAIL, IN ANY E-MAIL THREAD OF WHICH IT MAY BE A PART, OR IN ANY ATTACHMENTS THERETO, SHALL CONSTITUTE A BINDING CONTRACT, OR ANY CONTRACTUAL OBLIGATION BY PNY, OR ANY INTENT TO ENTER INTO ANY BINDING OBLIGATIONS, NOTWITHSTANDING ANY ENACTMENT OF THE UNIFORM ELECTRONIC TRANSACTIONS ACT, THE FEDERAL E-SIGN ACT, OR ANY OTHER STATE OR FEDERAL LAW OF SIMILAR SUBSTANCE OR EFFECT. THIS EMAIL MESSAGE, ITS CONTENTS AND ATTACHMENTS ARE NOT INTENDED TO REPRESENT AN OFFER OR ACCEPTANCE OF AN OFFER TO ENTER INTO A CONTRACT. NOTHING IN THIS E-MAIL, IN ANY E-MAIL THREAD OF WHICH IT MAY BE A PART, OR IN ANY ATTACHMENTS THERETO SHALL ALTER THIS DISCLAIMER.
This e-mail message from PNY Technologies, Inc. is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
[Non-text portions of this message have been removed]
I put this into an include file {pny/expHeadings.i} which I include wherever I need it.
def var vcHeading as char format "x(12)" extent 300 no-undo.
def var i as int no-undo.
procedure exp_headings.ip:
def input param pFile as char no-undo.
assign vcHeading = "" i = 0.
find _file where _file._file-name = pFile no-lock no-error.
if not avail _file then return error "Invalid filename: " + pFile.
for each _field no-lock of _file by _Order:
i = i + 1.
vcHeading[i] =
(if trim(_field._col-label) > "" then _field._col-label else
if trim(_field._label) > "" then _field._label else
_field._field-name).
if i >= 300 then leave.
end.
end procedure.
Syntax to export a table, e.g. customer ... I like to use named streams for exports to keep that data separate from the standard output:
DEF STREAM s-exp.
OUTPUT STREAM s-exp to "chris.csv".
RUN exp_headings.ip ("CUSTOMER").
/* write out the column headings */
EXPORT STREAM s-exp DELIMITER ",'" vcHeading.
/* export the corresponding data. Progress exports table fields in _order sequence */
FOR EACH customer NO-LOCK:
EXPORT STREAM s-exp DELIMITER "," CUSTOMER.
END.
OUTPUT STREAM s-exp CLOSE.
Note that if exporting a temp-table the DBRowIdent ROWID field cannot be exported directly (need STRING(DbRowIdent) to do this but then you would have to name every field. Progress makes this easy to do ...
EXPORT STREAM s-exp DELIMITER "," ttCustomer EXCEPT dbRowIdent.
You could list other fields to exclude too.
I often export temp-tables while testing to verify that the ROWMOD field has what I expect in a data or method directive, to see what the GetNew method populated, compare vs. a trace, etc.
I am working on a similar routine which will generate the column-headings for a temp-table by "walking the widget tree". Not ready for prime time yet. Most temp-tables are defined LIKE the DB table with some added fields. So to export ttCustomer RUN exp_headings.ip ("customer") and you'll have all but the extra fields. You can find out what they are if necessary by examining server/bo/customer/customer_ds.i.
One last little trick. By using preprocessor definition you can easily make the code above reusable for multiple tables, e.g. orderhed, orderdtl, orderrel. Like this:
&scoped-define dumpfile ShipHead
run exp_headings.ip ("{&DumpFile}").
output stream s-exp to value (vcEpiData + "{&DumpFile}.csv").
export stream s-exp delimiter "," vcHeading.
for each {&DumpFile} no-lock: export stream s-exp delimiter "," {&DumpFile}. end.
output stream s-exp close.
&scoped-define dumpfile ShipDtl
run exp_headings.ip ("{&DumpFile}").
output stream s-exp to value (vcEpiData + "{&DumpFile}.csv").
export stream s-exp delimiter "," vcHeading.
for each {&DumpFile} no-lock: export stream s-exp delimiter "," {&DumpFile}. end.
output stream s-exp close.
vcEpiData is defined in my standard.i include file (every program gets this) :
/* directives */
&global-define epidata \\erp-app1\EPICORDATA<file:///\\erp-app1\EPICORDATA>
&global-define epienv pilot
&global-define default_company PNY01
/* standard vars */
def var vcEpiData as char no-undo initial "{&epidata}\{&epienv}\".
So by changing the epienv definition vcEpiData will point to ..\pilot\, ..\prod\, ..\test\, etc. without changing any code.
Cheers!
Christopher Heins
Sr. Progress Programmer/Analyst
[cid:image001.png@01CC97B6.65916050]
Desk# 973-560-5370
Cell# 908-256-3662
Skype: cheins.nj
cheins@...<mailto:cheins@...>
________________________________
NOT INTENDED AS A SUBSTITUTE FOR A WRITING
NOTHING IN THIS E-MAIL, IN ANY E-MAIL THREAD OF WHICH IT MAY BE A PART, OR IN ANY ATTACHMENTS THERETO, SHALL CONSTITUTE A BINDING CONTRACT, OR ANY CONTRACTUAL OBLIGATION BY PNY, OR ANY INTENT TO ENTER INTO ANY BINDING OBLIGATIONS, NOTWITHSTANDING ANY ENACTMENT OF THE UNIFORM ELECTRONIC TRANSACTIONS ACT, THE FEDERAL E-SIGN ACT, OR ANY OTHER STATE OR FEDERAL LAW OF SIMILAR SUBSTANCE OR EFFECT. THIS EMAIL MESSAGE, ITS CONTENTS AND ATTACHMENTS ARE NOT INTENDED TO REPRESENT AN OFFER OR ACCEPTANCE OF AN OFFER TO ENTER INTO A CONTRACT. NOTHING IN THIS E-MAIL, IN ANY E-MAIL THREAD OF WHICH IT MAY BE A PART, OR IN ANY ATTACHMENTS THERETO SHALL ALTER THIS DISCLAIMER.
This e-mail message from PNY Technologies, Inc. is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
[Non-text portions of this message have been removed]