Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Import Data from Excel file DateTime format problem

281c6d2f-c636-4bb9-928f-b0983dc2a047Oct 8 2015 — edited Oct 9 2015

Hello, Dear Colleagues.

Help me please to deal with the next problem.

I have Excel file with field of Custom format 'dd.mm.yyyy hh:mm:ss'

05.02.2015  17:55:06

When I'm trying to import data through Oracle SQL Developer, choose appropriate Excel file, field already represented in format 'mm/dd/yyyy'

and hh:mm:ss part is not shown.

02/05/2015


How to import data in format of 'dd.mm.yyyy hh:mm:ss' from Excel file?

Thanks.

have a nice day.

Comments

thatJeffSmith-Oracle

You have to define the NLS DATE FORMAT that describes your date as it's shown in Excel so we can build the INSERT string.

41_import6.png

Hello, Jeff.

Thank you for answer.

Please, look at the screenshots. Here is how it look in Excel. There is fields of Custom format 'dd.mm.yyyy hh:mm:ss'

orcl1.png

While exporting in Oracle SQL Developer on step 1 it's already represented in format 'mm/dd/yyyy'

and 'hh:mm:ss' part is not shown.

orcl.png

Although I have configured NLS Date format.

orcl2.png

Thanks.

Have a nice day.

thatJeffSmith-Oracle

you have to come up with an Oracle date format that matches how the dates are written in your spreadsheet

DD/MM/YYYY

Sorry, Jeff. I don't understand, what do you mean.

I need to change settings in SQL Developer, or Excel?

Thanks.

thatJeffSmith-Oracle

you don't need to change settings ANYWHERE

in the wizard, you need to describe to sql developer how the text string in Excel can be interpreted as a DATE to Oracle

pretend you were writing an INSERT statement with that exact string, and you were going to use the TO_DATE() function. What would be the date format you would use in the TO_DATE call to let Oracle see your string as a date?

281c6d2f-c636-4bb9-928f-b0983dc2a047

Jeff, format will be as in Excel spreadsheet - 'dd.mm.yyyy hh:mm:ss'

The strange thing, at step 1 wizard already does not see part hh:mm:ss

I think the matter in Excel, because I converted .xls to .csv and successfully imported as timestamp 'dd.mm.yyyy hh:mm:ss'

But still interesting, why does not worked with .xls

Could you will try to create excel spreadsheet with field with custom format 'dd.mm.yyyy hh:mm:ss' - for example with '05.02.2015  17:55:06' value and import with SQL Developer?

Thanks.

Ok...this is an xlsx file

otn_test.png

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 6 2015
Added on Oct 8 2015
7 comments
10,152 views