Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: DB2 CSV handling in SP

    Hi,
    I have a column where we have account numbers in a csv format in a varchar column.

    The account number are in the following format 100105757. Each account number is 9 character longs and the csv column can have a maximum of but not limited to 8 accounts separated by commas. Some columns may have 1 account number some may have more but max in 8.

    I am trying to write an SP where I need to seperate the csv and load accounts into 8 columns in a temporary table.

    Can anyone help how can I split the csv.

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it would be more clear, if some samples where provided
    I would not do this with a sp but with a simple script where you prepare the data for import/load
    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
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    - create a temp table with 8 VARCHAR columns
    - IMPORT FROM your_CSV_file OF DEL INSERT INTO your_temp_table
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    how can I split the csv
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data
    ( id , accounts_in_csv ) AS (
    VALUES
      ( 1 , '100105757' )
    , ( 2 , '200105757,200205757,200305757' )
    , ( 3 , '300105757,300205757,300305757,300405757' )
    )
    SELECT id
         , SUBSTRB(accounts_in_csv ,  1 , 9) AS account_1
         , SUBSTRB(accounts_in_csv , 11 , 9) AS account_2
         , SUBSTRB(accounts_in_csv , 21 , 9) AS account_3
         , SUBSTRB(accounts_in_csv , 31 , 9) AS account_4
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    ID          ACCOUNT_1 ACCOUNT_2 ACCOUNT_3 ACCOUNT_4
    ----------- --------- --------- --------- ---------
              1 100105757                              
              2 200105757 200205757 200305757          
              3 300105757 300205757 300305757 300405757
    
      3 record(s) selected.

Posting Permissions

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