Home » RDBMS Server » Server Utilities » second enclosure string not present in sqlldr
second enclosure string not present in sqlldr [message #550823] Thu, 12 April 2012 11:29 Go to next message
samrush
Messages: 6
Registered: April 2012
Junior Member
Hi everyone,
I am new to sqlldr and am facing a problem.
I have to load a .csv file(I have no control over the format) into my staging table one of the fields in the .csv is mapped to a coloumn with clob datatype(DESCRIPTION).
The data in the descricption feild mostly contains Emails.
Hence it contains lot of newline character.
The newline character problem is solved when i use continueif last in my control file. but when i try to load the file it gives me second enclosure string not present error. I read about it and found
Cause: The logical end of record or the end of a LOBFILE was reached before a second enclosure delimiter was found.

Action: Correct the datafile to include the missing delimiter.

But the data file has the delimeterit is not missing.
The only difference i see is that particular line(which gives me an error) starts with a newline character
,"         
abcdabcdabcdabcdabcdabcdabcd
abcdabcdabcd
abcd
asjdfhskfhdlkhaf
dfjdklfjkdfjafjalfd",


I am not sure what i am doing wrong here.
I would appreciate any kind of help.
Thank you!


SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 12 11:42:10 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

OS: Windows 7



table structure
CREATE TABLE STG1_PROGRAM_TEST
  (
    "PROJECT_NUMBER"   VARCHAR2(1000 BYTE),
    "WORK_START"       VARCHAR2(1000 BYTE),
    "WORK_END"         VARCHAR2(1000 BYTE),
    "ASSIGNMENT_GROUP" VARCHAR2(1000 BYTE),
    "DESCRIPTION" CLOB,
    "U_KEY_INITIATIVE_"              VARCHAR2(1000 BYTE),
    "END_DATE"                       VARCHAR2(1000 BYTE),
    "PRIORITY"                       VARCHAR2(4000 BYTE),
    "U_PROJECT_STATUS"               VARCHAR2(1000 BYTE),
    "U_NAME"                         VARCHAR2(1000 BYTE),
    "U_PROJECT_SUB_PROGRAM_LEAD"     VARCHAR2(1000 BYTE),
    "U_REASON_FOR_NOT_PROGRESSING"   VARCHAR2(1000 BYTE),
    "STATE"                          VARCHAR2(1000 BYTE),
    "U_STATUS_DEFINITION"            VARCHAR2(4000 BYTE),
    "U_SUB_PROGRAM__COMPLETED__SUMM" VARCHAR2(1000 BYTE),
    "U_SUBPROGRAM_SUBOBJECTIVE_1"    VARCHAR2(1000 BYTE),
    "U_SUBPROGRAM_SUBOBJECTIVE_1___" VARCHAR2(1000 BYTE),
    "U_SUBPROGRAM_SUBOBJECTIVE_2"    VARCHAR2(1000 BYTE),
    "U_SUBPROGRAM_SUBOBJECTIVE_2___" VARCHAR2(1000 BYTE),
    "WORK_NOTES"                     VARCHAR2(4000 BYTE),
    "ACTIVE"                         VARCHAR2(1000 BYTE),
    "ACTIVITY_DUE"                   VARCHAR2(1000 BYTE),
    "WORK_COST"                      VARCHAR2(1000 BYTE),
    "WORK_DURATION"                  VARCHAR2(1000 BYTE),
    "WORK_EFFORT"                    VARCHAR2(1000 BYTE),
    "COMMENTS"                       VARCHAR2(4000 BYTE),
    "APPROVAL"                       VARCHAR2(1000 BYTE),
    "APPROVAL_HISTORY"               VARCHAR2(1000 BYTE),
    "APPROVAL_SET"                   VARCHAR2(1000 BYTE),
    "ASSIGNED_TO"                    VARCHAR2(1000 BYTE),
    "BUDGET_COST"                    VARCHAR2(1000 BYTE),
    "BUSINESS_CASE"                  VARCHAR2(1000 BYTE),
    "BUSINESS_DURATION"              VARCHAR2(1000 BYTE),
    "U_BUSINESS_SERVICE"             VARCHAR2(1000 BYTE),
    "CATEGORY"                       VARCHAR2(1000 BYTE),
    "CLOSE_NOTES"                    VARCHAR2(1000 BYTE),
    "CLOSED_BY"                      VARCHAR2(1000 BYTE),
    "U_COMMENTS_AND_WORK_NOTES"      VARCHAR2(4000 BYTE),
    "COMPANY"                        VARCHAR2(1000 BYTE),
    "CMDB_CI"                        VARCHAR2(1000 BYTE),
    "U_CONTACT_PHONE"                VARCHAR2(1000 BYTE),
    "CONTACT_TYPE"                   VARCHAR2(1000 BYTE),
    "CORRELATION_ID"                 VARCHAR2(1000 BYTE),
    "CORRELATION_DISPLAY"            VARCHAR2(1000 BYTE),
    "SYS_CREATED_ON"                 VARCHAR2(1000 BYTE),
    "SYS_CREATED_BY"                 VARCHAR2(1000 BYTE),
    "CRITICAL_PATH"                  VARCHAR2(1000 BYTE),
    "U_CUSTOMER_NAME"                VARCHAR2(1000 BYTE),
    "U_CUSTOMER_REGION"              VARCHAR2(1000 BYTE),
    "U_DEAL_NAME"                    VARCHAR2(1000 BYTE),
    "U_DEAL_SIZE"                    VARCHAR2(1000 BYTE),
    "DELIVERY_PLAN"                  VARCHAR2(1000 BYTE),
    "DELIVERY_TASK"                  VARCHAR2(1000 BYTE),
    "DOCUMENTATION"                  VARCHAR2(1000 BYTE),
    "SYS_DOMAIN"                     VARCHAR2(1000 BYTE),
    "DUE_DATE"                       VARCHAR2(1000 BYTE),
    "CALENDAR_DURATION"              VARCHAR2(1000 BYTE),
    "ESCALATION"                     VARCHAR2(1000 BYTE),
    "COST"                           VARCHAR2(1000 BYTE),
    "FOLLOW_UP"                      VARCHAR2(1000 BYTE),
    "U_FUNCTION"                     VARCHAR2(1000 BYTE),
    "GROUP_LIST"                     VARCHAR2(1000 BYTE),
    "HTML_DESCRIPTION"               VARCHAR2(1000 BYTE),
    "U_ITIL_WATCH_LIST"              VARCHAR2(1000 BYTE),
    "IMPACT"                         VARCHAR2(1000 BYTE),
    "KNOWLEDGE"                      VARCHAR2(1000 BYTE),
    "LOCATION"                       VARCHAR2(1000 BYTE),
    "MADE_SLA"                       VARCHAR2(1000 BYTE),
    "VALUE"                          VARCHAR2(1000 BYTE),
    "OPENED_AT"                      VARCHAR2(1000 BYTE),
    "OPENED_BY"                      VARCHAR2(1000 BYTE),
    "PROJECT_ORDER"                  VARCHAR2(1000 BYTE),
    "PARENT"                         VARCHAR2(1000 BYTE),
    "PERCENT_COMPLETE"               VARCHAR2(1000 BYTE),
    "PHASE"                          VARCHAR2(1000 BYTE),
    "DURATION"                       VARCHAR2(1000 BYTE),
    "EFFORT"                         VARCHAR2(1000 BYTE),
    "START_DATE"                     VARCHAR2(1000 BYTE),
    "U_POC"                          VARCHAR2(1000 BYTE),
    "PM_PORTFOLIO"                   VARCHAR2(1000 BYTE),
    "PROJECT_MANAGER"                VARCHAR2(1000 BYTE),
    "TOP_TASK"                       VARCHAR2(1000 BYTE),
    "ROI"                            VARCHAR2(1000 BYTE),
    "U_REASON_FOR_CANCELLATION"      VARCHAR2(1000 BYTE),
    "U_REASON_FOR_PROJECT"           VARCHAR2(1000 BYTE),
    "REASSIGNMENT_COUNT"             VARCHAR2(1000 BYTE),
    "U_RELATED_INCIDENTS"            VARCHAR2(1000 BYTE),
    "REMAINING_DURATION"             VARCHAR2(1000 BYTE),
    "REMAINING_EFFORT"               VARCHAR2(1000 BYTE),
    "U_REOPEN_COUNT"                 VARCHAR2(1000 BYTE),
    "U_COMPLETE_BY_DATE"             VARCHAR2(1000 BYTE),
    "U_REQUESTING_ORGANIZATION"      VARCHAR2(1000 BYTE),
    "U_ORGANIZATION"                 VARCHAR2(1000 BYTE),
    "U_REQUESTOR"                    VARCHAR2(1000 BYTE),
    "RISK"                           VARCHAR2(1000 BYTE),
    "RISK_COST"                      VARCHAR2(1000 BYTE),
    "ROLLUP"                         VARCHAR2(1000 BYTE),
    "U_SAE_PMO_POC"                  VARCHAR2(1000 BYTE),
    "U_SAE_TECHNICAL_POC"            VARCHAR2(1000 BYTE),
    "SLA_DUE"                        VARCHAR2(1000 BYTE),
    "SCHEDULE"                       VARCHAR2(1000 BYTE),
    "SHORT_DESCRIPTION"              VARCHAR2(1000 BYTE),
    "SKILLS"                         VARCHAR2(1000 BYTE),
    "SPONSOR"                        VARCHAR2(1000 BYTE),
    "U_STAKEHOLDERS"                 VARCHAR2(1000 BYTE),
    "SUBCATEGORY"                    VARCHAR2(1000 BYTE),
    "U_TARGET_SYSTEM"                VARCHAR2(1000 BYTE),
    "SYS_CLASS_NAME"                 VARCHAR2(1000 BYTE),
    "U_TEMPLATE"                     VARCHAR2(1000 BYTE),
    "TIME_CONSTRAINT"                VARCHAR2(1000 BYTE),
    "TIME_WORKED"                    VARCHAR2(1000 BYTE),
    "U_TYPE"                         VARCHAR2(1000 BYTE),
    "SYS_UPDATED_ON"                 VARCHAR2(1000 BYTE),
    "SYS_UPDATED_BY"                 VARCHAR2(1000 BYTE),
    "SYS_MOD_COUNT"                  VARCHAR2(1000 BYTE),
    "UPON_APPROVAL"                  VARCHAR2(1000 BYTE),
    "UPON_REJECT"                    VARCHAR2(1000 BYTE),
    "URGENCY"                        VARCHAR2(1000 BYTE),
    "USER_INPUT"                     VARCHAR2(1000 BYTE),
    "WATCH_LIST"                     VARCHAR2(1000 BYTE),
    "WF_ACTIVITY"                    VARCHAR2(1000 BYTE),
    "CLOSED_AT"                      VARCHAR2(1000 BYTE),
    "U_ARTIFACTS2"                   VARCHAR2(1000 BYTE),
    "U_ARTIFACTS"                    VARCHAR2(1000 BYTE),
    "EXPECTED_START"                 VARCHAR2(1000 BYTE)
)



Control File
OPTIONS(skip=1)
LOAD DATA
INFILE 'C:\Projects.csv' 
CONTINUEIF LAST PRESERVE !='"'

INTO TABLE STG1_PROGRAM_TEST
TRUNCATE
FIELDS 
TERMINATED BY ','  
  
TRAILING NULLCOLS
(
    PROJECT_NUMBER   CHAR(1000) OPTIONALLY ENCLOSED BY '"' ,
    ASSIGNMENT_GROUP CHAR(1000) OPTIONALLY ENCLOSED BY '"',
    U_NAME                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_KEY_INITIATIVE_              CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    STATE                          CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WORK_START       		   CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WORK_END                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    DESCRIPTION	                   CHAR(99999)ENCLOSED BY '"',
    END_DATE                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PRIORITY                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_PROJECT_STATUS               CHAR(1000)OPTIONALLY ENCLOSED BY '"' ,
    U_PROJECT_SUB_PROGRAM_LEAD     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_REASON_FOR_NOT_PROGRESSING   CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_STATUS_DEFINITION            CHAR(4000)OPTIONALLY ENCLOSED BY '"',
    U_SUB_PROGRAM__COMPLETED__SUMM CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_SUBPROGRAM_SUBOBJECTIVE_1    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_SUBPROGRAM_SUBOBJECTIVE_1___ CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_SUBPROGRAM_SUBOBJECTIVE_2    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_SUBPROGRAM_SUBOBJECTIVE_2___ CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WORK_NOTES                     CHAR(4000)OPTIONALLY ENCLOSED BY '"',
    ACTIVE                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    ACTIVITY_DUE                   CHAR(4000)OPTIONALLY ENCLOSED BY '"',
    WORK_COST                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WORK_DURATION                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WORK_EFFORT                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    COMMENTS                       CHAR(4000)OPTIONALLY ENCLOSED BY '"',
    APPROVAL                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    APPROVAL_HISTORY               CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    APPROVAL_SET                   CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    ASSIGNED_TO                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    BUDGET_COST                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    BUSINESS_CASE                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    BUSINESS_DURATION              CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_BUSINESS_SERVICE             CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CATEGORY                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CLOSE_NOTES                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CLOSED_BY                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_COMMENTS_AND_WORK_NOTES      CHAR(4000)OPTIONALLY ENCLOSED BY '"',
    COMPANY                        CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CMDB_CI                        CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_CONTACT_PHONE                CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CONTACT_TYPE                   CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CORRELATION_ID                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CORRELATION_DISPLAY            CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_CREATED_ON                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_CREATED_BY                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CRITICAL_PATH                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_CUSTOMER_NAME                CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_CUSTOMER_REGION              CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_DEAL_NAME                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_DEAL_SIZE                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    DELIVERY_PLAN                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    DELIVERY_TASK                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    DOCUMENTATION                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_DOMAIN                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    DUE_DATE                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CALENDAR_DURATION              CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    ESCALATION                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    COST                           CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    FOLLOW_UP                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_FUNCTION                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    GROUP_LIST                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    HTML_DESCRIPTION               CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_ITIL_WATCH_LIST              CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    IMPACT                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    KNOWLEDGE                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    LOCATION                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    MADE_SLA                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    VALUE                          CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    OPENED_AT                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    OPENED_BY                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PROJECT_ORDER                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PARENT                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PERCENT_COMPLETE               CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PHASE                          CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    DURATION                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    EFFORT                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    START_DATE                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_POC                          CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PM_PORTFOLIO                   CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    PROJECT_MANAGER                CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    TOP_TASK                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    ROI                            CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_REASON_FOR_CANCELLATION      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_REASON_FOR_PROJECT           CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    REASSIGNMENT_COUNT             CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_RELATED_INCIDENTS            CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    REMAINING_DURATION             CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    REMAINING_EFFORT               CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_REOPEN_COUNT                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_COMPLETE_BY_DATE             CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_REQUESTING_ORGANIZATION      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_ORGANIZATION                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_REQUESTOR                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    RISK                           CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    RISK_COST                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    ROLLUP                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_SAE_PMO_POC                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_SAE_TECHNICAL_POC            CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SLA_DUE                        CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SCHEDULE                       CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SHORT_DESCRIPTION              CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SKILLS                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SPONSOR                        CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_STAKEHOLDERS                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SUBCATEGORY                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_TARGET_SYSTEM                CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_CLASS_NAME                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_TEMPLATE                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    TIME_CONSTRAINT                CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    TIME_WORKED                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_TYPE                         CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_UPDATED_ON                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_UPDATED_BY                 CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    SYS_MOD_COUNT                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    UPON_APPROVAL                  CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    UPON_REJECT                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    URGENCY                        CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    USER_INPUT                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WATCH_LIST                     CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    WF_ACTIVITY                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    CLOSED_AT                      CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_ARTIFACTS2                   CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    U_ARTIFACTS                    CHAR(1000)OPTIONALLY ENCLOSED BY '"',
    EXPECTED_START                 CHAR(1000)OPTIONALLY ENCLOSED BY '"'


  
 )


Log File
SQL*Loader: Release 11.2.0.1.0 - Production on Thu Apr 12 11:44:34 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   c:\test1.ctl
Data File:      C:\Projects.csv
  Bad File:     c:\Projects.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   Last non-white character != 0X22(character '"')
                Preserving continuation characters as data
Path used:      Conventional

Table STG1_PROGRAM_TEST, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PROJECT_NUMBER                      FIRST  1000   ,  O(") CHARACTER            
ASSIGNMENT_GROUP                     NEXT  1000   ,  O(") CHARACTER            
U_NAME                               NEXT  1000   ,  O(") CHARACTER            
U_KEY_INITIATIVE_                    NEXT  1000   ,  O(") CHARACTER            
STATE                                NEXT  1000   ,  O(") CHARACTER            
WORK_START                           NEXT  1000   ,  O(") CHARACTER            
WORK_END                             NEXT  1000   ,  O(") CHARACTER            
DESCRIPTION                          NEXT 99999   ,    "  CHARACTER            
END_DATE                             NEXT  1000   ,  O(") CHARACTER            
PRIORITY                             NEXT  1000   ,  O(") CHARACTER            
U_PROJECT_STATUS                     NEXT  1000   ,  O(") CHARACTER            
U_PROJECT_SUB_PROGRAM_LEAD           NEXT  1000   ,  O(") CHARACTER            
U_REASON_FOR_NOT_PROGRESSING         NEXT  1000   ,  O(") CHARACTER            
U_STATUS_DEFINITION                  NEXT  4000   ,  O(") CHARACTER            
U_SUB_PROGRAM__COMPLETED__SUMM       NEXT  1000   ,  O(") CHARACTER            
U_SUBPROGRAM_SUBOBJECTIVE_1          NEXT  1000   ,  O(") CHARACTER            
U_SUBPROGRAM_SUBOBJECTIVE_1___       NEXT  1000   ,  O(") CHARACTER            
U_SUBPROGRAM_SUBOBJECTIVE_2          NEXT  1000   ,  O(") CHARACTER            
U_SUBPROGRAM_SUBOBJECTIVE_2___       NEXT  1000   ,  O(") CHARACTER            
WORK_NOTES                           NEXT  4000   ,  O(") CHARACTER            
ACTIVE                               NEXT  1000   ,  O(") CHARACTER            
ACTIVITY_DUE                         NEXT  4000   ,  O(") CHARACTER            
WORK_COST                            NEXT  1000   ,  O(") CHARACTER            
WORK_DURATION                        NEXT  1000   ,  O(") CHARACTER            
WORK_EFFORT                          NEXT  1000   ,  O(") CHARACTER            
COMMENTS                             NEXT  4000   ,  O(") CHARACTER            
APPROVAL                             NEXT  1000   ,  O(") CHARACTER            
APPROVAL_HISTORY                     NEXT  1000   ,  O(") CHARACTER            
APPROVAL_SET                         NEXT  1000   ,  O(") CHARACTER            
ASSIGNED_TO                          NEXT  1000   ,  O(") CHARACTER            
BUDGET_COST                          NEXT  1000   ,  O(") CHARACTER            
BUSINESS_CASE                        NEXT  1000   ,  O(") CHARACTER            
BUSINESS_DURATION                    NEXT  1000   ,  O(") CHARACTER            
U_BUSINESS_SERVICE                   NEXT  1000   ,  O(") CHARACTER            
CATEGORY                             NEXT  1000   ,  O(") CHARACTER            
CLOSE_NOTES                          NEXT  1000   ,  O(") CHARACTER            
CLOSED_BY                            NEXT  1000   ,  O(") CHARACTER            
U_COMMENTS_AND_WORK_NOTES            NEXT  4000   ,  O(") CHARACTER            
COMPANY                              NEXT  1000   ,  O(") CHARACTER            
CMDB_CI                              NEXT  1000   ,  O(") CHARACTER            
U_CONTACT_PHONE                      NEXT  1000   ,  O(") CHARACTER            
CONTACT_TYPE                         NEXT  1000   ,  O(") CHARACTER            
CORRELATION_ID                       NEXT  1000   ,  O(") CHARACTER            
CORRELATION_DISPLAY                  NEXT  1000   ,  O(") CHARACTER            
SYS_CREATED_ON                       NEXT  1000   ,  O(") CHARACTER            
SYS_CREATED_BY                       NEXT  1000   ,  O(") CHARACTER            
CRITICAL_PATH                        NEXT  1000   ,  O(") CHARACTER            
U_CUSTOMER_NAME                      NEXT  1000   ,  O(") CHARACTER            
U_CUSTOMER_REGION                    NEXT  1000   ,  O(") CHARACTER            
U_DEAL_NAME                          NEXT  1000   ,  O(") CHARACTER            
U_DEAL_SIZE                          NEXT  1000   ,  O(") CHARACTER            
DELIVERY_PLAN                        NEXT  1000   ,  O(") CHARACTER            
DELIVERY_TASK                        NEXT  1000   ,  O(") CHARACTER            
DOCUMENTATION                        NEXT  1000   ,  O(") CHARACTER            
SYS_DOMAIN                           NEXT  1000   ,  O(") CHARACTER            
DUE_DATE                             NEXT  1000   ,  O(") CHARACTER            
CALENDAR_DURATION                    NEXT  1000   ,  O(") CHARACTER            
ESCALATION                           NEXT  1000   ,  O(") CHARACTER            
COST                                 NEXT  1000   ,  O(") CHARACTER            
FOLLOW_UP                            NEXT  1000   ,  O(") CHARACTER            
U_FUNCTION                           NEXT  1000   ,  O(") CHARACTER            
GROUP_LIST                           NEXT  1000   ,  O(") CHARACTER            
HTML_DESCRIPTION                     NEXT  1000   ,  O(") CHARACTER            
U_ITIL_WATCH_LIST                    NEXT  1000   ,  O(") CHARACTER            
IMPACT                               NEXT  1000   ,  O(") CHARACTER            
KNOWLEDGE                            NEXT  1000   ,  O(") CHARACTER            
LOCATION                             NEXT  1000   ,  O(") CHARACTER            
MADE_SLA                             NEXT  1000   ,  O(") CHARACTER            
VALUE                                NEXT  1000   ,  O(") CHARACTER            
OPENED_AT                            NEXT  1000   ,  O(") CHARACTER            
OPENED_BY                            NEXT  1000   ,  O(") CHARACTER            
PROJECT_ORDER                        NEXT  1000   ,  O(") CHARACTER            
PARENT                               NEXT  1000   ,  O(") CHARACTER            
PERCENT_COMPLETE                     NEXT  1000   ,  O(") CHARACTER            
PHASE                                NEXT  1000   ,  O(") CHARACTER            
DURATION                             NEXT  1000   ,  O(") CHARACTER            
EFFORT                               NEXT  1000   ,  O(") CHARACTER            
START_DATE                           NEXT  1000   ,  O(") CHARACTER            
U_POC                                NEXT  1000   ,  O(") CHARACTER            
PM_PORTFOLIO                         NEXT  1000   ,  O(") CHARACTER            
PROJECT_MANAGER                      NEXT  1000   ,  O(") CHARACTER            
TOP_TASK                             NEXT  1000   ,  O(") CHARACTER            
ROI                                  NEXT  1000   ,  O(") CHARACTER            
U_REASON_FOR_CANCELLATION            NEXT  1000   ,  O(") CHARACTER            
U_REASON_FOR_PROJECT                 NEXT  1000   ,  O(") CHARACTER            
REASSIGNMENT_COUNT                   NEXT  1000   ,  O(") CHARACTER            
U_RELATED_INCIDENTS                  NEXT  1000   ,  O(") CHARACTER            
REMAINING_DURATION                   NEXT  1000   ,  O(") CHARACTER            
REMAINING_EFFORT                     NEXT  1000   ,  O(") CHARACTER            
U_REOPEN_COUNT                       NEXT  1000   ,  O(") CHARACTER            
U_COMPLETE_BY_DATE                   NEXT  1000   ,  O(") CHARACTER            
U_REQUESTING_ORGANIZATION            NEXT  1000   ,  O(") CHARACTER            
U_ORGANIZATION                       NEXT  1000   ,  O(") CHARACTER            
U_REQUESTOR                          NEXT  1000   ,  O(") CHARACTER            
RISK                                 NEXT  1000   ,  O(") CHARACTER            
RISK_COST                            NEXT  1000   ,  O(") CHARACTER            
ROLLUP                               NEXT  1000   ,  O(") CHARACTER            
U_SAE_PMO_POC                        NEXT  1000   ,  O(") CHARACTER            
U_SAE_TECHNICAL_POC                  NEXT  1000   ,  O(") CHARACTER            
SLA_DUE                              NEXT  1000   ,  O(") CHARACTER            
SCHEDULE                             NEXT  1000   ,  O(") CHARACTER            
SHORT_DESCRIPTION                    NEXT  1000   ,  O(") CHARACTER            
SKILLS                               NEXT  1000   ,  O(") CHARACTER            
SPONSOR                              NEXT  1000   ,  O(") CHARACTER            
U_STAKEHOLDERS                       NEXT  1000   ,  O(") CHARACTER            
SUBCATEGORY                          NEXT  1000   ,  O(") CHARACTER            
U_TARGET_SYSTEM                      NEXT  1000   ,  O(") CHARACTER            
SYS_CLASS_NAME                       NEXT  1000   ,  O(") CHARACTER            
U_TEMPLATE                           NEXT  1000   ,  O(") CHARACTER            
TIME_CONSTRAINT                      NEXT  1000   ,  O(") CHARACTER            
TIME_WORKED                          NEXT  1000   ,  O(") CHARACTER            
U_TYPE                               NEXT  1000   ,  O(") CHARACTER            
SYS_UPDATED_ON                       NEXT  1000   ,  O(") CHARACTER            
SYS_UPDATED_BY                       NEXT  1000   ,  O(") CHARACTER            
SYS_MOD_COUNT                        NEXT  1000   ,  O(") CHARACTER            
UPON_APPROVAL                        NEXT  1000   ,  O(") CHARACTER            
UPON_REJECT                          NEXT  1000   ,  O(") CHARACTER            
URGENCY                              NEXT  1000   ,  O(") CHARACTER            
USER_INPUT                           NEXT  1000   ,  O(") CHARACTER            
WATCH_LIST                           NEXT  1000   ,  O(") CHARACTER            
WF_ACTIVITY                          NEXT  1000   ,  O(") CHARACTER            
CLOSED_AT                            NEXT  1000   ,  O(") CHARACTER            
U_ARTIFACTS2                         NEXT  1000   ,  O(") CHARACTER            
U_ARTIFACTS                          NEXT  1000   ,  O(") CHARACTER            
EXPECTED_START                       NEXT  1000   ,  O(") CHARACTER            

value used for ROWS parameter changed from 64 to 1
Record 38: Rejected - Error on table STG1_PROGRAM_TEST, column DESCRIPTION.
second enclosure string not present
Record 39: Rejected - Error on table STG1_PROGRAM_TEST, column DESCRIPTION.
Initial enclosure character not found
Record 41: Rejected - Error on table STG1_PROGRAM_TEST, column DESCRIPTION.
second enclosure string not present
Record 42: Rejected - Error on table STG1_PROGRAM_TEST, column DESCRIPTION.
Initial enclosure character not found

Table STG1_PROGRAM_TEST:
  169 Rows successfully loaded.
  4 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 239252 bytes(1 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:           173
Total logical records rejected:         4
Total logical records discarded:        0

Run began on Thu Apr 12 11:44:34 2012
Run ended on Thu Apr 12 11:44:36 2012

Elapsed time was:     00:00:01.85
CPU time was:         00:00:00.33

[Updated on: Thu, 12 April 2012 11:32]

Report message to a moderator

Re: second enclosure string not present in sqlldr [message #550846 is a reply to message #550823] Thu, 12 April 2012 20:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You need to either simplify your problem or post a matching data set. In general, for the data that you posted, you should be using:

continueif last preserve != ','

You need to have something that you can consistently identify as either the start or end of a record or size of a record or number of lines per record.
Re: second enclosure string not present in sqlldr [message #550948 is a reply to message #550846] Fri, 13 April 2012 14:38 Go to previous messageGo to next message
samrush
Messages: 6
Registered: April 2012
Junior Member
Thank you for replying.
I have uploaded a part of my data set.
Hope this would help.
  • Attachment: sample.csv
    (Size: 27.49KB, Downloaded 1995 times)
Re: second enclosure string not present in sqlldr [message #550950 is a reply to message #550948] Fri, 13 April 2012 15:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
How many rows is that data supposed to get loaded into? I don't see any rows ending with " that should signify an end of record. I also don't see any rows beginning in ", that is supposed to be a problem. Can you simplify the problem? For example, can you provide a table, control file, and data set with just a few short columns and some rows that load properly and at least one row that does not, show the results that you get, and the results that you want instead, including whatever normally starts and ends the record?
Re: second enclosure string not present in sqlldr [message #551364 is a reply to message #550950] Tue, 17 April 2012 11:33 Go to previous messageGo to next message
samrush
Messages: 6
Registered: April 2012
Junior Member
Hi Barbara,
So i created a sample data file with 9 rows and duplicated the problem that i have with my actual data.
So in my test.csv file i have 2 column emp_num and data.
This is my .csv file
"emp_number","Data"
"A123","TESTDATA"
"A124","TESTDATA"
"A125","TESTDATA"
"A126","TESTDATA"
"A127","TESTDATA,TESTDATATESTDATATESTDATATESTDATA
TESTDATATESTDATATESTDATATESTDATA
TESTDATATESTDATATESTDATA
TESTDATA
TESTDATA"
"A128","
TESTDATATESTDATA
TESTDATATESTDATA"
"A129","TESTDATATESTDATA
TESTDATA
""TESTDATA""
TESTDATATESTDATA
TESTDATA"
"A130",""
"A131","TESTDATA"


My .ctl file
OPTIONS(skip=1)
LOAD DATA
INFILE 'C:\test.csv' 
CONTINUEIF LAST != '"'

INTO TABLE TEST
TRUNCATE
FIELDS 
TERMINATED BY ',' ENCLOSED BY '"'
  
TRAILING NULLCOLS
(
    EMP_NUM,
    DATA
)


My bad file
"A128","
TESTDATATESTDATA
TESTDATATESTDATA"
"A129","TESTDATATESTDATA
TESTDATA
""TESTDATA""
TESTDATATESTDATA
TESTDATA"


and my log file
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Apr 17 12:11:17 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   c:\loadertest.ctl
Data File:      C:\test.csv
  Bad File:     c:\test.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   Last non-white character != 0X22(character '"')
                Preserving continuation characters as data
Path used:      Conventional

Table TEST, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMP_NUM                             FIRST     *   ,    "  CHARACTER            
DATA                                 NEXT     *   ,    "  CHARACTER            

Record 6: Rejected - Error on table TEST, column DATA.
second enclosure string not present
Record 7: Rejected - Error on table TEST, column EMP_NUM.
Initial enclosure character not found
Record 8: Rejected - Error on table TEST, column DATA.
second enclosure string not present
Record 9: Rejected - Error on table TEST, column EMP_NUM.
Initial enclosure character not found

Table TEST:
  7 Rows successfully loaded.
  4 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:            11
Total logical records rejected:         4
Total logical records discarded:        0

Run began on Tue Apr 17 12:11:17 2012
Run ended on Tue Apr 17 12:11:17 2012

Elapsed time was:     00:00:00.24
CPU time was:         00:00:00.09


The correct output would be 9 records inserted in my table.
For some reason
SQLLDR does not insert these 2 fields correctly
"
TESTDATATESTDATA
TESTDATATESTDATA"

And
"TESTDATATESTDATA
TESTDATA
""TESTDATA""
TESTDATATESTDATA
TESTDATA"


What am i doing wrong over here? Confused
Re: second enclosure string not present in sqlldr [message #551368 is a reply to message #551364] Tue, 17 April 2012 12:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If, instead of:

CONTINUEIF LAST != '"'

you use:

CONTINUEIF NEXT PRESERVE (1) != '"'

then it loads all of the rows, expect the row "A129". It won't load that row because your enclosure character " is contained within the data. You need the enclosure character in order to allow the delimiter within the data, but your enclosure character needs to also not be within the data. If you have any control over what enclosure character is used, then you might try something else, like ~. Then you could use that as your enclosure character and use:

CONTINUEIF NEXT PRESERVE (1) != '~'

If you have any control over how you get your data in your input file, then it would be easiest if you had some sort of record terminator string. There is only so much that you can do with bad data.







Re: second enclosure string not present in sqlldr [message #551371 is a reply to message #551368] Tue, 17 April 2012 13:21 Go to previous messageGo to next message
samrush
Messages: 6
Registered: April 2012
Junior Member
Thank you for the reply Barbara.
I can manipulate the .csv file after its created so what is the best way to insert some sort of record terminator string in my .csv file without affecting the data?
Re: second enclosure string not present in sqlldr [message #551375 is a reply to message #551371] Tue, 17 April 2012 13:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The record terminator string should be inserted as part of the process that creates the data file. Otherwise, you just have to use notepad or some such editor and go through and manually figure out where to put each one. Where does your data come from? Most export utilities have the ability to specify various delimiters, enclosure characters, and record terminators.


Re: second enclosure string not present in sqlldr [message #551377 is a reply to message #551375] Tue, 17 April 2012 13:38 Go to previous messageGo to next message
samrush
Messages: 6
Registered: April 2012
Junior Member
Sadly I have no control over the creating process of the data file. The data is send by our client Sad .

Thank you very much for all the replay's. I appreciate all the help you have given me Smile .
Re: second enclosure string not present in sqlldr [message #551384 is a reply to message #551377] Tue, 17 April 2012 14:39 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's kind of frivolous. You should sit with a client and negotiate CSV file format. They can't just push something over the table and let you shed tears, sweat & blood over it. That's not a fair relationship between two sides.
Re: second enclosure string not present in sqlldr [message #551490 is a reply to message #551384] Wed, 18 April 2012 12:41 Go to previous messageGo to next message
samrush
Messages: 6
Registered: April 2012
Junior Member
Hi Littlefoot,
I am a junior developer at the firm and i don't think my management would appreciate if i sit with a client and negotiate CSV file format Cool

But i did go talk to my manager and explain to him the the problem the format is causing.Hopefully they can negotiate CSV file format with the client
Re: second enclosure string not present in sqlldr [message #551518 is a reply to message #551490] Wed, 18 April 2012 14:48 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's OK, I suppose.
Previous Topic: How to find estimated time to complete import (imp)
Next Topic: Execute package using sqlldr and input csv
Goto Forum:
  


Current Time: Fri Mar 29 07:43:51 CDT 2024