none
Flat File Schema Wizard with restriction upto 80 characters per row RRS feed

  • Question

  • Hi,

    I have to create a target Flat File Schema for purchase order using the wizard, which consists of Header, Body & Footer.

    The requirement consists of below mentioned things:

    1. Each row must consist of 80 characters only.
    2. Also, major content in the file is positional.
    3. In the body part, "+" symbol has been used as delimiter.

    Do get an exact idea of how the file looks, please click on the URL here: https://github.com/gizur/gizurcloud/wiki/System_Documentation%23Client%23Cikab%23Sales-Order-Export

    Actual file description is mentioned along with the positional details.

    In the URL, detailed explanation of file is present, look for "Set File Specification".

    Please let me know how i can proceed with this.

    Thanks in advance.

    Friday, February 12, 2016 7:07 AM

Answers

  • Hi Abdul,

    This can be solved by the following approach,

    1) Create a flat file using flat file wizard by considering the SET file as having one record. The header, detail records and footer will be in one single file. Take the sample file as given in the reference link and open in Notepad++, then join lines. Please make sure you verify the final file with the specification so that the position is as per the spec. As I understand, after the header, there is a detail record.There is no + symbol before this detail record and after which you have six detail records separated by + and then a repeating record (189 type) and finally the footer.

    2)You need to run the flat file wizard and give the input file created in step 1. Select the complete line and then split by delimiter (+). The first detail record will be under Header as there is no + sign.You then need to define the fields under header and the first detail record by positions.

    3)Repeat for other detail records and footer. Now you have the flat file schema. Make sure to validate the schema against the instance and generate a new instance and check if it matches with the flat file input.

    4) Create a custom pipeline component that you will add after the Flat File Assembler pipeline component in the Send Pipeline.

    This custom component should split the file after 80 characters. Basically you need to add "\r\n" after every 80 chars so that final output will be as per your requirement. You can do it the stream way without making the whole process memory intensive.

    Regards,

    Sajith C P Nair

    Monday, February 15, 2016 11:47 AM

All replies

  • The specifications state that CRLF is not allowed [4.2 - Some Rules]. Then how are you assuming 80 character rows ???? Are you possibly confusing text wrap in the editor with row definition ???

    Use a pure positional structure in the Flat File Schema Wizard and then define the schema.

    Regards. 


    • Edited by Shankycheil Friday, February 12, 2016 7:52 AM edit
    Friday, February 12, 2016 7:52 AM
  • Yes. It is certainly not the CRLF.

    However, if you could observe closely in the example set files. It is always 80 per row.

    And once it exceeds the number, it must automatically pass it on to the next line.

    Regards.

    Friday, February 12, 2016 8:01 AM
  • The next line is only demarcated with CRLF... in the specification on the site, the column numbering does not reset after 80... it continues (0 to 406) which implies that it is one continuous stream.... Refer to SET File Specification (APPENDIX). In the sample files (IMHO) they have split the single line into 80 column lines for readability purposes.

    So it is a positional specification and based on the field lengths you have to pad with spaces to maintain the positional format.

    Regards.

    Friday, February 12, 2016 9:37 AM
  • Hi,

    Please share an input file.

    Best regards

    Rasmus Jaeger

    Friday, February 12, 2016 12:20 PM
  • So, what exactly is the problem you're having?  What have you tried?

    What you're describing pretty simple.  If you're not familiar with it, you may have to run the Flat File Schema Wizard several times to get it right and there's nothing wrong with that.

    Run the Wizard a few times, then if you're having specific issues, we're here to help.

    Friday, February 12, 2016 1:02 PM
    Moderator
  • Hi,

    Input file for this is present in the same link https://github.com/gizur/gizurcloud/wiki/System_Documentation%23Client%23Cikab%23Sales-Order-Export 

    Please look for below header.

    Examples of SET files

    Best Regards

    Monday, February 15, 2016 4:46 AM
  • Hi,

    The problem here is, we are unable to create a Target schema, for Flat Files.

    As per the requirement,

    1. We need to control the row length to 80 characters/row.

    2. And in the link provided section 4.4.20 is order details, and the order can be dynamic. I wanted to understand how it can be controlled dynamically and a small catch here. The footer sectoin 4.5.1 needs to append exactly at the end of order details, and 80/row should be taken care

    We have now zeroed on taking the entire file in a single row (IO Stream). And at the send side, using a custom pipeline truncate it to 80/row.

    Please let me know if we could do this with something which would enhance the performance.

    Thank you so much.

    Best Regards

    Monday, February 15, 2016 5:08 AM
  • Hi Abdul,

    This can be solved by the following approach,

    1) Create a flat file using flat file wizard by considering the SET file as having one record. The header, detail records and footer will be in one single file. Take the sample file as given in the reference link and open in Notepad++, then join lines. Please make sure you verify the final file with the specification so that the position is as per the spec. As I understand, after the header, there is a detail record.There is no + symbol before this detail record and after which you have six detail records separated by + and then a repeating record (189 type) and finally the footer.

    2)You need to run the flat file wizard and give the input file created in step 1. Select the complete line and then split by delimiter (+). The first detail record will be under Header as there is no + sign.You then need to define the fields under header and the first detail record by positions.

    3)Repeat for other detail records and footer. Now you have the flat file schema. Make sure to validate the schema against the instance and generate a new instance and check if it matches with the flat file input.

    4) Create a custom pipeline component that you will add after the Flat File Assembler pipeline component in the Send Pipeline.

    This custom component should split the file after 80 characters. Basically you need to add "\r\n" after every 80 chars so that final output will be as per your requirement. You can do it the stream way without making the whole process memory intensive.

    Regards,

    Sajith C P Nair

    Monday, February 15, 2016 11:47 AM
  • The problem here is, we are unable to create a Target schema, for Flat Files.

    ...

    We have now zeroed on taking the entire file in a single row (IO Stream). And at the send side, using a custom pipeline truncate it to 80/row.

    Well, no, that's not the problem.  Can you describe what exactly is the problem you facing with the Wizard or you schema?  Wrapping, delimiters, etc.

    No, you do not need to do this.  Your format is really simple.  Please try the wizard a few times and you'll get it.

    Monday, February 15, 2016 1:16 PM
    Moderator