IBM Sterling Transformation Extender

Sterling Transformation Extender

Come for answers, stay for best practices. All we're missing is you.


#Sterling
#Supplychain
 View Only
Expand all | Collapse all

Parse the *.PSV file to *.CSV file

  • 1.  Parse the *.PSV file to *.CSV file

    Posted 11/21/11 10:20 AM

    Originally posted by: Venki52


    Hi,

    Is it possible to parse the File1.psv file to File.csv file using WTX? If possible, my source file is '|' ( pipe) delimeter file. Please let me know which option do i need select while creating the type tree? Also the source file having morethat 100000 records, need to load all the records into the target CSV file.

    Please find below the sample record.

    ENTRY_TYPE|CURRENCY|BOOK|RECON_DATE|LEGAL_ENTITY|SOURCE_SYSTEM|INPUT_BY|INPUT_TIME|SUB_BRANCH|TRADE_ID|PRODUCT|CDR_ID|ACCOUNT|DIRECTION|AMOUNT|CTRPTY_ID1|CTRPTY_ID2|TRANS_ID|VALUE_DATE|NARRATIVE|GAAP|OLD_ID
    1|AUD|EINFAM|20111109|RBCEL|LDNINF|ETLADM|20111109|EINFAM||||1011309999999|D|3079527.57||||||IFRS_SETTLE|IIFAUDEINFAM
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange


  • 2.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 11:17 AM

    Originally posted by: Venki52


    I am able to create the type tree but unable to load the data into the target file.
    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender


  • 3.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 12:04 PM

    Originally posted by: Venki52


    I tried with sample file with 3 fields, getting the below error while processing.

    Error Message:

    (Level 1: Offset 0, len 0, comp 1 of 3, #1, DI 00000001:)
    Data at offset 0 ('Col1|Col2|Col3<CR><LF>...') does not match DELIMITER '|'
    of TYPE X'0003' (NewGroup0 Record Data).

    (Level 1: Offset 0, len 0, comp 1 of 3, #1, DI 00000001:)
    COMPONENT number 1 of TYPE X'0003' (NewGroup0 Record Data)
    is required, but does not exist.

    (Level 1: Offset 0, len 0, comp 1 of 3, #1, DI 00000001:)
    Data at offset 0 ('Col1|Col2|Col3<CR><LF>') is INVALID data of TYPE
    X'0005' (NewItem0 Field Data).

    (Level 1: Offset 0, len 0, comp 2 of 3, #1, DI 00000002:)
    Data at offset 0 ('Col1|Col2|Col3<CR><LF>...') does not match DELIMITER '|'
    of TYPE X'0003' (NewGroup0 Record Data).

    (Level 1: Offset 0, len 0, comp 2 of 3, #1, DI 00000002:)
    Data at offset 0 ('Col1|Col2|Col3<CR><LF>') was found to be of TYPE
    X'0006' (NewItem1 Field Data).

    (Level 1: Offset 0, len 0, comp 3 of 3, #1, DI 00000002:)
    Data at offset 0 ('Col1|Col2|Col3<CR><LF>...') does not match DELIMITER '|'
    of TYPE X'0003' (NewGroup0 Record Data).

    (Level 1: Offset 0, len 0, comp 3 of 3, #1, DI 00000002:)
    Data at offset 0 ('Col1|Col2|Col3<CR><LF>') was found to be of TYPE
    X'0007' (NewItem2 Field Data).

    INPUT 1 exists, but its type is in error.
    End of Validation messages for INPUT CARD 1.
    End of Execution messages.

    Please let me know how can i resolve this.
    #IBMSterlingTransformationExtender
    #DataExchange
    #IBM-Websphere-Transformation-Extender


  • 4.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 12:21 PM

    Originally posted by: Dhiren.Ghelani


    how have you defined the delimter? infix? prefix ? postfix? post your map for a quick look
    #DataExchange
    #IBMSterlingTransformationExtender
    #IBM-Websphere-Transformation-Extender


  • 5.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 12:54 PM
      |   view attached

    Originally posted by: Venki52


    Hi Please find the attached map.
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange

    Attachment(s)



  • 6.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 01:11 PM

    Originally posted by: NancyR


    Could you post the type trees and source file also?
    #IBMSterlingTransformationExtender
    #DataExchange
    #IBM-Websphere-Transformation-Extender


  • 7.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 01:21 PM
      |   view attached

    Originally posted by: Venki52


    Thanks for your time. My original source file is similar to the attached file. Please look into this and let me the changes. Thanks.
    #IBM-Websphere-Transformation-Extender
    #DataExchange
    #IBMSterlingTransformationExtender

    Attachment(s)



  • 8.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 06:24 PM
      |   view attached

    Originally posted by: NancyR


    The reason you're getting the error is you defined NewGroup0 as being prefix delimited which means the delimiter would be the first character in your data. Change that to infix. The next problem is you have a <CR> terminator on NewItem0 but there is no <CR> in the data. I changed Groupset1 to contain multiple NewGroup0 Record(s) and changed the input card to use that type from the type tree.

    I've attached the modified type tree. If you have a problem with the output, I'll need the type tree you used in your output card.
    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender

    Attachment(s)



  • 9.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/21/11 11:01 PM

    Originally posted by: Venki52


    Thanks a lot for your time Nancy. If you attach the type tree it will great help for me.
    #IBM-Websphere-Transformation-Extender
    #DataExchange
    #IBMSterlingTransformationExtender


  • 10.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 08:22 AM
      |   view attached

    Originally posted by: NancyR


    Sorry I grabbed the wrong file the first time.
    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender

    Attachment(s)



  • 11.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 09:36 AM
      |   view attached

    Originally posted by: Venki52


    I am unable to open the Type tree. Please find the attachment for the error message.
    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender

    Attachment(s)



  • 12.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 09:42 AM

    Originally posted by: NancyR


    I did it in WTX 8.3. If that's not the version you're using you will not be able to open it.

    Just follow the instructions in my earlier post and you should be able to fix it yourself.
    #IBM-Websphere-Transformation-Extender
    #DataExchange
    #IBMSterlingTransformationExtender


  • 13.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 10:20 AM

    Originally posted by: NancyR


    Double-click the group to open the group window. Then right-click the component and choose Set Range. Type 0 if the file is valid with no components or the minimum number of components required. Type s for the maximum.
    #IBMSterlingTransformationExtender
    #IBM-Websphere-Transformation-Extender
    #DataExchange


  • 14.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 09:49 AM

    Originally posted by: Venki52


    Hi Nancy, Can you please let me know the options i need to set in GroupSet1 foe allowing multiple records? Thanks.
    #DataExchange
    #IBMSterlingTransformationExtender
    #IBM-Websphere-Transformation-Extender


  • 15.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 10:08 AM

    Originally posted by: Venki52


    I have used the below options. Please let me know if there is anything wrong.

    Input Type Tree:

    In the GroupSet1 : GroupSub Class -> Format -> Component Syntax -> Delimiter (Literal) -> Value (|) -> Location (Infix).
    In the NewGroup0 : Type Syntax -> Terminator (Literal) -> Value <NL>.

    In the output card also i have followed the same options.

    Thanks.
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange


  • 16.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 10:12 AM

    Originally posted by: Venki52


    In addition to the proviour response options, I have chaged the GroupSet1 Class options as Group.
    #IBMSterlingTransformationExtender
    #DataExchange
    #IBM-Websphere-Transformation-Extender


  • 17.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/22/11 12:57 PM

    Originally posted by: NancyR


    When you run you map if the input data validates successfully you've got the type tree done correctly.
    #DataExchange
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender


  • 18.  Re: Parse the *.PSV file to *.CSV file

    Posted 11/23/11 01:30 PM
      |   view attached

    Originally posted by: jvanboga


    If you are still having questions take a look at this. There are 4 examples in he attached map.
    #IBM-Websphere-Transformation-Extender
    #IBMSterlingTransformationExtender
    #DataExchange

    Attachment(s)