6 Replies Latest reply: Jul 12, 2012 1:32 PM by Pavan. RSS

    .csv file to generate columns in Lowercase.

    Pavan.
      Hi Gurus,

      Iam creating the .csv file dynamically in a specified location with the columns like (NUMBER,NAME,AGE,GROUP_NAME) but my requirement is to create it like (Number,Name,Age,Group Name)

      1)In simple i can say like they doesnt want to have an excell file columns with ALL CAPSLOCK they want to have columns only with the first letter as CAPS.
      2)For the GROUP_NAME they want space between the Group and Name as Group Name.

      can we do it using ODI if so what we need to do and if it is possible to Customise any KM also i was ready to do.

      please help me in finding a solution for this.
      Any Help is highly Appreciable.

      Regards,
      Pavan Kumar
        • 1. Re: .csv file to generate columns in Lowercase.
          Sutirtha Roy
          Pavan. wrote:
          Iam creating the .csv file dynamically in a specified location with the columns like (NUMBER,NAME,AGE,GROUP_NAME) but my requirement is to create it like (Number,Name,Age,Group Name)

          can we do it using ODI if so what we need to do and if it is possible to Customise any KM also i was ready to do.
          You can do this with out modifying any KM

          Basic idea for this will be

          Create an interface which will create the file header .
          Second interface will load the file generated by the 1st interface.

          If you need step by step guide then let us know .
          • 2. Re: .csv file to generate columns in Lowercase.
            Krishnamoorthy
            Create two Data Store - one for heading (introduce filter to fetch first line), another to fetchremainign lines.


            Put it in twointerface and bundle it in package.
            • 3. Re: .csv file to generate columns in Lowercase.
              Pavan.
              Hi Sutirtha Roy ,

              Thank you for the Suggestion and can you please tell me the step by step approach so that it will be more helpful to me.

              Thanks,
              Pavan Kumar
              • 4. Re: .csv file to generate columns in Lowercase.
                Sutirtha Roy
                Pavan. wrote:

                Thank you for the Suggestion and can you please tell me the step by step approach so that it will be more helpful to me.
                Create a table called DUMMY with single character type column . For that matter you can use DUAL table also.
                Reverse that table into ODI model.

                Create an interface ( Interface_header) with source as DUMMY and target as your FILE which has the column as (NUMBER,NAME,AGE,GROUP_NAME)
                The staging area needs to be set to a RDBMS schema .
                In the mapping explorer set the mapping for column named NUMBER as 'Number'
                In the mapping explorer set the mapping for column named NANE as 'Name'
                .
                .
                In the mapping explorer set the mapping for column named GROUP_NAME as 'Group Name'
                All of these mapping needs to be executed on staging .
                LKM : As per your choice
                IKM: IKM Sql To File Append

                IKM Option :
                Create Target Table : YES
                Truncate : YES
                Insert: YES

                Create second interface (Interface_Data) with your actual source table which contains data
                Target will be the same as that of 1st interface ( Interface_header)
                LKM : As per your choice
                IKM: IKM Sql To File Append

                IKM Option :
                Create Target Table : NO
                Truncate : NO
                Insert: YES

                Create a package :
                Interface_header ----------ok-----------------> Interface_Data


                Thanks,
                Sutirtha
                • 5. Re: .csv file to generate columns in Lowercase.
                  Pavan.
                  Hi Sutirtha,

                  I tried implementing the same process what u have mentioned me clearly in the above post (thanks for that post).

                  But while implementing i am getting an error in the first interface (Interface_header) in the step of load data as below.

                  ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection SCOTT.
                  Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression.

                  and the code it is generating in this step is as below.

                  IN SOURCE CODE
                  select     
                       
                  from     SCOTT.PAVAN PAVAN
                  where     (1=1)


                  IN TARGET CODE

                  insert into "C$_0pavan"
                  (
                       
                  )
                  values
                  (
                       
                  )



                  can you please guide me if i miss out any thing in the process and the dummy table with a single column with out any data has been also created. Is there a mistake in creating the DUMMY TABLE from myside.


                  Thanks,
                  Pavan Kumar
                  • 6. Re: .csv file to generate columns in Lowercase.
                    Pavan.
                    Thanks Sutirtha for the valuable input you have given,

                    i resolved my previous post issue by my own by taking the required column name as one of the data in to that DUMMY table column and mapped that with the respective column in my target so problem solved and a correction is that for making this to work out exactly

                    we need to make Generate Header as FALSE in the properties of IKM SQL TO FILE APPEND.

                    so please make a note of the above for further reference.


                    Regards,
                    pavan