Re: Convert Database file to CSV

On 20-Jul-2016 06:05 -0500, Rob Berendt wrote:

On 20-Jul-2016 05:50 -0500, Carel wrote:

On 19-Jul-2016 14:13 -0500, John Yeung wrote:

On Tue, Jul 19, 2016 at 11:13 AM, Carel wrote:

If you want column headings, you need a query with a UNION;
the numeric/decimal values in columns have therefore to be
converted to character (using DIGITS).

Not necessarily. Very recently there was a thread discussing the
ADDCOLNAM parameter on modern versions of CPYTOIMPF. It's kind
of underdocumented, but it's there when you press F9 to show all
parameters.

True. But:

- We do not know which OS version the OP is on.

- The ADDCOLNAM parameter will be using the field name (column
name) as defined. With DDS those could be cryptic with 10
characters max.

Adding your own column headings may be more informative.

Wasn't with DDS. It was with a Query output file. I guess that makes
it user controllable. Still have that 10 character limitation though
but it is something to consider.

Just as little is known about the file as with the VRM. The OP suggested only that the file was "created during a customer query". So not necessarily a Query either; i.e. the upper-cased term, typically implying WRKQRY or RUNQRY of Query/400.

In any case, the query [Query or SQL] will carry the long names [i.e. the CQE or SQE query maintains the "Alternative name" aka ALIAS]. Thus the use of ADDCOLNAM(*SYS) or ADDCOLNAM(*SQL) are possibly helpful.

Nevertheless, Carel's suggestion was for use of Column Headings. And whiel a ColHdg may be carried over in a Query/400 Query Definition (QRYDFN), I expect ColHdgs are not [at least are less likely to be] carried by an SQL query [at least not for which the Query/400 feature was not invoked to create a non-SQL object] and thus an output file would await the LABEL ON request(s) -- thus trying to include the ColHdgs in the export might be the least worthwhile, as they may all be NULL, or no better than ADDCOLNAM(*SQL) because IIRC, both the catalogs [and Display File Field Descriptions (DSPFFD)] are designed to return the long-name for lack of ColHdg per an effective COALESCE(colhgd, longColName).

And with regard to the query with UNION to get the column headings and having to cast the numeric data to character string data, e.g. in a VIEW that would be exported, much simpler IMO to generate the CSV string separately and export the TABLE and then append to that generated ColHdg data, the export of the data in the database file as generated by the CPYTOIMPF. The implied query would be doing [much of] the work that the export feature would be doing normally [i.e. converting to character], and then care must be taken to avoid loss of the decimal point and sign which DIGITS() would lose, and if more appropriately using CHAR or VARCHAR then depending on the recipient care might be necessary to avoid including a string delimiter on the numeric data.