Import from csv

Toad for Oracle
1

Toad 17.0.353

CREATE TABLE P97911_BKP_A1PPP_A1PPI_SF
(
  BAN                    NUMBER(9)              NOT NULL,
  SUBSCRIBER_NO          VARCHAR2(25 CHAR)      NOT NULL,
  SOC                    CHAR(9 CHAR)           NOT NULL,
  SOC_SEQ_NO             NUMBER(9)              NOT NULL,
  SERVICE_FTR_SEQ_NO     NUMBER(9)              NOT NULL,
  FTR_SOC_VER_NO         NUMBER(9)              NOT NULL,
  SYS_CREATION_DATE      DATE                   NOT NULL,
  SYS_UPDATE_DATE        DATE,
  OPERATOR_ID            NUMBER(9),
  APPLICATION_ID         CHAR(6 CHAR),
  DL_SERVICE_CODE        CHAR(5 CHAR),
  DL_UPDATE_STAMP        NUMBER(4),
  SOC_EFFECTIVE_DATE     DATE                   NOT NULL,
  CUSTOMER_ID            NUMBER(9)              NOT NULL,
  FEATURE_CODE           CHAR(6 CHAR)           NOT NULL,
  SERVICE_TYPE           CHAR(1 CHAR)           NOT NULL,
  SOC_LEVEL_CODE         CHAR(1 CHAR)           NOT NULL,
  FTR_EFFECTIVE_DATE     DATE                   NOT NULL,
  FTR_EFF_RSN_CODE       CHAR(1 CHAR),
  FTR_EXPIRATION_DATE    DATE,
  FTR_EXP_RSN_CODE       CHAR(1 CHAR),
  SERVICE_FTR_QTY        NUMBER(4)              NOT NULL,
  RC_WAIVER_EFF_DATE     DATE,
  RC_WAIVER_EXPR_DATE    DATE,
  RC_WAIVER_RSN          CHAR(6 CHAR),
  RC_WAIVER_OPID         NUMBER(9),
  CONV_RUN_NO            NUMBER(3),
  FTR_EFF_ISSUE_DATE     DATE,
  FTR_EXP_ISSUE_DATE     DATE,
  FTR_TRX_ID             NUMBER(9),
  FTR_PREV_SEQ_NO        NUMBER(9),
  FTR_ADD_SW_PRM         VARCHAR2(2000 CHAR),
  FTR_INS_TRX_ID         NUMBER(9),
  ADDITIONAL_INFO_AMT    NUMBER(11,2),
  ADDITIONAL_INF_EU_AMT  NUMBER(11,4),
  ADDITIONAL_INFO_TYPE   CHAR(1 CHAR),
  ADDITIONAL_INFO        VARCHAR2(20 CHAR),
  BEN                    NUMBER(5),
  CHARGE_LEVEL_CODE      CHAR(1 CHAR),
  REVENUE_CODE           CHAR(3 CHAR),
  PRODUCT_TYPE           CHAR(1 CHAR),
  MSISDN                 VARCHAR2(25 CHAR),
  FRIENDS_FAMILY_PARAM   VARCHAR2(150 CHAR),
  RC_FREQ                NUMBER(2),
  CPI_START_DATE         DATE,
  RTR_EFFECTIVE_DT       DATE,
  RTR_EXPIRATION_DT      DATE
)
TABLESPACE BATCH
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          40K
            NEXT             40K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE;

Data (just swhing first few rows...enough to understand the problem:

523606215;06803224466;PRCBOB7  ;704036853;719236779;0;16-nov-2010 21:09:22;21-sep-2014 13:55:30;1074;RT1074;82109;8211;16-nov-2010 00:00:00;523606215;A1PPP ;P;C;16-nov-2010 00:00:00;A;;;0;;;;;;16-nov-2010 00:00:00;;703336722;;;703336722;;;;;;;;A;;;;;16-nov-2010 21:09:21;
522126687;06802313713;PRCBOB8  ;704039907;719255910;0;17-nov-2010 18:12:16;21-sep-2014 13:57:20;1074;RT1074;82109;8211;17-nov-2010 00:00:00;522126687;A1PPP ;P;C;17-nov-2010 00:00:00;A;;;1;;;;;;17-nov-2010 00:00:00;;703338636;;;703338636;;;;;;;;A;;;;;17-nov-2010 00:00:00;
521404568;06506226776;PRCBOB7  ;704044345;719283394;0;18-nov-2010 19:46:37;21-sep-2014 13:57:29;1074;RT1074;82109;8211;18-nov-2010 00:00:00;521404568;A1PPP ;P;C;18-nov-2010 00:00:00;A;;;1;;;;;;18-nov-2010 00:00:00;;703341396;;;703341396;;;;;;;;A;;;;;18-nov-2010 00:00:00;
521677794;06801337516;PRCBOB7  ;704044756;719286017;0;18-nov-2010 21:23:43;21-sep-2014 13:57:29;1074;RT1074;82109;8211;18-nov-2010 00:00:00;521677794;A1PPP ;P;C;18-nov-2010 00:00:00;A;;;1;;;;;;18-nov-2010 00:00:00;;703341645;;;703341645;;;;;;;;A;;;;;18-nov-2010 00:00:00;

image
Next...

image
image690×533 49 KB

Next steps still all fine!
image
image1343×592 227 KB

Ad then comes strange missing date format (not recognized)

image
image990×635 180 KB

2

Hi Damir,

I see two problems here.

  1. The date format you should be using is dd-mmm-yyyy hh:nn:ss (we want a Delphi date format here, not Oracle date format).
  2. There is a bug in Toad here that's not accepting the dash as a date separator in the "custom" date order.

I will fix.

Workaround - Don't use "Custom" date format. This works:

image

3

Cannot ide custim as sata i jave as is

Brg
Damir (from mobile)

4

I have no idea what you just said.

Use the date format that I showed you in my screen shot and it will work.

5

Ok this is clear John. You mentiibed data...data i cannot influence...iz is what is

Brg
Damir (from mobile)

6

Ahhhhh Google corect

Brg
Damir (from mobile)

7

Sorry, I didn't mean to make the data in your file a different format. I meant, in Toad Data Import settings, you made your custom date format = dd-mon-yyyy hh24:mi:ss. It should be dd-mmm-yyyy hh:nn:ss because Toad needs Delphi date formats here, not Oracle date formats.

But you can work around it by not using CUSTOM at all, and make your date settings like my screen shot.

8

DMY format worked.
Custom (dd-mmm-yyyy hh24:mi:ss) stil not works...but let us say that problem is solve
I do not understand what then format in that part mens except it is tthe same as i have it in Grid, what should not have anything with it.
:slight_smile: Cheers.

9

Hi Damir,

The grid has nothing to do with it. The "Custom" date format wants a Delphi date format string, not an Oracle date format string.

So...

dd-mmm-yyyy hh24:mi:ss is also wrong.
dd-mmm-yyyy hh:nn:ss SHOULD work, but it does not, but as I said in my first reply:

There is a bug in Toad here that's not accepting the dash as a date separator in the "custom" date order.

I will fix.

-John