This discussion is archived
5 Replies Latest reply: Apr 11, 2013 1:53 PM by user13373097 RSS

Loading Fixed format file

947775 Newbie
Currently Being Moderated
Hi I am getting one fixed format file like below


110Ramhr20111Samsales10112abc30113xyzmarketing40


so the the data should load like this

EMP_ID EMP_NAME DEPT_NAME DEPT_ID

110 Ram HR 20
111 Sam Sales 10
112 abc 30
113 xyz Marketing 40


if we are getting file like this as fixed format that's fine i can able to load


but if we get file like "110Ramhr20111Samsales10112abc30113xyzmarketing40" then how we can able to load data row by row.
  • 1. Re: Loading Fixed format file
    DecaXD Pro
    Currently Being Moderated
    you need at least a marker as | to make a delimited file.
    In this case you will have

    110|Ramhr|20|111|Samsales|10|112|abc|30|113|xyzmarketing|40|

    in this case you have to process file to insert a row delimiter by jython.
  • 2. Re: Loading Fixed format file
    JeromeFr Expert
    Currently Being Moderated
    This is not fixed if it's not the same number of characters for each row.
    So if there is no delimiter, there is no way to easily create a datastore. In addition you have null values.

    Even regex is not possible because emp_name and dept_name are both composed of letters. Unless the emp_name is always on 3 characters as in your example. If it's not the case, I don't think it's possible.


    Regards,
    JeromeFr
  • 3. Re: Loading Fixed format file
    947775 Newbie
    Currently Being Moderated
    Ram101Sam102ABC103XYZ104

    How abt this one now i have only Name and Emp_Id

    Name of 3 Char and Emp_Id of Number(3)

    now i want to load this data in 3 rows

    as follows

    Ram 101
    Sam 102
    ABC 103
    XYZ 104

    is it Possible
  • 4. Re: Loading Fixed format file
    DecaXD Pro
    Currently Being Moderated
    Hi, how many rows are expected?

    I'd use a jython step before that every 6 char insert a new line. You could use this guide

    http://www.business-intelligence-quotient.com/wp-content/themes/biq/downloads/Jython_beginner_guide_for_ODI.pdf

    if i find i'll post to you the solution
  • 5. Re: Loading Fixed format file
    user13373097 Explorer
    Currently Being Moderated
    You could try this:
    1.     Define the file source as fixed
    2.     Select Other under the Record Separator, but do not enter any code
    3.     Define column names and length as needed.
    4.     Check how the data look using View Data
    It worked for me.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points