5 Replies Latest reply: Apr 11, 2013 3:53 PM by user13373097 RSS

    Loading Fixed format file

    947775
      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
          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
            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
              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
                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
                  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.