DB2 ingest of decimal data from csv

Question

i am looking for a db2 method to import a .csv file (IMPORT.CSV) like this:

2020-01-01;1.234.567,12345

in the a DB2 table MY_TABLE:

CREATE TABLE
    MY_TABLE
    (
        DTRIF DATE,
        SALDO DECIMAL(18,5)
    );

i tried "INGEST" method:

INGEST FROM FILE IMPORT.CSV FORMAT DELIMITED
  (
      $data date,
      $decimale char(18)
   ) 
   INSERT INTO MY_TABLE(dtrif,saldo)
      VALUES( 
        $data, 
        CAST(REPLACE(REPLACE($decimale, '.', ''), ',', '.') AS DECIMAL(18,5))
   );

but the result is:

    DTRIF      SALDO
2020-01-01 12345.00000

why?

If using Db2 11.5, try using an EXTERNAL TABLE to get CSV file data into Db2. ibm.com/support/knowledgecenter/SSEPGG_11.5.0/… — Paul Vernon
– Paul Vernon, Commented Jan 4, 2021 at 14:14

mao · Accepted Answer · 2020-11-27 16:52:32Z

0

The following works correctly for me (other variations are possible) with Db2-LUW v11.1.4.5:

INGEST FROM FILE ... FORMAT DELIMITED BY ';'
  (
      $data date,
      $decimale char
  )
   INSERT INTO ... (dtrif,saldo)
      VALUES( $data
            , CAST(REPLACE(REPLACE($decimale, '.', ''), ',', '.') AS DECIMAL(18,5)));

answered Nov 27, 2020 at 16:52

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DB2 ingest of decimal data from csv

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related