| Insert Excel spreadsheet into Oracle issue [message #448934] |
Thu, 25 March 2010 21:45  |
moone009
Messages: 3
Registered: March 2010
|
Junior Member |
|
|
I have been trying to inset an excel spreadsheet into oracle. I can import everything fine but whenever I need to import a sheet that includes dates it does not work.
Is there a special formatting I need to do with excel(I have tried just about every date format in excel and tried matching the date style to the one in oracle and tried doing the opposite)? I have tried every step that I can possibly think of.
whenever I format the Date in the Oracle loader it gives me the Success message for the dates but still fails to load?
ive also tried many format with the oracle loader
Maybe I missed something small?
Any ideas?
|
|
|
|
|
|
| Re: Insert Excel spreadsheet into Oracle issue [message #449061 is a reply to message #448934] |
Fri, 26 March 2010 08:38  |
moone009
Messages: 3
Registered: March 2010
|
Junior Member |
|
|
I'm sorry I am very new with Oracle.
I have been trying to import an excel .xls spreadsheet into oracle. I can import everything fine but whenever I need to import a sheet that includes dates it does not work.
Is there a special formatting I need to do with excel(THE DATE COLUMN??)(I have tried just about every date format in excel and tried matching the date style to the one in oracle and tried doing the opposite)? I have tried every step that I can possibly think of
When I format the date this way MMDDYYYY:HH24:MI:SS in the Oracle Importer it loads but then I get this Error; SQL Error:ORA-01858: a non-numeric character was found where a numeric was expected.
I've also tried many format with the oracle loader.
I have even exported data directly from a table that has DATES and then simply changed one of the account numbers and try to import the excel sheet back into the table and it still does not work.
Maybe I missed something small?
Any ideas?
|
|
|
|
|
|
| Re: Insert Excel spreadsheet into Oracle issue [message #449086 is a reply to message #449078] |
Fri, 26 March 2010 09:48 |
moone009
Messages: 3
Registered: March 2010
|
Junior Member |
|
|
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Im sorry I really dont know how to put this I guess.
When I need to import data into an oracle table(SQL DEVELOPER)sometimes I do it by using an excel spreadsheet in the .xls format. This works perfectly everytime except when I have dates, in my spreadsheet then I have to go into SQL Developer and write a query to do this.
Is there a special way I need to format the date column because I believe I have tried every way so far?
This would be a sample row from a sheet that I would like to import by right clicking on the table in sql developer and choosing import. I have made sure to format everything in sql developer while going through the import steps.
SA_ID SA_REL_TYPE_CD EFFDT
0062000749 'WW' 01-OCT-02
|
|
|
|
| Re: Insert Excel spreadsheet into Oracle issue [message #449089 is a reply to message #448934] |
Fri, 26 March 2010 10:14 |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I suspect your life would be a lot easier if you converted the .xls to a .csv. Then you'd be able to tell for sure what the format the date is actually stored in.
Past that it's difficult to say. You'd have to show us:
1) What format the raw data is in.
2) What exact steps you took to import it.
3) What happens then.
I would point out that this format:
MMDDYYYY:HH24:MI:SS
Does in no way match this format:
01-OCT-02
Bear in mind that most of us here don't use sqldeveloper to import data. We'ed normally get a csv file and then use either sqlloader or an external table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
