Turn a Tab Delimited File into a Fixed Width File



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Turn a Tab Delimited File into a Fixed Width File

Postby cbrio » Thu Dec 23, 2010 3:53 am

Is it possible to turn a tab delimited file into a fixed length file?

I have a variable length file which shows up on the mainframe as depicted under "input" below.
There appear to be periods between variables, which I think are unprintable characters that represent tabs.
The reason I think this is that when I do a search for "." the only instances the search finds are the decimal points
in the 13th variable. There are some fields where every value is blank (e.g., the 3rd variable).

I would like to turn the file into a fixed length file, where each instance of a given variable starts in the same column (e.g. the id starts in column 1, the location starts in column 10 and so on). (The output below is not showing up exactly as I want it to because of the variable spacing of the font.)

Any help you can give would be greatly appreciated.

Thank you.
Cindy

input
200000001.01M450..2009.2010.2.MO7AW.2.04.1.0.97.0.50.A.P.M.3
200000002.01M450..2009.2010.1.GG07.3.05.1.0.92.0.50.A.P.M.9
200000003.01M450..2009.2010.1.MO8AW.3.01.1.0.92.1.00.A.P.M.3
200000004.01M450..2009.2010.1.PEO7.3.01.1.0.92.0.58.A.P.M.8



desired output
200000001 01M450        2009 2010 2 MO7AW   2 04 1 0 97 0.50 A  P M 3
200000002 01M450        2009 2010 1 GG07    3 05 1 0 92 0.50 A  P M 9
200000003 01M450        2009 2010 1 MO8AW   3 01 1 0 92 1.00 A  P M 3
200000004 01M450        2009 2010 1 PEO7    3 01 1 0 92 0.58 A  P M 8

"Code'd"
cbrio
 
Posts: 15
Joined: Thu Apr 17, 2008 11:34 am
Has thanked: 0 time
Been thanked: 0 time

Re: Turn a Tab Delimited File into a Fixed Width File

 

Re: Turn a Tab Delimited File into a Fixed Width File

Postby dick scherrer » Thu Dec 23, 2010 4:22 am

Hello,

To get posts aligned, simply copy/paste from the screen, highlight, and use the Code tag (your data has been "Code'd").
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Turn a Tab Delimited File into a Fixed Width File

Postby MrSpock » Thu Dec 23, 2010 5:15 am

What is/are the hex value(s) of those unprintable characters?
User avatar
MrSpock
Global moderator
 
Posts: 810
Joined: Wed Jun 06, 2007 9:37 pm
Location: Raleigh NC USA
Has thanked: 0 time
Been thanked: 2 times

Re: Turn a Tab Delimited File into a Fixed Width File

Postby Frank Yaeger » Thu Dec 23, 2010 6:31 am

Cindy,

What is the RECFM and LRECL of the input file?

I need to see what a few records look like in hex before I can help you. So please post that.

If the file is FB, use DFSORT statements like this to show the first 3 records in hex:

    OPTION COPY,STOPAFT=3
    INREC BUILD=(1,n,HEX)


where n is the LRECL.

If the file is VB, use DFSORT statements like this to show the first 3 records in hex:

    OPTION COPY,STOPAFT=3
    INREC BUILD=(1,4,5,HEX)
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Turn a Tab Delimited File into a Fixed Width File

Postby dick scherrer » Thu Dec 23, 2010 10:35 am

Hello,

What is/are the hex value(s) of those unprintable characters?
If the file is tab-delimited, those should be x'05's.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Turn a Tab Delimited File into a Fixed Width File

Postby cbrio » Wed Dec 29, 2010 1:20 am

The format is FB, the LRECL is 300.

Here's what the file looks like in hex (the first 3 records):
F2F0F0F0F0F3F2F9F105F0F2D4F1F3F10505F2F0F0F905F2F0F1F005F105C8F7C3C505F205F0F105F105F005F7F405F04BF5F005C305D705D405F205F705F105E2A381958481998405C3C5D3C5C2D9C1E3C9D6D5E20505F14BF0F005F2F0F1F060F0F360F1F240F1F37AF4F67AF4F54BF7F0F7F0F0F0F0F0F005D4D78187819505C8E2E2E36DC4E3E26DE4978481A385C799818485A2
F2F0F0F0F0F3F2F9F105F0F2D4F1F3F10505F2F0F0F905F2F0F1F005F105E3F7C4C505F505F0F105F105F005F8F005F04BF5F005C205D705D405F605F005F105E2A381958481998405C4C9C7C9E3C1D340D7D6D9E3C6D6D3C9D60505F14BF0F005F2F0F1F060F0F660F0F740F1F87AF3F67AF2F44BF7F9F3F0F0F0F0F0F005D4D78187819505C8E2E2E36DC4E3E26DC995A28599A3C7
F2F0F0F0F0F3F3F0F905F0F2D4F1F3F10505F2F0F0F905F2F0F1F005F105D4D6F7F405F505F0F205F105F005F8F005F14BF0F005C205D705D405F305F805F105E2A381958481998405D4C1E3C840F70505F14BF0F005F2F0F1F060F0F660F0F740F1F67AF3F37AF2F74BF0F7F0F0F0F0F0F0F005D4D78187819505C8E2E2E36DC4E3E26DC995A28599A3C799818485A205F2F0F1F060

Here's what it looks like in original format (1st 3 records):
200003291.02M131..2009.2010.1.H7CE.2.01.1.0.74.0.50.C.P.M.2.7.1.Standard.CELEBRATIONS..1.00.2010-03-12 13:46:45.707000000.MPagan.HSST_DTS_UpdateGrades.2010-11-12 16:21:43.933000000
200003291.02M131..2009.2010.1.T7DE.5.01.1.0.80.0.50.B.P.M.6.0.1.Standard.DIGITAL PORTFOLIO..1.00.2010-06-07 18:36:24.793000000.MPagan.HSST_DTS_InsertGrades.2010-11-12 16:21:43.933000000
200003309.02M131..2009.2010.1.MO74.5.02.1.0.80.1.00.B.P.M.3.8.1.Standard.MATH 7..1.00.2010-06-07 16:33:27.070000000.MPagan.HSST_DTS_InsertGrades.2010-11-12 16:21:43.933000000

Thank you.
cbrio
 
Posts: 15
Joined: Thu Apr 17, 2008 11:34 am
Has thanked: 0 time
Been thanked: 0 time

Re: Turn a Tab Delimited File into a Fixed Width File

Postby Frank Yaeger » Wed Dec 29, 2010 2:22 am

You would have to use DFSORT's PARSE function to create the fixed length file, but it won't be automatic. You will have to set up a %nn variable for each "field" with its appropriate FIXLEN length. You can use ENDBEFR=X'05' to find the delimiter for each field. You can set up to 100 %nn fields this way (%00-%99).

For complete information on DFSORT's PARSE function, see:

http://www.ibm.com/support/docview.wss? ... g3T7000086
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Turn a Tab Delimited File into a Fixed Width File

Postby dick scherrer » Wed Dec 29, 2010 2:55 am

Hello,

You might consider writing a bit of code to UNSTRING the delimited data into fixed fields.

This could be invoked as a SORT exit (unless there is something to prevent this) or run standalone. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Turn a Tab Delimited File into a Fixed Width File

Postby cbrio » Wed Dec 29, 2010 4:46 am

Thank you very much for your help and guidance.
cbrio
 
Posts: 15
Joined: Thu Apr 17, 2008 11:34 am
Has thanked: 0 time
Been thanked: 0 time

Re: Turn a Tab Delimited File into a Fixed Width File

Postby cbrio » Wed Dec 29, 2010 4:51 am

When I try to get to the pdf for SORTPEUG, I get an error page:

Network Access Message: The page cannot be displayed

Explanation: There is a problem with the page you are trying to reach and it cannot be displayed.

Try the following:
Refresh page: Search for the page again by clicking the Refresh button. The timeout may have occurred due to Internet congestion.
Check spelling: Check that you typed the Web page address correctly. The address may have been mistyped.
Access from a link: If there is a link to the page you are looking for, try accessing the page from that link.
If you are still not able to view the requested page, try contacting your administrator or Helpdesk.



Technical Information (for support personnel)
Error Code: 502 Proxy Error. The ISA Server denied the specified Uniform Resource Locator (URL). (12202)
IP Address: 10.3.3.20
Date: 12/28/2010 11:19:36 PM [GMT]
Server: OP1ISA01.central.nyced.org
Source: proxy
cbrio
 
Posts: 15
Joined: Thu Apr 17, 2008 11:34 am
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post