CODEWITHSUNDEEP
sqloracle-databaseora-01830
Jono_2007
Jono_2007

Reputation: 1056

Oracle: Importing spreadsheet data

I have a table which I have created in Oracle. I have three date columns in the table. I am trying to import some data into the table via oracles spreadsheet import, I have around 100 records I am looking to import, an example of the data being:

1235    |   05/4749     |   18-Mar-2006 15:26   |   19-Mar-2006   |  AM  |   19-Mar-2006 09:15  |   19-Mar-2006 10:45   |   351

However when I try import such a record into oracle I get a error:

ORA-01830: date format picture ends before converting entire input string

Can someone please advise how I should import this data successfully?

Upvotes: 0

Views: 513

Answers (1)

APC
APC

Reputation: 146349

You have dates in two formats: with hours and minutes (18-Mar-2006 15:26) and dates with no time element (19-Mar-2006). Difficult to say which one is giving you grief but it's probably the the hours and minutes. That is not a standard datetime format: Oracle expects seconds as well.

As to how you fix it? Well one way would be to adjust the input data and add :00 to every datetime value. But that isn't always possible, and perhaps isn't desirable.

The better way is to change the load process so it recognises the specific format of each column. How you do this depends on what you mean by "oracles spreadsheet import". There isn't any such thing, at least not in the database. There are various client tools which support this (from Excel or CSV): SQL Developer, Apex, SQL*Loader, external tables.

So, tell us which tool you are using and we can help you further.

Upvotes: 1

Related Questions