Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Splitting 1 column and inserting into multiple columns

    Db2 9.7 FP 7 LUW

    Hello,
    I have a flat file (.csv) which come from legacy systems non-RDBMS. Some columns have multiple occurrences which needs to be split with integrity in DB2. Here is an example of what i'm trying to do:

    ADDRESS
    123abc~234bcd~345cde~456def~567efg
    111aaa~222bbb~~~
    999zzz~~~888yyy~

    -There will always be a value before and after a ~ whether it is null or not. If null, it must insert as null in target column, which will be nullable.

    Split the 5 occurrences and possibly set them in a variable and insert that variable into it's respective target column.

    So essentially I should be able to loop through each record, find values before and after tilde's and insert those into target columns.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the best would be to create a script- reading the rows and handling/preparing the data for insert
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea is first load the data(flat file (.csv)) into intermediate table, then insert into target table from the intermediate table.

    An example of the insert statement might be
    Code:
    INSERT INTO target_table
    ( pk
    , splittted_1 , splittted_2 , splittted_3 , splittted_4 , splittted_5
    [, other columns , ...]
    )
    SELECT pk
         ,        SUBSTR(address , 1           , tilde_1 - 1          )
         , NULLIF(SUBSTR(address , tilde_1 + 1 , tilde_2 - tilde_1 - 1) , '')
         , NULLIF(SUBSTR(address , tilde_2 + 1 , tilde_3 - tilde_2 - 1) , '')
         , NULLIF(SUBSTR(address , tilde_3 + 1 , tilde_4 - tilde_3 - 1) , '')
         , NULLIF(SUBSTR(address , tilde_4 + 1                        ) , '')
        [, other columns , ...]
     FROM  (SELECT t.*
                 , INSTR(address , '~' , 1 , 1) AS tilde_1
                 , INSTR(address , '~' , 1 , 2) AS tilde_2
                 , INSTR(address , '~' , 1 , 3) AS tilde_3
                 , INSTR(address , '~' , 1 , 4) AS tilde_4
             FROM  intermediate_table AS t
           )
    ;

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    If all your columns belong to the same table, then you can try the IMPORT command using '~' symbol as a column delimiter and the 'nochardel' option.
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •