Need help importing CSV (or txt) file into Oracle table

[mungthewicked]

I've been pounding my fists trying to solve this problem. I'm simply trying to take an Excel(Office 2000) file and import it into a table within Oracle. I'm using 10g client and Toad 9.7.2.5. I've converted the .xls file into a CSV file and a tab-delimited txt file and tried to import both(on separate occasions). Both means have failed. I think I'm close. I've followed this thread(

http://www.tek-tips.com/viewthread.cfm?qid=1014789&page=1)

for inspiration.

The method of importing I've been trying is Toad's SQL*Loader Wizard. I need to use the Wizard, because I'll have to show other programmers here in the future how to accomplish this task.

I'll post my attempt using the tab-delimited text file.

Here is the log file produced after I finish the Wizard:

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Feb 12 16:12:44 2009

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

Control File: C:\Documents and Settings\harrisc7\Desktop\TextToOracle.ctl
Data File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt
Bad File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad
Discard File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table "EZSDBO"."APPOINTMENT", loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 4 INTEGER
DONOR_ID NEXT 4 INTEGER
APPOINTMENT_TYPE_ID NEXT 4 INTEGER
APPOINTMENT_DATETIME NEXT * WHT DATETIME DD-MON-RR HH.MI.SSXFF AM
NUMBER_OF_CHILDREN NEXT * WHT CHARACTER
DONOR_CHECKED_IN NEXT * WHT CHARACTER
APPOINTMENT_STATUS_ID NEXT * WHT CHARACTER
OVERRIDE_ID NEXT * WHT CHARACTER
SCHEDULED_FROM_CENTER_FLAG NEXT * WHT CHARACTER
INSERTED_BY_USER_ID NEXT * WHT CHARACTER
INSERTED_DATE_TIME NEXT * WHT DATETIME DD-MON-RR HH.MI.SSXFF AM
UPDATED_BY_USER_ID NEXT * WHT CHARACTER
UPDATED_DATE_TIME NEXT * WHT DATETIME DD-MON-RR HH.MI.SSXFF AM
RECORD_VERSION NEXT * WHT CHARACTER

Record 1: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 2: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 3: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 4: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 5: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 6: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 7: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 8: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 9: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 10: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 11: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 12: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 13: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 14: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 15: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 16: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 17: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 18: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 19: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 20: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 21: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 22: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 23: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 24: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month

Record 25: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01843: not a valid month


Table "EZSDBO"."APPOINTMENT":
0 Rows successfully loaded.
25 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: 182400 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 25
Total logical records rejected: 25
Total logical records discarded: 0

Run began on Thu Feb 12 16:12:44 2009
Run ended on Thu Feb 12 16:12:45 2009

Elapsed time was: 00:00:00.52
CPU time was: 00:00:00.05



The table I'm trying to import into is called APPOINTMENT
Here's the layout of the table (Col names and constraints):
Column Name Null? Data Type
ID Y INTEGER
DONOR_ID Y INTEGER
APPOINTMENT_TYPE_ID Y INTEGER
APPOINTMENT_DATETIME Y TIMESTAMP(6)
NUMBER_OF_CHILDREN Y INTEGER
DONOR_CHECKED_IN Y VARCHAR2 (1 Byte)
APPOINTMENT_STATUS_ID Y INTEGER
OVERRIDE_ID Y INTEGER
SCHEDULED_FROM_CENTER_FLAG Y VARCHAR2 (1 Byte)
INSERTED_BY_USER_ID Y INTEGER
INSERTED_DATE_TIME Y TIMESTAMP(6)
UPDATED_BY_USER_ID Y INTEGER
UPDATED_DATE_TIME Y TIMESTAMP(6)
RECORD_VERSION Y INTEGER


I've successfully executed an INSERT statement into this table:
INSERT INTO APPOINTMENT
VALUES ( (SELECT MAX(ID) FROM APPOINTMENT) + 1, 987, 1, to_timestamp('2/13/2009 11:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF AM') , 0, 'N', 1, NULL, 'N', 2, to_timestamp('2/11/2009 2:38:12.000000 PM', 'MM/DD/YYYY HH:MI:SS.FF AM'), NULL, NULL, 1)


Once I got this INSERT statement to successfully work, I realized there was a problem with the timestamp(6) columns. I was getting the same error message when I initially attempted the INSERT statement.


Here's my control file:
LOAD DATA
INFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt'
BADFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad'
DISCARDFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc'

APPEND
INTO TABLE "EZSDBO"."APPOINTMENT"
FIELDS TERMINATED BY X'9'
(ID INTEGER,
DONOR_ID INTEGER,
APPOINTMENT_TYPE_ID INTEGER,
APPOINTMENT_DATETIME TIMESTAMP,
NUMBER_OF_CHILDREN,
DONOR_CHECKED_IN,
APPOINTMENT_STATUS_ID,
OVERRIDE_ID,
SCHEDULED_FROM_CENTER_FLAG,
INSERTED_BY_USER_ID,
INSERTED_DATE_TIME TIMESTAMP,
UPDATED_BY_USER_ID,
UPDATED_DATE_TIME TIMESTAMP,
RECORD_VERSION)


Now, I know I'm missing something because in that other thread that I referenced, there was a format next to TIMESTAMP within the control file.

So far, everything in the Wizard looks fine except for the portion that says Field mapping. The Preview fields tab shows the first 4 columns being populated and that's it. The 4th column is the first column that contains a timestamp(6).

Like I said, I think I'm close. What am I doing wrong? Accurate help is desperately needed. Thank you in advance. Hopefully, there will be more thank you's to come.

 

[SantaMufasa]

Thanks, Mung, for your thorough post. The only thing missing is a few lines of sample data (from, say, your tab-delimited data file). Once you post those data, I'm sure we can assist.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mungthewicked]

I had a feeling I had forgotten something........

Here's the tab-delimited file:
802 1400 1 12/15/2008 5:00 PM 2 Y 5 Y 1 12/15/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
803 1401 1 12/17/2008 4:45 PM 2 Y 5 1 Y 1 12/16/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
804 1402 1 12/22/2008 5:00 PM 2 N 4 Y 12/17/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
805 1403 1 12/24/2008 4:30 PM 2 Y 3 Y 12/18/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
806 1404 1 12/2/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
807 1405 1 12/6/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
808 1406 1 12/9/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
809 1407 1 12/13/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
810 1408 1 12/5/2008 11:00 AM N 2 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
811 1409 1 12/9/2008 3:00 PM Y 5 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
812 1410 1 12/12/2008 6:00 AM Y 5 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
813 1411 1 12/16/2008 4:00 PM Y 5 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
814 1412 2 12/17/2008 1:00 PM Y 5 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
815 1413 1 12/19/2008 1:00 PM N 4 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
816 1414 1 12/24/2008 1:00 PM N 4 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
817 1415 1 12/26/2008 1:00 PM N 3 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
818 1416 1 12/11/2008 8:00 AM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
819 1417 1 12/16/2008 6:00 PM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
820 1418 1 12/18/2008 7:00 AM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
821 1419 1 12/23/2008 7:00 AM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
822 1420 2 12/5/2008 9:00 AM Y 5 1 Y 1 12/5/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
823 1421 1 12/10/2008 9:00 AM Y 5 Y 12/5/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
824 1422 1 12/27/2008 9:00 AM 1 Y 5 Y 12/20/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
825 1423 1 12/30/2008 9:00 AM 1 Y 5 Y 12/20/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
826 1424 2 12/15/2008 3:00 PM N 1 12/1/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1

 

[SantaMufasa]

Mung,

I can understand your receiving errors during your SQL*Loader session...You have "TIMESTAMP" as the data type/format for "APPOINTMENT_DATETIME", "INSERTED_DATE_TIME", "UPDATED_DATE_TIME". Oracle's default format mask within SQL*Loader for the "TIMESTAMP" data type is "YYYY-MM-DD HH24:MI:SS". In each of your data values for your three columns, they certainly do not match the default format mask. (BTW, SQL*Loader has no default data/time format mask that accommodates "AM"/"PM". You should use 24-hour-clock-time format.)

So, reconcile your data with the format masks, and it should improve the outcome. If your still encounter errors, please post the error message(s) and another copy of your newly adjusted .txt data.

Cheers,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[harebrain]

Or, rather than changing your data, in the control file you can use a bind-variable with an explicit conversion to the timestamp datatype that specifies the input format of your data. E.g., change

APPOINTMENT_DATETIME TIMESTAMP,

to

APPOINTMENT_DATETIME "TO_TIMESTAMP(:appdttm,'MM/DD/YYYY HH:MI AM')",

and make similar adjustments to the rest of your timestamp input.

 

[mungthewicked]

Thank you Santa and harebrain. For the moment, I've tried harebrain's method. The input data came from an Excel file that was generated by a script from someone else. I'll probably be getting more Excel files like this in the future.

Anyway, the use of a bind-variable to explicitly convert to a timestamp datatype failed.

Here's how my Control file looks now:
*Note - I added the :SS to the parameters. I tried without :SS and with :SS in there.

Load Data
INFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt'
BADFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad'
DISCARDFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc'

APPEND
INTO TABLE "EZSDBO"."APPOINTMENT"
FIELDS TERMINATED BY X'9'
(ID INTEGER,
DONOR_ID INTEGER,
APPOINTMENT_TYPE_ID INTEGER,
APPOINTMENT_DATETIME "TO_TIMESTAMPappdttm,'MM/DD/YYYY HH:MI:SS AM')",
NUMBER_OF_CHILDREN,
DONOR_CHECKED_IN,
APPOINTMENT_STATUS_ID,
OVERRIDE_ID,
SCHEDULED_FROM_CENTER_FLAG,
INSERTED_BY_USER_ID,
INSERTED_DATE_TIME "TO_TIMESTAMPappdttm,'MM/DD/YYYY HH:MI:SS AM')",
UPDATED_BY_USER_ID,
UPDATED_DATE_TIME "TO_TIMESTAMPappdttm,'MM/DD/YYYY HH:MI:SS AM')",
RECORD_VERSION)



Here's the log:

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Feb 13 16:33:12 2009

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

Control File: C:\Documents and Settings\harrisc7\Desktop\TextToOracle.ctl
Data File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt
Bad File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad
Discard File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table "EZSDBO"."APPOINTMENT", loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 4 INTEGER
DONOR_ID NEXT 4 INTEGER
APPOINTMENT_TYPE_ID NEXT 4 INTEGER
APPOINTMENT_DATETIME NEXT * WHT CHARACTER
SQL string for column : "TO_TIMESTAMPappdttm,'MM/DD/YYYY HH:MI:SS AM')"
NUMBER_OF_CHILDREN NEXT * WHT CHARACTER
DONOR_CHECKED_IN NEXT * WHT CHARACTER
APPOINTMENT_STATUS_ID NEXT * WHT CHARACTER
OVERRIDE_ID NEXT * WHT CHARACTER
SCHEDULED_FROM_CENTER_FLAG NEXT * WHT CHARACTER
INSERTED_BY_USER_ID NEXT * WHT CHARACTER
INSERTED_DATE_TIME NEXT * WHT CHARACTER
SQL string for column : "TO_TIMESTAMPappdttm,'MM/DD/YYYY HH:MI:SS AM')"
UPDATED_BY_USER_ID NEXT * WHT CHARACTER
UPDATED_DATE_TIME NEXT * WHT CHARACTER
SQL string for column : "TO_TIMESTAMPappdttm,'MM/DD/YYYY HH:MI:SS AM')"
RECORD_VERSION NEXT * WHT CHARACTER

SQL*Loader-291: Invalid bind variable :appdttm in SQL string for column APPOINTMENT_DATETIME.




I tried Googling this error, but couldn't really tell what was wrong with the bind variable. My suspicion is that the formatting is off. The first two timestamps don't have seconds in them. The last one does plus the milliseconds. Would that be the reason for the error?

I'll also try Santa's method now.

 

[mungthewicked]

OK - I tried Santa's method. That didn't work either.

Here's the modified tab-delimited .txt file
820 1400 1 2008-12-15 17:00:00 2 Y 5 Y 1 2008-12-15 7:00:00 477 2009-2-11 12:05:00 1
821 1401 1 2008-12-17 16:45:00 2 Y 5 1 Y 1 2008-12-16 7:00:00 477 2009-2-11 12:05:00 1
822 1402 1 2008-12-22 17:00:00 2 N 4 Y 2008-12-17 7:00:00 477 2009-2-11 12:05:00 1
823 1403 1 2008-12-24 16:30:00 2 Y 3 Y 2008-12-18 7:00:00 477 2009-2-11 12:05:00 1
824 1404 1 2008-12-2 6:00:00 Y 5 2008-12-1 8:00:00 477 2009-2-11 12:05:00 1
825 1405 1 2008-12-6 6:00:00 Y 5 2008-12-1 8:00:00 477 2009-2-11 12:05:00 1
826 1406 1 2008-12-9 6:00:00 Y 5 2008-12-1 8:00:00 477 2009-2-11 12:05:00 1
827 1407 1 2008-12-13 6:00:00 Y 5 2008-12-1 8:00:00 477 2009-2-11 12:05:00 1
828 1408 1 2008-12-5 11:00:00 N 2 2008-12-5 9:00:00 477 2009-2-11 12:05:00 1
829 1409 1 2008-12-9 15:00:00 Y 5 2008-12-5 9:00:00 477 2009-2-11 12:05:00 1
830 1410 1 2008-12-12 6:00:00 Y 5 2008-12-5 9:00:00 477 2009-2-11 12:05:00 1
831 1411 1 2008-12-16 16:00:00 Y 5 2008-12-5 9:00:00 477 2009-2-11 12:05:00 1
832 1412 2 2008-12-17 13:00:00 Y 5 2008-12-16 10:00:00 477 2009-2-11 12:05:00 1
833 1413 1 2008-12-19 13:00:00 N 4 2008-12-16 10:00:00 477 2009-2-11 12:05:00 1
834 1414 1 2008-12-24 13:00:00 N 4 2008-12-16 10:00:00 477 2009-2-11 12:05:00 1
835 1415 1 2008-12-26 13:00:00 N 3 2008-12-16 10:00:00 477 2009-2-11 12:05:00 1
836 1416 1 2008-12-11 8:00:00 Y 5 2008-12-11 6:00:00 477 2009-2-11 12:05:00 1
837 1417 1 2008-12-16 18:00:00 Y 5 2008-12-11 6:00:00 477 2009-2-11 12:05:00 1
838 1418 1 2008-12-18 7:00:00 Y 5 2008-12-11 6:00:00 477 2009-2-11 12:05:00 1
839 1419 1 2008-12-23 7:00:00 Y 5 2008-12-11 6:00:00 477 2009-2-11 12:05:00 1
840 1420 2 2008-12-5 9:00:00 Y 5 1 Y 1 2008-12-5 7:00:00 477 2009-2-11 12:05:00 1
841 1421 1 2008-12-10 9:00:00 Y 5 Y 2008-12-5 7:00:00 477 2009-2-11 12:05:00 1
842 1422 1 2008-12-27 9:00:00 1 Y 5 Y 2008-12-20 7:00:00 477 2009-2-11 12:05:00 1
843 1423 1 2008-12-30 9:00:00 1 Y 5 Y 2008-12-20 7:00:00 477 2009-2-11 12:05:00 1
844 1424 2 2008-12-15 15:00:00 N 1 2008-12-1 9:00:00 477 2009-2-11 12:05:00 1





Here's the Control file:
Load Data
INFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited)2.txt'
BADFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited)2.bad'
DISCARDFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited)2.dsc'

APPEND
INTO TABLE "EZSDBO"."APPOINTMENT"
FIELDS TERMINATED BY X'9'
(ID INTEGER,
DONOR_ID INTEGER,
APPOINTMENT_TYPE_ID INTEGER,
APPOINTMENT_DATETIME TIMESTAMP,
NUMBER_OF_CHILDREN,
DONOR_CHECKED_IN,
APPOINTMENT_STATUS_ID,
OVERRIDE_ID,
SCHEDULED_FROM_CENTER_FLAG,
INSERTED_BY_USER_ID,
INSERTED_DATE_TIME TIMESTAMP,
UPDATED_BY_USER_ID,
UPDATED_DATE_TIME TIMESTAMP,
RECORD_VERSION)




Here's the log file:

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Feb 13 17:10:33 2009

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

Control File: C:\Documents and Settings\harrisc7\Desktop\TextToOracle.ctl
Data File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited)2.txt
Bad File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited)2.bad
Discard File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited)2.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table "EZSDBO"."APPOINTMENT", loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 4 INTEGER
DONOR_ID NEXT 4 INTEGER
APPOINTMENT_TYPE_ID NEXT 4 INTEGER
APPOINTMENT_DATETIME NEXT * WHT DATETIME DD-MON-RR HH.MI.SSXFF AM
NUMBER_OF_CHILDREN NEXT * WHT CHARACTER
DONOR_CHECKED_IN NEXT * WHT CHARACTER
APPOINTMENT_STATUS_ID NEXT * WHT CHARACTER
OVERRIDE_ID NEXT * WHT CHARACTER
SCHEDULED_FROM_CENTER_FLAG NEXT * WHT CHARACTER
INSERTED_BY_USER_ID NEXT * WHT CHARACTER
INSERTED_DATE_TIME NEXT * WHT DATETIME DD-MON-RR HH.MI.SSXFF AM
UPDATED_BY_USER_ID NEXT * WHT CHARACTER
UPDATED_DATE_TIME NEXT * WHT DATETIME DD-MON-RR HH.MI.SSXFF AM
RECORD_VERSION NEXT * WHT CHARACTER

Record 1: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 2: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 3: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 4: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 5: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 6: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 7: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 8: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 9: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 10: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 11: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 12: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 13: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 14: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 15: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 16: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 17: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 18: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 19: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 20: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 21: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 22: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 23: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 24: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month

Record 25: Rejected - Error on table "EZSDBO"."APPOINTMENT", column APPOINTMENT_DATETIME.
ORA-01847: day of month must be between 1 and last day of month


Table "EZSDBO"."APPOINTMENT":
0 Rows successfully loaded.
25 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: 182400 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 25
Total logical records rejected: 25
Total logical records discarded: 0

Run began on Fri Feb 13 17:10:33 2009
Run ended on Fri Feb 13 17:10:34 2009

Elapsed time was: 00:00:00.48
CPU time was: 00:00:00.07



Maybe I made a typo? It is after 5pm on a Friday. I'm burned out.

 

[harebrain]

Three things:

Where the incoming data doesn't include seconds, don't put SS in the format.

For 3 digits worth of fractional seconds, the time format is HH:MI:SS.FF[3] AM

You must use a different bind-variable name for each column. In other words, you can't use :appdttm for all of your incoming timestamp fields. Use unique names.

 

[karluk]

SantaMufasa is mistaken about the default timestamp format on your database. As you can tell from your SQL*Loader output, it is expecting a format of "DATETIME DD-MON-RR HH.MI.SSXFF AM".

Harebrain has the right idea of specifying a format string in your control file - it's much easier to modify one control file than many lines of data. Here is a sample control file that successfully loads timestamp data using a user specified format string:

LOAD DATA INFILE *
INTO TABLE test_ts (ts_col POSITION (1:26) timestamp 'MM/DD/YYYY HH:MI:SS.XFF AM') 
BEGINDATA
12/30/2008 10:00:00.000 AM

 

[harebrain]

karluk probably has the format for the milliseconds correct. I haven't used fractional seconds so that's just how I interpreted what is in my book.

However, his example is for a fixed-field format. mung has delimited data, so POSITION doesn't apply (because it will vary from one record to the next.) That's something that bothers me about Oracle's documentation of SQL*Loader: almost all the examples look like they're made to import old printed reports; very scant attention is paid to delimited (e.g., CSV) files.

And I didn't think SQL*Loader was aware of all the Oracle datatypes (like timestamp.) I'd be keen on learning that I was mistaken on that score.

 

[SantaMufasa]

SantaMufasa is mistaken about the default timestamp format on your database.

My apologies if what I posted was faulty...At the time I posted, my Oracle database was not accessible, so I relied on "facts" I had Googled from this link, which says in part,

...TIMESTAMP...enter the format string in SQL Loader control files for loading timestamps (year, month, day, hours, minutes, seconds)...The default format is "YYYY-MM-DD HH24:MI:SS".

Typically, the solutions I post are from a real-life working example that I produce. It shows what can happen if I rely on "expert opinions" (from Google, for example) that do not accompany a screen shot of a working example. Next time, I'll wait 'til I'm near my Oracle, from which I shall post a working example.



Sorry for any inconvenience.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mungthewicked]

Hi guys,

Thank you for all your replies. I'm glad everyone is in agreement on Harebrain's idea. I've tried Harebrain and Karluk's suggestions (SS where appropriate, differently named bind variables, and both millisecond formats). I seem to be having problems with the bind variables still.

I keep getting this error:

SQL*Loader: Release 10.2.0.1.0 - Production on Tue Feb 24 11:01:07 2009

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

Control File: C:\Documents and Settings\harrisc7\Desktop\ImportToOracle\TextToOracle.ctl
Data File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt
Bad File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad
Discard File: C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table "EZSDBO"."APPOINTMENT", loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 4 INTEGER
DONOR_ID NEXT 4 INTEGER
APPOINTMENT_TYPE_ID NEXT 4 INTEGER
APPOINTMENT_DATETIME NEXT * WHT CHARACTER
SQL string for column : "TO_TIMESTAMPapptdttmA, 'MM/DD/YYYY HH:MI AM')"
NUMBER_OF_CHILDREN NEXT * WHT CHARACTER
DONOR_CHECKED_IN NEXT * WHT CHARACTER
APPOINTMENT_STATUS_ID NEXT * WHT CHARACTER
OVERRIDE_ID NEXT * WHT CHARACTER
SCHEDULED_FROM_CENTER_FLAG NEXT * WHT CHARACTER
INSERTED_BY_USER_ID NEXT * WHT CHARACTER
INSERTED_DATE_TIME NEXT * WHT CHARACTER
SQL string for column : "TO_TIMESTAMPapptdttmB, 'MM/DD/YYYY HH:MI AM')"
UPDATED_BY_USER_ID NEXT * WHT CHARACTER
UPDATED_DATE_TIME NEXT * WHT CHARACTER
SQL string for column : "TO_TIMESTAMPapptdttmC, 'MM/DD/YYYY HH:MI:SS.XFF AM')"
RECORD_VERSION NEXT * WHT CHARACTER

SQL*Loader-291: Invalid bind variable :apptdttmA in SQL string for column APPOINTMENT_DATETIME.


I've tried Googling for solutions to this error message, but I get into all kinds of stuff about date fields being null and external tables, etc. A bit of it is over my head for the current moment, and these peoples' scenarios don't match mine.

One thing I do know is that there is a date for every row for every timestamp column within my .txt file. I've left nothing as NULL.

Here's my modified .ctl file
*Note - I tried using MM/DD/YYYY HH:MI:SS.XFF AM and HH:MI:SS.FF[3] AM on separate attempts. I still got the same results.


Load Data
INFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt'
BADFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad'
DISCARDFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc'

APPEND
INTO TABLE "EZSDBO"."APPOINTMENT"
FIELDS TERMINATED BY X'9'
(ID INTEGER,
DONOR_ID INTEGER,
APPOINTMENT_TYPE_ID INTEGER,
APPOINTMENT_DATETIME "TO_TIMESTAMPapptdttmA, 'MM/DD/YYYY HH:MI AM')",
NUMBER_OF_CHILDREN,
DONOR_CHECKED_IN,
APPOINTMENT_STATUS_ID,
OVERRIDE_ID,
SCHEDULED_FROM_CENTER_FLAG,
INSERTED_BY_USER_ID,
INSERTED_DATE_TIME "TO_TIMESTAMPapptdttmB, 'MM/DD/YYYY HH:MI AM')",
UPDATED_BY_USER_ID,
UPDATED_DATE_TIME "TO_TIMESTAMPapptdttmC, 'MM/DD/YYYY HH:MI:SS.XFF AM')",
RECORD_VERSION)


I've gone back to using my original .txt file that I first posted:
820 1400 1 12/15/2008 5:00 PM 2 Y 5 Y 1 12/15/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
821 1401 1 12/17/2008 4:45 PM 2 Y 5 1 Y 1 12/16/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
822 1402 1 12/22/2008 5:00 PM 2 N 4 Y 12/17/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
823 1403 1 12/24/2008 4:30 PM 2 Y 3 Y 12/18/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
824 1404 1 12/2/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
825 1405 1 12/6/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
826 1406 1 12/9/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
827 1407 1 12/13/2008 6:00 AM Y 5 12/1/2008 8:00 AM 477 2/11/2009 12:05:00.000 PM 1
828 1408 1 12/5/2008 11:00 AM N 2 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
829 1409 1 12/9/2008 3:00 PM Y 5 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
830 1410 1 12/12/2008 6:00 AM Y 5 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
831 1411 1 12/16/2008 4:00 PM Y 5 12/5/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1
832 1412 2 12/17/2008 1:00 PM Y 5 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
833 1413 1 12/19/2008 1:00 PM N 4 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
834 1414 1 12/24/2008 1:00 PM N 4 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
835 1415 1 12/26/2008 1:00 PM N 3 12/16/2008 10:00 AM 477 2/11/2009 12:05:00.000 PM 1
836 1416 1 12/11/2008 8:00 AM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
837 1417 1 12/16/2008 6:00 PM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
838 1418 1 12/18/2008 7:00 AM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
839 1419 1 12/23/2008 7:00 AM Y 5 12/11/2008 6:00 AM 477 2/11/2009 12:05:00.000 PM 1
840 1420 2 12/5/2008 9:00 AM Y 5 1 Y 1 12/5/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
841 1421 1 12/10/2008 9:00 AM Y 5 Y 12/5/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
842 1422 1 12/27/2008 9:00 AM 1 Y 5 Y 12/20/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
843 1423 1 12/30/2008 9:00 AM 1 Y 5 Y 12/20/2008 7:00 AM 477 2/11/2009 12:05:00.000 PM 1
844 1424 2 12/15/2008 3:00 PM N 1 12/1/2008 9:00 AM 477 2/11/2009 12:05:00.000 PM 1


Keep in mind that the column UPDATED_DATE_TIME within the destination table can have null in it. Some records indeed do have nothing for this column within the destination table.

 

[karluk]

Your immediate problem is that you are using the bind variables "apptdttmA", "apptdttmB" and "apptdttmC" without defining them in your control file. It's ok to refer back to, say, "TO_TIMESTAMPAPPOINTMENT_DATETIME, 'MM/DD/YYYY HH:MI AM')" because APPOINTMENT_DATETIME is one of the fields that is defined in your control file. "apptdttmA, on the other hand, isn't defined, so SQL*Loader doesn't know where to look for the data that is supposed to be converted to a timestamp.

You may have other issues as well. I see that you are relying on the tab character as a field separator. But you also have some fields, such as NUMBER_OF_CHILDREN, that show up as blanks in some input records. I'm not sure if SQL*Loader is smart enough to tab to the next blank space, treat is as a null input, and then tab to the next column.

 

[harebrain]

karluk has the right idea on the variable names; sorry for misleading you on that score.

SQLLDR does treat consecutive delimiters as representing null data. Provided your schema allows nulls, this shouldn't present a problem.

 

[mungthewicked]

Sweet! That makes a whole lot of sense about declaring the variables. I should have realized that. I had briefly thought about that, but I thought maybe Oracle allowed on-the-fly variables to be declared and initialized. I'll give that a shot later today when I get done working on some other stuff. THANKS!

 

[mungthewicked]

Hey harebrain, karluk, Santa, and anyone else,

I've been looking into bind variables whenever I can in my spare time. Lately, I've been given some higher priority tasks at work. Anyway, I can't find any examples of people declaring them in control files. All of them tell me to define the bind variables before-hand in SQL*PLUS. If there is a way to define them in a control file like you guys say, then I'd prefer to do just that. So, I thought I'd offer up an attempt of me implementing a "declaration" section within my control file. I'm sure I have something wrong - Fire away!

Load Data
INFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt'
BADFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad'
DISCARDFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc'


DECLARE
apptdttmA TIMESTAMP(6);
apptdttmB TIMESTAMP(6);
apptdttmC TIMESTAMP(6);

APPEND
INTO TABLE "EZSDBO"."APPOINTMENT"
FIELDS TERMINATED BY X'9'
(ID INTEGER,
DONOR_ID INTEGER,
APPOINTMENT_TYPE_ID INTEGER,
APPOINTMENT_DATETIME "TO_TIMESTAMPapptdttmA, 'MM/DD/YYYY HH:MI AM')",
NUMBER_OF_CHILDREN,
DONOR_CHECKED_IN,
APPOINTMENT_STATUS_ID,
OVERRIDE_ID,
SCHEDULED_FROM_CENTER_FLAG,
INSERTED_BY_USER_ID,
INSERTED_DATE_TIME "TO_TIMESTAMPapptdttmB, 'MM/DD/YYYY HH:MI AM')",
UPDATED_BY_USER_ID,
UPDATED_DATE_TIME "TO_TIMESTAMPapptdttmC, 'MM/DD/YYYY HH:MI:SS.XFF AM')",
RECORD_VERSION)


And yes......I'm tired of being an Oracle newb/noob. I need to take some Oracle classes and get certified anyway

Any help with the bind variable part would be stupendous. I'll try toad's SQL*Loader tool once it's agreed upon that the bind variables are being declared and implemented successfully. Then I'll attempt to cross that bridge of problems involving the tabs and white space.

 

[karluk]

Dear me, I'm afraid that you have really gotten seriously sidetracked on this bind variable issue. If you take a closer look at my original post in this thread, you will see that it gives you the syntax for declaring a timestamp column in SQL*Loader without using any bind variables at all. I was expecting you to apply this syntax to your own control file to see if it works with your input data, but unfortunately you got bogged down in a secondary issue. I haven't actually tested this syntax using your control file, but it most likely will work unless there are other problems beside the timestamp declarations.

Plugging my syntax into your control file, I get the following. Please try it and report your results.

Load Data
INFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).txt'
BADFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).bad'
DISCARDFILE 'C:\Documents and Settings\harrisc7\Desktop\Report Data(Edited).dsc'

APPEND
INTO TABLE "EZSDBO"."APPOINTMENT"
FIELDS TERMINATED BY X'9'
(ID INTEGER,
DONOR_ID INTEGER,
APPOINTMENT_TYPE_ID INTEGER,
APPOINTMENT_DATETIME TIMESTAMP 'MM/DD/YYYY HH:MI AM',
NUMBER_OF_CHILDREN,
DONOR_CHECKED_IN,
APPOINTMENT_STATUS_ID,
OVERRIDE_ID,
SCHEDULED_FROM_CENTER_FLAG,
INSERTED_BY_USER_ID,
INSERTED_DATE_TIME TIMESTAMP 'MM/DD/YYYY HH:MI AM',
UPDATED_BY_USER_ID,
UPDATED_DATE_TIME TIMESTAMP 'MM/DD/YYYY HH:MI:SS.XFF AM',
RECORD_VERSION)