Re: merging 2 .csv files

El Thu, 29 Dec 2016 12:34:22 -0500
John Yeung <gallium.arsenide@xxxxxxxxx> escribió:

On Thu, Dec 29, 2016 at 12:08 PM, Smith, Mike
<Mike_Smith@xxxxxxxxxxxxxxxx> wrote:

Just to be clear this is how the data is showing up after the cat command
"FNAME","LNAME""SASA","ACKAR""JAMES","ADAMS""AMY","ADKINS""ERNEST","ADKINS"

This is how it should show up.
"FNAME","LNAME"
"MICHAEL","ALBERT"
"FRANCES","ALDERSON"
"JONATHAN","ALDRIDGE"

Actually, this is not the way to be clear. I mean, I can make an
educated guess, based on my experience and on your previous messages.
But your previous messages were clearer. The issue is, when you are
having problems with encoding, the clearest thing to do is give a hex
dump. Barring that, you need to at least say how you are looking at
the data (in other words, are you using Notepad, Excel, DSPF at a CL
prompt, cat command in QSH, etc.), because different methods will
render the data differently.

In one of your previous messages, you said your hex 0D0A line endings
became hex 0A. This is consistent with what I observed when using
methods which involve interpreting the data as text and then
potentially re-encoding it.

However, the -c option on the cat command should not be subject to
that, because it explicitly doesn't interpret the data. Please share
the EXACT command you are using. And be sure to use CHGATR to set the
CCSID of the output file to the same CCSID as the input files, after
the cat is complete. (The procedure isn't guaranteed to work if the
two CCSIDs on the input files are not the same as each other. It also
isn't guaranteed to work if the second file has a BOM; but if the only
CCSIDs you are seeing are 819 and 1208, and they are accurate, then
there shouldn't be any BOMs.)

John Y.

I have done this in the CLP by using 2 files: the first holds the header
and the content gets "reloaded" as needed. I have never encounter encoding problems by using this method with DDS.
The IBM supplied CpyToImpF features:
a) Encoding shortcuts (*STDASCII, *PCASCII).
b) *ADD or *REPLACE options allows to append to an existing stream file.




A) This is a one time process (init). Create the content table, the header
and pre-load the header. (I use SQL "create table", but CRTPFM with DDS can be used as well and it is my preferred method).

> create table QTEMP/CSVH (COLA char(64),COLB char(64),COLC char(64))
Table CSVH created in QTEMP.
> create table QTEMP/CSVD (COLA char(8),COLB numeric(9,2),COLC char(12
8))
Table CSVD created in QTEMP.

> insert into CSVH values(
'This is the heading for column A',
'This is the heading for column B',
'This is the heading for column C'
)
1 rows inserted in CSVH in QTEMP.

B) This is the content file. It will be loaded each run with records.
I loaded a couple of records manually to illustrate the process.
This could be a simplified form of the program LOADCSV (used below in the CLP):

> insert into CSVD values('ITEM-123',
6545.2,'This is a pair of pants that needs to be washed')
1 rows inserted in CSVD in QTEMP.
> insert into CSVD values('ITEM-456',
34.2,'This is a green hat')
1 rows inserted in CSVD in QTEMP.


C) The CLP:

Pgm

ClrPFM CSVD /* clear only the content, leave the header alone to be reused */

Call LOADCSV /* program to load the content and perhaps update the header if */
/* necessary (I inserted two records manually in this case) */

CpyToImpF FROMFILE(CSVH) TOSTMF('CSV.csv') MBROPT(*REPLACE) STMFCCSID(*STDASCII) STMFAUT(*INDIR) RCDDLM(*LF) RMVBLANK(*BOTH) /* always *REPLACE for the header */
CpyToImpF FROMFILE(CSVD) TOSTMF('CSV.csv') MBROPT(*ADD) STMFCCSID(*STMF) RCDDLM(*LF) RMVBLANK(*BOTH) /* use *ADD to keep the header */

EndPgm


=== result ===

Let's see how it looks:

call qp2term:

cat CSV.csv

"This is the heading for column A","This is the heading for column B"
,"This is the heading for column C"
"ITEM-123",6545.20,"This is a pair of pants that needs to be washed"
"ITEM-456",34.20,"This is a green hat"
$