I am new in toad. I am trying to import data from excel file into toad for oracle but when I perform the Import execution I get this error ORA-01843.
I need help on how to resolve the problem.
@JohnDorlon
Hi Matthew.
ORA-01843 means "not a valid month", so it sounds like maybe you have an incorrect date format specified. I am happy to help diagnose this, but I need more to go on. The following would be helpful:
- Screen shots of your import settings
- A sample of the data that you are importing
- Spool SQL output so we can see which SQL produced the error. From the main menu: Database -> Spool SQL -> Spool to screen
Thanks
These are the screen shots.
I will post other screen shots separately because I am limited to one at a time as a new user
oh, I'll bump your privileges so you don't have to do one at a time.
Can you email me that Excel file in your screen shot and a "create table" statement for the table that you are trying to import into?
Send to john.dorlon@quest.com.
Thanks.
I have emailed it from mjsystemss@gmail.com
I just tried this and was able to import using Toad version 17.1. Here is what I did:
- Deleted sheet1, sheet2, sheet3 from your file, then renamed sheet4 to sheet 1. Just to make things simpler.
- Tried to import with default settings. (Mapping columns sequentially)
- I got an error that 44.75 is not a valid integer value, so I unmapped the AMI_PERCENTAGE column so it wouldn't import
- Tried to import again, and it imported without error.
Can you reproduce the problem with the file that you sent me?
It's hard to tell from your screen shot but It looks like for "First Row" you have a 1. You should change that to 2 because the first row in your spreadsheet is field names.
Also, I can tell from your screen shot that you are not using Toad version 17.1. Which version of Toad do you have?
I am also able to import using Toad version 16.2. I can tell you have 16.2 or older.
To make it work, you may have to alter your date format here.
I am using 12.5.0.99
Try setting the date format in options as shown in my screen shot.
I have set the date format.
Will it be fine to format the ami_percentage using TO_Char(ami_percentage, '99,999.99') to see if works. I need the field on the table
You should either:
Change AMI_PERCENTAGE in your database so that it can accept decimal values
OR
Change your data to import so that it does not contain decimal values.
I will use decimal then
I was also able to import that file with Toad 12.5.
I can reproduce the problem if I set "First Row" to 1. It needs to be 2 with this spreadsheet.
Thanks John, I am able to import the file now.
When I run the app I get the warning ORA-00001 Violated: Unique constraint. UserSYS_C0033571 Violated. The only Column I have with Constraint is the Column ID. But when I click yes or yes to all the file is imported.
1 Like