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  |
 |
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 #551364 is a reply to message #550950] |
Tue, 17 April 2012 11:33  |
|
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? 
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 00:49:38 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
.
