Hi,
I’m using toad’s sql wizard loader to load a excel doc into a table, in which i already made. I simply want to insert the contents from row 2 of the excel sheet in.
I tried googling this, but I can’t seem to a a straight answer.
The error
In case you can’t view the picture, here are my errors:
Record 2: Rejected - Error on table “JMSUSER”.“SNAPSHOTS_TEST”, column PERSTART.
ORA-01843: not a valid month
Record 3: Rejected - Error on table “JMSUSER”.“SNAPSHOTS_TEST”, column PERSTART.
ORA-01843: not a valid month
. . .
and so fourth.
I made sure the column name in the table has a correct data type format, I tried masking the month in the format mm/dd/yyyy on the page right before you execute the loader and auto-mapping the fields. Still no luck,
If anyone can help me resolve this issue, I would appreciate it.
Thanks,
please, show some lines of data (few records) and the whole .ctl file (which drives sqlloader (or command you send).
Then we’ll look.
Hi KJED_SMUK,
Before you post any kind of data to a public forum, I would first recommend checking the date values in your source data file – especially the first row. The picture you provided shows the first error as “ORA-01858: a non-numeric character was found where a numeric was expected.” It’s possible the first date value is actually in an incorrect format.
Something like that can definitely happen – especially with Excel. Excel’s formatting is generally applied as “display formatting”, rather than actually formatting the underlying data.
-John
Btw, that’s not to contradict you at all, Damir… 
We may need to look at is source files to solve his problem. I just wanted to make sure he was protected in case his company has rules about protecting sensitive data, and I figured that might be a good thing for him to check first.
Hope you’re having a great day, Damir!
Thank you for the responses!
The reason why I was getting the first error was because the first column was a header. I skipped it this time. Here is the source code
the <-- isn’t in the soucr code, but just here to let you know where the data types are DATE
OPTIONS ( SKIP=1)
LOAD DATA
INFILE ‘C:\Users\XXXX\Desktop\Massive Snapshot Project\v1_snapshots_test.csv’
BADFILE ‘C:\Users\XXXX\Desktop\Massive Snapshot Project\v1_snapshots_test.bad’
DISCARDFILE ‘C:\Users\XXXX\Desktop\Massive Snapshot Project\v1_snapshots_test.dsc’
INTO TABLE “XXXX”.“SNAPSHOTS”
APPEND
REENABLE DISABLED_CONSTRAINTS
EXCEPTIONS “XXXX”.“SNAPSHOTS”
FIELDS TERMINATED BY ‘,’
(TYPE,
ID,
NAME,
STATUS,
PLANFY,
OWNER,
SPOONDEPT,
PLANSTART,
PLANFINISH,
REQFY,
IO,
MONTH,
PERSTART, <— DATE
PEREND, <---- DATE
CAPBUDGET,
EXPBUDGET,
CAPFC,
EXPFC,
CAPACT,
EXPACT,
PLANHOURS,
ACTHOURS,
SNAPSHOTDATE <---- DATE
)
@John,No problem at all … always good intentions are accepted in all means.
@Kjed,
So, problem is solved…It is amazing how second look sometimes makes a miracle…
Cheers
@Damir I’m still getting the same errors though… other than the first one, which involved loading the header.
@Kjed,
sorry didn't realize that problem is still here ... ok let us go further.
error other than the first one,
what is the error??
show me .err log file (in the same dir as .ctl file) and some records of data.beside that, tell me all NLS* environment values (look in registry, grep by "NLS*").
We may need to look at is source files to solve his problem. I just wanted to make sure he was protected in case his company has rules about protecting sensitive data, and I figured that might be a good thing for him to check first.