MVSFORUMS.com :: View topic - Convert file fields into Varchar format

MVSFORUMS.com Forum Index

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Register

Log in to check your private messages

Convert file fields into Varchar format

printer-friendly view

MVSFORUMS.com Forum Index -> Utilities

View previous topic :: View next topic

Author

Message

jathampy
Beginner

Joined: 21 Dec 2007
Posts: 15
Topics: 6
Location: UK

Posted: Thu Aug 24, 2023 1:43 pm Post subject: Convert file fields into Varchar format

Reply with quote

I am having a CSV file as shown below.

Code:

0001,xyz,pedfgh,zehhhee,yutte8901
0002,ABCDEE,UFGGGGGGGH,ytettetteee,9
0003,pqrst,asd,mnopql,fdghsjjss

Want to convert this file into varchar format.The first field is of 4 characters which is of fixed length. The 2nd ,3rd ,4th and 5th field are variable fields that can have maximum of 22 characters.

Basically want to add two hexadecimal characters before 2nd,3rd ,4th and 5th field that indicates the length.

Sample output record corresponding to input record number 1 is listed below (used $ character to represent blank and X to represent hexadecimal character)

Code:

0001X'0003'xyz$$$$$$$$$$$$$$$$$$$X'0006'pedfgh$$$$$$$$$$$$$$$$X'0007'zehhhee$$$$$$$$$$$$$$$X'0009'yutte8901$$$$$$$$$$$$$

Thinking of using PARSE first to convert field 2-5 into a fixed string of 22 characters. Then use multiple IF statements to count spaces of individual fields and finally subtract the count from 22.

Is there a better way of achieving this using SORT ?

Send private message

kolusu
Site Admin

Site Admin

Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose

Posted: Thu Aug 24, 2023 4:31 pm Post subject:

Reply with quote

jathampy,

You can use the following DFSORT ICETOOL Jcl which will give you the desired results.

Basically you RESIZE the records after the parse and convert it into VLR so that you can get the length of each record and then re-assemble it back again.

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SYMNAMES DD *
TMPRDW,01,04,BI
FLD01,*,04,CH
FLD02_RDW,*,02,BI
FLD02_VAL,*,22,CH
FLD03_RDW,*,02,BI
FLD03_VAL,*,22,CH
FLD04_RDW,*,02,BI
FLD04_VAL,*,22,CH
FLD05_RDW,*,02,BI
FLD05_VAL,*,22,CH
//INP DD *
0001,XYZ,PEDFGH,ZEHHHEE,YUTTE8901
0002,ABCDEE,UFGGGGGGGH,YTETTETTEEE,9
0003,PQRST,ASD,MNOPQL,FDGHSJJSS
//TMP DD DSN=&&TMP,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
RESIZE FROM(INP) TO(TMP) TOLEN(22) USING(CTL1)
COPY FROM(TMP) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
OPTION COPY
INREC PARSE=(%01=(ENDBEFR=C',',
REPEAT=5,
FIXLEN=22)),
BUILD=(8C'$',23:%01,%02,%03,%04,%05)

OUTFIL FTOV,VLTRIM=C' '
/*
//CTL2CNTL DD *
INREC IFTHEN=(WHEN=INIT,
BUILD=(001:01,04,
151:01,02,BI,SUB,+4,BI,LENGTH=2,
05,22)),

IFTHEN=(WHEN=GROUP,
BEGIN=(153,08,CH,EQ,C'$$$$$$$$'),
PUSH=(131:ID=8,
140:SEQ=2)),

IFTHEN=(WHEN=GROUP,
BEGIN=(140,2,ZD,EQ,2),
END=(140,2,ZD,EQ,1),
PUSH=(FLD01:153,04)),

IFTHEN=(WHEN=GROUP,
BEGIN=(140,2,ZD,EQ,3),
END=(140,2,ZD,EQ,1),
PUSH=(FLD02_RDW:151,02,153,22)),

IFTHEN=(WHEN=GROUP,
BEGIN=(140,2,ZD,EQ,4),
END=(140,2,ZD,EQ,1),
PUSH=(FLD03_RDW:151,02,153,22)),

IFTHEN=(WHEN=GROUP,
BEGIN=(140,2,ZD,EQ,5),
END=(140,2,ZD,EQ,1),
PUSH=(FLD04_RDW:151,02,153,22)),

IFTHEN=(WHEN=GROUP,
BEGIN=(140,2,ZD,EQ,6),
END=(140,2,ZD,EQ,1),
PUSH=(FLD05_RDW:151,02,153,22))

OUTFIL REMOVECC,VTOF,NODETAIL,BUILD=(100X),
SECTIONS=(131,8,
TRAILER3=(5,100))
/*

The output of this job is (showing the hex values of first record, so that you can see the length for each field.)

Code:

0001 XYZ PEDFGH ZEHHHEE YUTTE8901
FFFF00EEE444444444444444444400DCCCCC444444444444444400ECCCCCC44444444444444400EEEECFFFF44444444444444444444
00010378900000000000000000000675467800000000000000000795888550000000000000000984335890100000000000000000000
-----------------------------------------------------------------------------------------------------------
0002 ABCDEE UFGGGGGGGH YTETTETTEEE 9
0003 PQRST ASD MNOPQL FDGHSJJSS

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

Send private message

Send e-mail

Visit poster's website

jathampy
Beginner

Joined: 21 Dec 2007
Posts: 15
Topics: 6
Location: UK

Posted: Fri Aug 25, 2023 4:35 am Post subject:

Many thanks Kolusu for providing the solution. It worked.

Send private message

Display posts from previous:

	MVSFORUMS.com Forum Index -> Utilities	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group