Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: load problem with delimiters

    Hi everybody,
    I'm trying to load data from the following data-file. All data is enclosed by " including the numeric fields. If I use the load client command, all data is imported except for the decimal and integer data which appearantly cannot be converted (they stay null). If I use data studio, the data file is imported correctly. However I want to use the load client command. What am I missing here. I'd appreciate the advice. Cheers

    source.csv
    "Balance","A0000","Balance code","20100101","750","Balance code","1.35","1.65"
    "Balance","A0001","Balance code","20100101","600","Balance code","1.02","1.25"
    "Balance","A0002","Balance code","20100101","1205","Balance code","1.1","2.35"
    "Letter","C0000","Balance code","20100101","1803","Main mailcode","1.12","1.34"

    load client from "source.csv" of del modified by
    timestampformat="yyyy-mm-dd hh:mm:ss"
    dateformat="yyyymmdd"
    decpt.
    chardel""
    coldel,
    dumpfile="/tmp/SQLDIR.LK0/source.dmp"
    method P (1, 2, 3, 4, 5, 6, 7, 8) messages "sourcebron.log"
    replace into IMPORT.SOURCE(
    FIELD_A,
    FIELD_B,
    FIELD_C,
    FIELD_D,
    FIELD_E,
    FIELD_F,
    FIELD_G,
    FIELD_H)
    copy no indexing mode autoselect;

    table import.source (
    FIELD_A varchar(255),
    FIELD_B varchar(255),
    FIELD_C varchar(255),
    FIELD_D date,
    FIELD_E bigint,
    FIELD_F varchar(200),
    FIELD_G decimal(10,2),
    FIELD_H decimal(10,2)
    )

  2. #2
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20
    Are you trying to load the file from a remote server?
    The LOAD CLIENT specifies that the .csv file is being located remotely.

    If you are loading from remote client then code page conversion is not performed. If the code pages of data & server differs then the data code page should be specified using modified by codepage=<your_server_code_page>.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    DB2 treats whatever is enclosed in string delimiters as character data, which it refuses to LOAD (or IMPORT) into decimal fields. I guess you have two options: either manipulate the input file and remove the unnecessary string delimiters, or do a LOAD into a staging table where the columns in question are defined as character, then do another LOAD from a cursor that has an explicit cast from character to decimal.

  4. #4
    Join Date
    Dec 2011
    Posts
    3
    Thanks for the help.

    Unfortunately, I don't have any influence over the file format that is delivered. I assumed that DB2 would convert automatically, like Oracle, SQLServer and Sybase.

    I'll use a staging table for now, but I'm not giving up. Like I wrote before, Data studio can import this data without a problem.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by n_i View Post
    ... or do a LOAD into a staging table where the columns in question are defined as character, then do another LOAD from a cursor that has an explicit cast from character to decimal.
    This may be an example to insert into target table from staging table.

    Example 1:

    Test Data
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM import.staging;
    ------------------------------------------------------------------------------
    
    COLUMN_ALL                                                                                          
    ----------------------------------------------------------------------------------------------------
    "Balance","A0000","Balance code","20100101","750","Balance code","1.35","1.65"                      
    "Balance","A0001","Balance code","20100101","600","Balance code","1.02","1.25"                      
    "Balance","A0002","Balance code","20100101","1205","Balance code","1.1","2.35"                      
    "Letter","C0000","Balance code","20100101","1803","Main mailcode","1.12","1.34"                     
    
      4 record(s) selected.

    INSERT statement
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO import.source
    SELECT MAX( CASE cn WHEN 1 THEN extracted END )
         , MAX( CASE cn WHEN 2 THEN extracted END )
         , MAX( CASE cn WHEN 3 THEN extracted END )
         , INSERT( INSERT(
              MAX( CASE cn WHEN 4 THEN extracted END )
            , 5 , 0 , '-' ) , 8 , 0 , '-' )
         , MAX( CASE cn WHEN 5 THEN extracted END )
         , MAX( CASE cn WHEN 6 THEN extracted END )
         , MAX( CASE cn WHEN 7 THEN extracted END )
         , MAX( CASE cn WHEN 8 THEN extracted END )
     FROM  import.staging
     CROSS JOIN LATERAL
           (SELECT cn , del_cn
                 , LAG( del_cn , 1 , 2 )
                      OVER( ORDER BY del_cn ) AS del_lag
             FROM  (VALUES 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 ) AS f(cn)
             CROSS JOIN LATERAL
                   (VALUES COALESCE(
                              NULLIF( INSTR(column_all , '","' , 1 , cn) , 0 )
                            , LENGTH(column_all)
                           )
                   ) AS f(del_cn)
           )
     CROSS JOIN LATERAL
           (VALUES SUBSTR(column_all , del_lag + 3 , del_cn - del_lag - 3)
           ) AS f(extracted)
     GROUP BY
           column_all
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Last edited by tonkuma; 12-29-11 at 16:03. Reason: Add test data.

Tags for this Thread

Posting Permissions

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