Home » RDBMS Server » Server Utilities » Insert Excel spreadsheet into Oracle issue
Insert Excel spreadsheet into Oracle issue [message #448934] Thu, 25 March 2010 21:45 Go to next message
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 #448935 is a reply to message #448934] Thu, 25 March 2010 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Maybe I missed something small?

The file you are trying to load contains only strings.
Some of the string can be viewed as DATE datatype; but they are still strings because it is a TEXT file.

Since I don't really know what you have or exactly what you did,
I am unable to tell you how to fix it.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


What is the following date '07-08-09'?

http://www.orafaq.com/forum/?SQ=3129d96eb0088bb2bf4065df3d3b6818&t=search&srch=date&btn_submit=Search&field=all&f orum_limiter=10&search_logic=AND&sort_order=DESC&author=

[Updated on: Thu, 25 March 2010 22:21]

Report message to a moderator

Re: Insert Excel spreadsheet into Oracle issue [message #449061 is a reply to message #448934] Fri, 26 March 2010 08:38 Go to previous messageGo to next message
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 #449078 is a reply to message #449061] Fri, 26 March 2010 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Since I don't really know what you have or exactly what you did,
I am unable to tell you how to fix it.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Re: Insert Excel spreadsheet into Oracle issue [message #449086 is a reply to message #449078] Fri, 26 March 2010 09:48 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 12403
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.
Previous Topic: How to export one table to few separated dump files?
Next Topic: Impdp a table with table already present with new columns
Goto Forum:
  


Current Time: Sun Dec 04 12:40:04 CST 2016

Total time taken to generate the page: 0.13234 seconds