Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Don Johnson]

Kolusu, thank you for putting this together. I am hopeful to be able to try it 
out today, but then will be away through next week.

Let me ask one question here. Would it be easier if I changed the separator 
from a comma to a tilde? If so, then the output looks like this
ITM_ID~DESC~SHORT_DESC~U_M~UNIT_PRICE~ON_HAND~COMMIT~INV_HOLD~DISC_QTY~DISC_PCT~B_O_QTY~O
CHAR(10) N.N.~CHAR(30) NOT NULL~CHAR(8) N.N.~CHAR(4) N.N.~DEC(7,2) 
N.N.~DEC(7,0) N.N.~DEC
A1~CARPETED RUBBER 
MATS-FRONT-RED~FLOORMAT~PAIR~22.99~135~205~Y~5000~1.5~0~0~87~02~02
A10001~CARPETED RUBBER 
MATS-REAR(RED)~FLOORMAT~PAIR~12.99~277~14~N~250~1.5~0~0~86~02~20  
A10002~PERSONALIZED VINYL 
MATS(BEIGE)~FLOORMAT~PAIR~19.99~296~7~N~250~1.5~0~0~87~02~03   

That seems to make it easier to get around the comma problem.

What do you think?
Don

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

Thanks Sri. I did cut a corner but simple to add the other data types.

On Sun, Mar 27, 2022, 02:52 Sri h Kolusu  wrote:

> >> I couldn't resist. Here's a REXX solution.
>
> Wayne,
>
> Thanks for the Rexx exec. One change you may want to is that you don't
> have to check for 'DEC'  as there can be other numeric formats like
> "smallinit" , "Integer" "Float" and different date and time fields.
> So checking for CHAR or VARCHAR should be enough and everything other than
> them would be in ELSE part.
>
> Thanks,
> Kolusu
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Sri h Kolusu]

>> I couldn't resist. Here's a REXX solution.

Wayne,

Thanks for the Rexx exec. One change you may want to is that you don't have to 
check for 'DEC'  as there can be other numeric formats like "smallinit" , 
"Integer" "Float" and different date and time fields.
So checking for CHAR or VARCHAR should be enough and everything other than them 
would be in ELSE part.
 
Thanks,
Kolusu

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

I couldn't resist. Here's a REXX solution. Should work for any layout which
is defined in the manner of the original request, although my test data
only has the header in the first record.



On Thu, Mar 24, 2022 at 7:47 AM Don Johnson <
02ee771a0785-dmarc-requ...@listserv.ua.edu> wrote:

> This is a post now to the listserv, instead of the Google group. Sorry for
> the duplication!
>
> Hi, I have a comma-delimited extract from a file that has numeric and
> character fields, and I would like to turn it into a true CSV file by
> making the character fields quoted.
>
> I have a 2-line header (column names, and column types) which indicates
> which are CHAR fields, but cannot figure out how to capture the information
> from the header to apply to the actual data lines.
>
> For example, I have this in my file:
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
>
> CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
> A1,CARPETED RUBBER
> MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> A10001,CARPETED RUBBER
> MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> A10002,PERSONALIZED VINYL
> MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> A10004,SPLASH
> GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> A10005,SPLASH
> GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> A10006,MONOGRAMMED SPLASH
> GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
>
> and want the output to look like this:
> "A1","CARPETED RUBBER
> MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> "A10001","CARPETED RUBBER
> MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> "A10002","PERSONALIZED VINYL
> MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> "A10004","SPLASH
> GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
>
> "A10005","SPLASH
> GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
>
> "A10006","MONOGRAMMED SPLASH
> GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
>
> Is there a way to see which column type contains CHAR( -- each of the
> types is column separated -- and then be able to apply quotes to that
> particular output field? I am not sure about this, but hope there is an
> answer here.
>
> Thank you for your help!
> Don Johnson
> Sr. Principal Support Engineer  |  MSD - Datacom product family
> Broadcom Software
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
/* REXX  */
/* MODIFY A COMMA DELIMITED FILE USING FILE TAILORING */
/* INITIAL ROUTINE TO PARSE THE HEADER*/
SAY 'ENTER LISTDS'
PULL LISTDS
SAY 'ENTER OFIL  '
PULL OFIL
"ALLOC F(IFILE) DA('"LISTDS"') SHR REUSE "
/* Read Input file */
  "EXECIO * DISKR  IFILE(STEM REC. FINIS "
"FREE  F(IFILE) "
F. = ''
F.0 = 0

Member = Strip(REC.1)
/* The header record defines the attributes of the columns in the next
   records */
/* Example
ID,NAME,ADDR,SUBURB,ZIP,SOCSEC DEC(5,0),CHAR(20),CHAR(20),CHAR(20),DEC(4),DEC(10
*/
K = 0
Do I = 1 To Length(REC.1)
  If Substr(REC.1,I,3) = "CHA" Then Do
 K = K + 1
 F.K = '"'||K||'",'
  End
  Else If Substr(REC.1,I,3) = "DEC" Then Do
 K = K + 1
 F.K = ''||K||','
  End
End
L = Length(F.K)-1 /* Take off one byte */
F.K = Substr(F.K,1,L) /* Remove trailing Comma */
SLIB  = ''
Ptail = ''
Do J = 1 to K
   SLIB = SLIB || F.J
   Ptail  = Ptail ' F'||J||"','"
End
Pstmt = 'PARSE VALUE MEMBER WITH ' Ptail
/* Allocate a generated PDS file name */

"ALLOC F(FILEIN) DA('BDB204.SKEL.SOURCE(CAFILE)') SHR REUSE"

/* Assign the contents */

R1.1 = SLIB

/* Write and close the file*/
"EXECIO 1 DISKW FILEIN (STEM R1. FINIS)"
"ISPEXEC LIBDEF ISPSLIB DATASET ID('BDB204.SKEL.SOURCE')"
   CALL AllocPDS(ofil 10 10 300 CYL)
   ADDRESS ISPEXEC
   Do k = 2 to REC.0
  Member = Strip(REC.k)
  Omem = 'CAFILE'
  INTERPRET Pstmt
  "ISPEXEC LIBDEF ISPFILE DATASET ID('"ofil"')"
  ADDRESS ISPEXEC  'FTINCL ' CAFILE
   End
   ADDRESS ISPEXEC 'FTCLOSE NAME('omem')   '
"ISPEXEC EDIT DATASET ('"ofil"') MACRO(NONE) "
Exit
Allocpds:
ARG iparm

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

Hi Sri,

Yes, I took a few shortcuts!

If I were to build a complete solution, I would parse the attributes and
dynamically build the file tailoring skeleton.

The underlying data comes from CA (Broadcoms) Datacom sample database. I
expect that similar table extracts may be required for porting to other
environments. The Datacom product range has many tools to facilitate this.
Their Datadictionary utilities can produce a variety of copy book
definitions and some of these provide a simpler mapping to attributes of
columns in a table. That would be my approach to build a general solution.

Your sort solution is great though...


On Fri, Mar 25, 2022 at 12:38 PM Sri h Kolusu  wrote:

> >>"","","","",,
>
> Wayne,
>
> Isn't this assuming that the first 4 columns are character and you are
> embedding the quotes ? Also did you delete off the first 2 records (the
> headers and column attributes) ?
>
> I believe Op's requirement is to determine the column attributes based on
> the 2nd record of the file and add quotes based on the requirement.
>
> Thanks,
> Kolusu
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Sri h Kolusu]

>>"","","","",,

Wayne,

Isn't this assuming that the first 4 columns are character and you are 
embedding the quotes ? Also did you delete off the first 2 records (the headers 
and column attributes) ? 

I believe Op's requirement is to determine the column attributes based on the 
2nd record of the file and add quotes based on the requirement. 

Thanks,
Kolusu

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Sri h Kolusu]

Hi,

Was busy and couldn't look at this right away. There are a few monkey wrenches 
in processing the data. You have some columns which had spaces in between the 
data and the column attributes having comma's added complications to parsing 
the data as a comma separator.
Either way here is a job that would give you the desired results. I added 
comments so that you can follow and easy to modify.

I am attaching the solution as a text file . 

Let me know if you have any have further questions.

Thanks,
Kolusu
DFSORT Development
IBM Corporation

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*  
//SYMNOUT  DD SYSOUT=*  
//INA  DD DISP=SHR,DSN=Your.input.VB.csv.file
//INB  DD DISP=SHR,DSN=Same.input.VB.csv.file
/*  
//SORTOUT  DD DSN=Your.output.vb.csv.file,  
   
//DISP=(NEW,CATLG,DELETE),  
//SPACE=(CYL,(X,Y),RLSE)
/*
//SYMNAMES DD * 
FLD-MAX-LENGTH,33   # Max len of field including quotes and separator   
ARRAY-COUNT,20  # MAX NUMBER OF COLUMNS 
** Note : we assumed that we have a max of 20 columns, so we defined
**20 flag indicators and 20 field-values. If you more fields
**then make sure you define additional QTInn and FLDnn  
**symbols also  
**  
TOT-FLD-WIDTH,660   # FLD-MAX-LENGTH X ARRAY-COUNT (33 X 20 = 660)  
** Note : The REFORMAT statment does not allow symbols, so if   
**change the tot-fld-width, make sure you change it on the  
**reformat statement too.   
**  
JOIN-IND,01,01,CH   
QTI01,*,01,CH   
QTI02,*,01,CH   
QTI03,*,01,CH   
QTI04,*,01,CH   
QTI05,*,01,CH   
QTI06,*,01,CH   
QTI07,*,01,CH   
QTI08,*,01,CH   
QTI09,*,01,CH   
QTI10,*,01,CH   
QTI11,*,01,CH   
QTI12,*,01,CH   
QTI13,*,01,CH   
QTI14,*,01,CH   
QTI15,*,01,CH   
QTI16,*,01,CH   
QTI17,*,01,CH   
QTI18,*,01,CH   
QTI19,*,01,CH   
QTI20,*,01,CH   
ORIG-DATA-BEGIN,*   
FLD01,*,33,CH   
FLD02,*,33,CH   
FLD03,*,33,CH   
FLD04,*,33,CH   
FLD05,*,33,CH   
FLD06,*,33,CH   
FLD07,*,33,CH   
FLD08,*,33,CH   
FLD09,*,33,CH   
FLD10,*,33,CH  

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

Don,

ISPF file tailoring can easily handle this.

I have a generic panel driven program and every time I have a list to
manipulate, I just build a new skeleton and run it against the list.

In your case, this would be your VB input file.

The prototype skeleton I used looks like this:

))) SLIB CAFILE
"","","","",,

I only worked on the first 6 columns. It does demonstrate CHAR and NUMERIC
handling.

In my REXX list processor, I just parse out those first 6 columns:

  When Skelmem = 'CAFILE' Then Do
   Omem = 'CAFILE'
PARSE VALUE MEMBER WITH F1 ',' F2 ',' F3 ',' F4 ',' F5 ',' F6
   ADDRESS ISPEXEC
   "ISPEXEC LIBDEF ISPFILE DATASET ID('"ofil"')"
   ADDRESS ISPEXEC  'FTINCL ' CAFILE
  End

The resulting output file VB.OUTPUT(CAFILE)

"A1","CARPETED RUBBER MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135

This is what my panel driver looks like, you just add in a new skeleton if
needed.

 Build JCL from Skeleton 
OPTION  ===>

 Specify Output PDS Name ===> BDB204.VB.OUTPUT



 List Dataset===> BDB204.CAFILE

 SKEL Member ===> CAFILE

DEFMODEL ... Your library of choice
DEFJC... Your library of choice
COPT ... SYS3.OMEGAMON.LPAR.V5R1.RKD2PAR
DTCLASS  ... NEW RACF CLASS FOR ICHERCDE
RKANPARU ... SYS3.OMEGAMON.LPAR.V5R1.RKANPARU(LPAR)
KCIRSV2  ... SYS3.OMEGAMON.LPAR.V5R1.RKANCMD
EQQICNVH ... Migrate IBM Workload Scheduler

 LPAR===> PROD





On Fri, Mar 25, 2022 at 12:15 AM Don Johnson <
02ee771a0785-dmarc-requ...@listserv.ua.edu> wrote:

> Kolusu, here are the first 3 column definitions:
> CHAR(10) N.N.,
> CHAR(30) NOT NULL,
> CHAR(8) N.N.,
>
> As you can see, each of the columns is separated by a comma, which is
> outside of the parens. The N.N. is an abbreviation for Not Null, and the
> abbreviation is used when the column width is less than the width of that
> descriptor. In terms of separation of the columns, I believe that all the
> fields have NN or Not Null, so you can only distinguish each column
> attribute by "NULL," or "N.N.," here. Another option is to convert ") N.N."
> to ") NOT NULL" or vice-versa, if that makes parsing easier.
>
> As to the input file, it is VB, and the max data record length is 200 (so,
> VB-204). There are actually several files, but 200 is the longest.
>
> I could write a Rexx program to handle this, but if Sort could do it, it
> would be much simpler.
>
> Thanks again!
> Don
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Don Johnson]

Wayne, thanks for these great solution ideas - they give me other ideas, too. I 
might try the Datasource for Excel option for another need I have. The IBM DB2 
Rexx is interesting, and I will have to see if I could adapt it to my Datacom 
table (at first blush, it seems too DB2-centric).

Thanks again for these ideas.
Don

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Don Johnson]

Kolusu, here are the first 3 column definitions:
CHAR(10) N.N.,
CHAR(30) NOT NULL,
CHAR(8) N.N.,

As you can see, each of the columns is separated by a comma, which is outside 
of the parens. The N.N. is an abbreviation for Not Null, and the abbreviation 
is used when the column width is less than the width of that descriptor. In 
terms of separation of the columns, I believe that all the fields have NN or 
Not Null, so you can only distinguish each column attribute by "NULL," or 
"N.N.," here. Another option is to convert ") N.N." to ") NOT NULL" or 
vice-versa, if that makes parsing easier.

As to the input file, it is VB, and the max data record length is 200 (so, 
VB-204). There are actually several files, but 200 is the longest.

I could write a Rexx program to handle this, but if Sort could do it, it would 
be much simpler.

Thanks again!
Don

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

Don,

Microsoft Powershell will add quotes around a comma delimited field.
However, it does it for all fields, not just Alphanumeric.

It's a free download.

This example command does the quote addition:
import-csv C:\Users\wayne\Documents\book2.csv | export-csv
C:\Users\wayne\Documents\book3.csv  -NoTypeInformation -Encoding UTF8

In REXX, you can parse out each field from your CSV and use the STRIP
function to take out the words that are numeric.
This file:
a, 1,   2, three
abc, 5, 3, def
hgi, 4, 6, ttt

Becomes:
"a","1","2","three"
"abc","5","3","def"
"hgi","4","6","ttt"

PARSE VALUE mystring with F1 ',' F2','  F3 ',' F4
F2 = STRIP(F2,,'"')
F4 = STRIP(F4,,'"')

OUTSTR = F1 F2 F3 F4

Having said all this, I'd just PARSE your original records and add the
quotes word by word where needed. They are in known positions, so you know
which ones to STRIP and rebuild.






On Thu, Mar 24, 2022 at 7:47 AM Don Johnson <
02ee771a0785-dmarc-requ...@listserv.ua.edu> wrote:

> This is a post now to the listserv, instead of the Google group. Sorry for
> the duplication!
>
> Hi, I have a comma-delimited extract from a file that has numeric and
> character fields, and I would like to turn it into a true CSV file by
> making the character fields quoted.
>
> I have a 2-line header (column names, and column types) which indicates
> which are CHAR fields, but cannot figure out how to capture the information
> from the header to apply to the actual data lines.
>
> For example, I have this in my file:
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
>
> CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
> A1,CARPETED RUBBER
> MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> A10001,CARPETED RUBBER
> MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> A10002,PERSONALIZED VINYL
> MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> A10004,SPLASH
> GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> A10005,SPLASH
> GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> A10006,MONOGRAMMED SPLASH
> GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
>
> and want the output to look like this:
> "A1","CARPETED RUBBER
> MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> "A10001","CARPETED RUBBER
> MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> "A10002","PERSONALIZED VINYL
> MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> "A10004","SPLASH
> GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
>
> "A10005","SPLASH
> GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
>
> "A10006","MONOGRAMMED SPLASH
> GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
>
> Is there a way to see which column type contains CHAR( -- each of the
> types is column separated -- and then be able to apply quotes to that
> particular output field? I am not sure about this, but hope there is an
> answer here.
>
> Thank you for your help!
> Don Johnson
> Sr. Principal Support Engineer  |  MSD - Datacom product family
> Broadcom Software
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

Ack, Export to CSV for MS-DOS only appears to work on Mac, not IBM PC.

On Thu, Mar 24, 2022 at 7:47 AM Don Johnson <
02ee771a0785-dmarc-requ...@listserv.ua.edu> wrote:

> This is a post now to the listserv, instead of the Google group. Sorry for
> the duplication!
>
> Hi, I have a comma-delimited extract from a file that has numeric and
> character fields, and I would like to turn it into a true CSV file by
> making the character fields quoted.
>
> I have a 2-line header (column names, and column types) which indicates
> which are CHAR fields, but cannot figure out how to capture the information
> from the header to apply to the actual data lines.
>
> For example, I have this in my file:
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
>
> CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
> A1,CARPETED RUBBER
> MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> A10001,CARPETED RUBBER
> MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> A10002,PERSONALIZED VINYL
> MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> A10004,SPLASH
> GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> A10005,SPLASH
> GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> A10006,MONOGRAMMED SPLASH
> GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
>
> and want the output to look like this:
> "A1","CARPETED RUBBER
> MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> "A10001","CARPETED RUBBER
> MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> "A10002","PERSONALIZED VINYL
> MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> "A10004","SPLASH
> GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
>
> "A10005","SPLASH
> GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
>
> "A10006","MONOGRAMMED SPLASH
> GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
>
> Is there a way to see which column type contains CHAR( -- each of the
> types is column separated -- and then be able to apply quotes to that
> particular output field? I am not sure about this, but hope there is an
> answer here.
>
> Thank you for your help!
> Don Johnson
> Sr. Principal Support Engineer  |  MSD - Datacom product family
> Broadcom Software
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

Hi Don,

Since you have access to Broadcom software. Why don't you use your table as
a Datasource utilizing Datacom Server and export to CSV from spreadsheet?

On Thu, Mar 24, 2022 at 7:47 AM Don Johnson <
02ee771a0785-dmarc-requ...@listserv.ua.edu> wrote:

> This is a post now to the listserv, instead of the Google group. Sorry for
> the duplication!
>
> Hi, I have a comma-delimited extract from a file that has numeric and
> character fields, and I would like to turn it into a true CSV file by
> making the character fields quoted.
>
> I have a 2-line header (column names, and column types) which indicates
> which are CHAR fields, but cannot figure out how to capture the information
> from the header to apply to the actual data lines.
>
> For example, I have this in my file:
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
>
> CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
> A1,CARPETED RUBBER
> MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> A10001,CARPETED RUBBER
> MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> A10002,PERSONALIZED VINYL
> MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> A10004,SPLASH
> GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> A10005,SPLASH
> GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> A10006,MONOGRAMMED SPLASH
> GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
>
> and want the output to look like this:
> "A1","CARPETED RUBBER
> MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> "A10001","CARPETED RUBBER
> MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> "A10002","PERSONALIZED VINYL
> MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> "A10004","SPLASH
> GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
>
> "A10005","SPLASH
> GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
>
> "A10006","MONOGRAMMED SPLASH
> GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
>
> Is there a way to see which column type contains CHAR( -- each of the
> types is column separated -- and then be able to apply quotes to that
> particular output field? I am not sure about this, but hope there is an
> answer here.
>
> Thank you for your help!
> Don Johnson
> Sr. Principal Support Engineer  |  MSD - Datacom product family
> Broadcom Software
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

The IBM sample REXX is here:

https://www.ibm.com/docs/en/db2-for-zos/11?topic=examples-sample-db2-rexx-application

The DRAW function builds this:

INSERT INTO SAN_JOSE.DSN8B10.EMP ( "EMPNO" , "FIRSTNME" , "MIDINIT" ,
"LASTNAME" , "WORKDEPT" , "PHONENO" , "HIREDATE" , "JOB" , "EDLEVEL" ,
"SEX" , "BIRTHDATE" , "SALARY" , "BONUS" , "COMM" ) VALUES ( -- ENTER
VALUES BELOW COLUMN NAME DATA TYPE , -- EMPNO CHAR(6) NOT NULL , --
FIRSTNME VARCHAR(12) NOT NULL , -- MIDINIT CHAR(1) NOT NULL , -- LASTNAME
VARCHAR(15) NOT NULL , -- WORKDEPT CHAR(3) , -- PHONENO CHAR(4) , --
HIREDATE DATE , -- JOB CHAR(8) , -- EDLEVEL SMALLINT , -- SEX CHAR(1) , --
BIRTHDATE DATE , -- SALARY DECIMAL(9,2) , -- BONUS DECIMAL(9,2) ) -- COMM
DECIMAL(9,2)

On Thu, Mar 24, 2022 at 7:47 AM Don Johnson <
02ee771a0785-dmarc-requ...@listserv.ua.edu> wrote:

> This is a post now to the listserv, instead of the Google group. Sorry for
> the duplication!
>
> Hi, I have a comma-delimited extract from a file that has numeric and
> character fields, and I would like to turn it into a true CSV file by
> making the character fields quoted.
>
> I have a 2-line header (column names, and column types) which indicates
> which are CHAR fields, but cannot figure out how to capture the information
> from the header to apply to the actual data lines.
>
> For example, I have this in my file:
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
>
> CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
> A1,CARPETED RUBBER
> MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> A10001,CARPETED RUBBER
> MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> A10002,PERSONALIZED VINYL
> MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> A10004,SPLASH
> GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> A10005,SPLASH
> GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> A10006,MONOGRAMMED SPLASH
> GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
>
> and want the output to look like this:
> "A1","CARPETED RUBBER
> MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> "A10001","CARPETED RUBBER
> MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> "A10002","PERSONALIZED VINYL
> MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> "A10004","SPLASH
> GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
>
> "A10005","SPLASH
> GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
>
> "A10006","MONOGRAMMED SPLASH
> GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
>
> Is there a way to see which column type contains CHAR( -- each of the
> types is column separated -- and then be able to apply quotes to that
> particular output field? I am not sure about this, but hope there is an
> answer here.
>
> Thank you for your help!
> Don Johnson
> Sr. Principal Support Engineer  |  MSD - Datacom product family
> Broadcom Software
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Wayne Bickerdike]

I see this is perhaps from the Datacom ITM table, supplied in the sample
database?

There is an IBM sample REXX program which reads the attributes of a SQL
table and will build a template INSERT statement.

If you could use this utility, it will give you the syntax that you need.

I have a copy of this, I'll add some context later when I log on.

On Thu, Mar 24, 2022 at 1:23 PM Robin Vowels  wrote:

> The list of attributes are obviously PL/I, and that suggests that
> PL/I could be the means of solving the problem.
>
> On 2022-03-24 08:20, Horacio Luis Villa wrote:
> > Hi,
> >
> > the last 3 columns are CHAR but you don't want them quoted?
> > Don't know how to do it using SORT. I'd do it with Rexx.
> > 
> > De: IBM Mainframe Discussion List  en nombre
> > de Don Johnson <02ee771a0785-dmarc-requ...@listserv.ua.edu>
> > Enviado: miércoles, 23 de marzo de 2022 17:46
> > Para: IBM-MAIN@LISTSERV.UA.EDU 
> > Asunto: [EXTERNAL] Using SORT to add quotes around CHAR fields?
> > (Listserv)
> >
> > This is a post now to the listserv, instead of the Google group. Sorry
> > for the duplication!
> >
> > Hi, I have a comma-delimited extract from a file that has numeric and
> > character fields, and I would like to turn it into a true CSV file by
> > making the character fields quoted.
> >
> > I have a 2-line header (column names, and column types) which
> > indicates which are CHAR fields, but cannot figure out how to capture
> > the information from the header to apply to the actual data lines.
> >
> > For example, I have this in my file:
> >
> ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
> > CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
> > N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
> > N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2)
> > N.N.
> > A1,CARPETED RUBBER
> > MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
> > A10001,CARPETED RUBBER
> > MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
> > A10002,PERSONALIZED VINYL
> > MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
> > A10003,4-PIECE CARPET MAT SET (BLUE),MAT
> > SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
> > A10004,SPLASH
> > GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
> > A10005,SPLASH
> > GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
> > A10006,MONOGRAMMED SPLASH
> > GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20
> >
> > and want the output to look like this:
> > "A1","CARPETED RUBBER
> > MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
> > "A10001","CARPETED RUBBER
> > MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
> > "A10002","PERSONALIZED VINYL
> > MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
> > "A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
> > SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
> > "A10004","SPLASH
> > GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
> > "A10005","SPLASH
> > GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
> > "A10006","MONOGRAMMED SPLASH
> > GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20
> >
> > Is there a way to see which column type contains CHAR( -- each of the
> > types is column separated -- and then be able to apply quotes to that
> > particular output field? I am not sure about this, but hope there is
> > an answer here.
> >
> > Thank you for your help!
> > Don Johnson
> > Sr. Principal Support Engineer  |  MSD - Datacom product family
> > Broadcom Software
>
> --
> For IBM-MAIN subscribe / signoff / archive access instructions,
> send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
>


-- 
Wayne V. Bickerdike

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Robin Vowels]

The list of attributes are obviously PL/I, and that suggests that
PL/I could be the means of solving the problem.

On 2022-03-24 08:20, Horacio Luis Villa wrote:

Hi,

the last 3 columns are CHAR but you don't want them quoted?
Don't know how to do it using SORT. I'd do it with Rexx.

De: IBM Mainframe Discussion List  en nombre
de Don Johnson <02ee771a0785-dmarc-requ...@listserv.ua.edu>
Enviado: miércoles, 23 de marzo de 2022 17:46
Para: IBM-MAIN@LISTSERV.UA.EDU 
Asunto: [EXTERNAL] Using SORT to add quotes around CHAR fields? 
(Listserv)


This is a post now to the listserv, instead of the Google group. Sorry
for the duplication!

Hi, I have a comma-delimited extract from a file that has numeric and
character fields, and I would like to turn it into a true CSV file by
making the character fields quoted.

I have a 2-line header (column names, and column types) which
indicates which are CHAR fields, but cannot figure out how to capture
the information from the header to apply to the actual data lines.

For example, I have this in my file:
ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2)
N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1)
N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2)
N.N.
A1,CARPETED RUBBER
MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
A10001,CARPETED RUBBER
MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
A10002,PERSONALIZED VINYL
MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
A10003,4-PIECE CARPET MAT SET (BLUE),MAT
SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
A10004,SPLASH 
GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
A10005,SPLASH 
GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03

A10006,MONOGRAMMED SPLASH
GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20

and want the output to look like this:
"A1","CARPETED RUBBER
MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
"A10001","CARPETED RUBBER
MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
"A10002","PERSONALIZED VINYL
MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
"A10003","4-PIECE CARPET MAT SET (BLUE)","MAT
SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
"A10004","SPLASH
GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
"A10005","SPLASH
GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
"A10006","MONOGRAMMED SPLASH
GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20

Is there a way to see which column type contains CHAR( -- each of the
types is column separated -- and then be able to apply quotes to that
particular output field? I am not sure about this, but hope there is
an answer here.

Thank you for your help!
Don Johnson
Sr. Principal Support Engineer  |  MSD - Datacom product family
Broadcom Software


--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Horacio Luis Villa]

Hi,

the last 3 columns are CHAR but you don't want them quoted?
Don't know how to do it using SORT. I'd do it with Rexx.

De: IBM Mainframe Discussion List  en nombre de Don 
Johnson <02ee771a0785-dmarc-requ...@listserv.ua.edu>
Enviado: miércoles, 23 de marzo de 2022 17:46
Para: IBM-MAIN@LISTSERV.UA.EDU 
Asunto: [EXTERNAL] Using SORT to add quotes around CHAR fields? (Listserv)

This is a post now to the listserv, instead of the Google group. Sorry for the 
duplication!

Hi, I have a comma-delimited extract from a file that has numeric and character 
fields, and I would like to turn it into a true CSV file by making the 
character fields quoted.

I have a 2-line header (column names, and column types) which indicates which 
are CHAR fields, but cannot figure out how to capture the information from the 
header to apply to the actual data lines.

For example, I have this in my file:
ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2) 
N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1) 
N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
A1,CARPETED RUBBER 
MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
A10001,CARPETED RUBBER 
MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20
A10002,PERSONALIZED VINYL 
MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03
A10003,4-PIECE CARPET MAT SET (BLUE),MAT 
SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03
A10004,SPLASH GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03
A10005,SPLASH GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03
A10006,MONOGRAMMED SPLASH 
GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20

and want the output to look like this:
"A1","CARPETED RUBBER 
MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
"A10001","CARPETED RUBBER 
MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20
"A10002","PERSONALIZED VINYL 
MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03
"A10003","4-PIECE CARPET MAT SET (BLUE)","MAT 
SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03
"A10004","SPLASH 
GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03
"A10005","SPLASH 
GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
"A10006","MONOGRAMMED SPLASH 
GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20

Is there a way to see which column type contains CHAR( -- each of the types is 
column separated -- and then be able to apply quotes to that particular output 
field? I am not sure about this, but hope there is an answer here.

Thank you for your help!
Don Johnson
Sr. Principal Support Engineer  |  MSD - Datacom product family
Broadcom Software

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: Using SORT to add quotes around CHAR fields? (Listserv)

[Sri h Kolusu]

>> I have a 2-line header (column names, and column types) which indicates 
>> which are CHAR fields, but cannot figure out how to capture the information 
>> from the header to apply to the actual data lines.
>>  Is there a way to see which column type contains CHAR( -- each of the types 
>> is column separated -- and then be able to apply quotes to that particular 
>> output field? I am not sure about this, but hope there is an answer here.
Don,

If your intention is to just enclose the character fields with double quotes, 
It can be done, however need couple of clarifications. 

1. What is the LRECL and RECFM of the input dataset?
2. Looking at the data, it seems to comma separated data, however the 2nd 
header line that has the db2 column attributes has numeric fields which has the 
precision separated with a comma. Is there anything special to distinguish the 
attributes? What does N.N mean? Can I use that as separator? 


Thanks,
Kolusu
DFSORT Development
IBM Corporation


--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Using SORT to add quotes around CHAR fields? (Listserv)

[Don Johnson]

This is a post now to the listserv, instead of the Google group. Sorry for the 
duplication!

Hi, I have a comma-delimited extract from a file that has numeric and character 
fields, and I would like to turn it into a true CSV file by making the 
character fields quoted.

I have a 2-line header (column names, and column types) which indicates which 
are CHAR fields, but cannot figure out how to capture the information from the 
header to apply to the actual data lines.

For example, I have this in my file:
ITM_ID,DESC,SHORT_DESC,U_M,UNIT_PRICE,ON_HAND,COMMIT,INV_HOLD,DISC_QTY,DISC_PCT,B_O_QTY,ON_ORD,ACT_YR,ACT_MO,ACT_DAY
  
CHAR(10) N.N.,CHAR(30) NOT NULL,CHAR(8) N.N.,CHAR(4) N.N.,DEC(7,2) 
N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(1) N.N.,DEC(7,0) N.N.,DEC(3,1) 
N.N.,DEC(7,0) N.N.,DEC(7,0) N.N.,CHAR(2) N.N.,CHAR(2) N.N.,CHAR(2) N.N.
A1,CARPETED RUBBER 
MATS-FRONT-RED,FLOORMAT,PAIR,22.99,135,205,Y,5000,1.5,0,0,87,02,02
A10001,CARPETED RUBBER 
MATS-REAR(RED),FLOORMAT,PAIR,12.99,277,14,N,250,1.5,0,0,86,02,20  
A10002,PERSONALIZED VINYL 
MATS(BEIGE),FLOORMAT,PAIR,19.99,296,7,N,250,1.5,0,0,87,02,03  
A10003,4-PIECE CARPET MAT SET (BLUE),MAT 
SET,SET,19.99,275,2,N,250,1.5,0,0,87,02,03  
A10004,SPLASH GUARDS-ALUMINUM,SPLSHGRD,PAIR,8.99,523,55,N,500,1.5,0,0,87,02,03  

A10005,SPLASH GUARDS-VINYL,SPLSHGRD,PAIR,8.99,550,25,N,500,1.5,0,0,87,02,03 
 
A10006,MONOGRAMMED SPLASH 
GUARDS,SPLSHGRD,PAIR,11.99,300,0,N,250,1.5,0,0,86,02,20

and want the output to look like this:
"A1","CARPETED RUBBER 
MATS-FRONT-RED","FLOORMAT","PAIR",22.99,135,205,"Y",5000,1.5,0,0,87,02,02
"A10001","CARPETED RUBBER 
MATS-REAR(RED)","FLOORMAT","PAIR",12.99,277,14,"N",250,1.5,0,0,86,02,20  
"A10002","PERSONALIZED VINYL 
MATS(BEIGE)","FLOORMAT","PAIR",19.99,296,7,"N",250,1.5,0,0,87,02,03  
"A10003","4-PIECE CARPET MAT SET (BLUE)","MAT 
SET","SET",19.99,275,2,"N",250,1.5,0,0,87,02,03  
"A10004","SPLASH 
GUARDS-ALUMINUM","SPLSHGRD","PAIR",8.99,523,55,"N",500,1.5,0,0,87,02,03 
 
"A10005","SPLASH 
GUARDS-VINYL","SPLSHGRD","PAIR",8.99,550,25,"N",500,1.5,0,0,87,02,03
  
"A10006","MONOGRAMMED SPLASH 
GUARDS","SPLSHGRD","PAIR",11.99,300,0,"N",250,1.5,0,0,86,02,20

Is there a way to see which column type contains CHAR( -- each of the types is 
column separated -- and then be able to apply quotes to that particular output 
field? I am not sure about this, but hope there is an answer here.

Thank you for your help!
Don Johnson
Sr. Principal Support Engineer  |  MSD - Datacom product family
Broadcom Software

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN