Db2 import from CSV cuts varchar leading zeros-CodePudding

I try to import data from CSV into DB2 database with db2 import command.

In CSV I have data like:

NUMBER,NAME
12345,aa
23456,bb
0012345,aa

In db2 database I have table PERSON with columns NUMBER VARCHAR(255), NAME VARCHAR(255). There is unique constraint on (NUMBER, NAME) column.

When I import with db2 IMPORT FROM "C:\UTILS\export.csv" OF DEL INSERT INTO PERSON

it imports only record 1 and 2 but record 3 is rejected as duplicate. Like leading zeros are being cut by import command, which should not take place.

Anyone knows how to make db2 import command not cutting leading zeros when importing VARCHAR column?

CodePudding user response：

The effect you describe may only happen, if you have some numeric data type for the NUMBER column like INTEGER.

-- somefile.sql contents

drop table test_imp;

create table test_imp 
(
  number 
  varchar (255)  -- comment out this line instead of the next one to get your error
  --int 
  not null
, name varchar (255) not null
, constraint test_imp_pk unique (number, name) 
);

import from export.csv of del replace into test_imp;

Please, show the result of the following command as is:

db2 -tvf somefile.sql -z somefile.sql.log

on:

> cat export.csv
12345,aa
23456,bb
0012345,aa

CodePudding user response：

Issue solved. File was saved with Excel by db admin which cut leading zeros... Thanks anyway.