Question posted 2011 · +10 upvotes
I have an Excel file (which has data imported from Oracle 10G Database) one of the fields is a Date Filed which has values like 28-JAN-11 03.25.11.000000000 PM ( Date field is Oracle Time Stamp(6) in Database )
When I am trying to Import that Excel file to another Oracle 10 G database (for another database/application), I get an error because the data field is not being recognized by Oracle 10G –> Import is being done by ORACLE SQL Developer (Table (field) has TIMESTAMP(6) as the datatype)
How can I import that field? For time being I made the TIMESTAMP to VARCHAR2 and its working but I could not convert that to Date field again in C# CODE ( it says not a valid date type).
Accepted answer +8 upvotes
You likely need to change your SQL Developer settings.
Tools->Preferences->Database->NLS
Modify the Timestamp Format field to conform to the data format in your Excel spreadsheet. Not how it appears in the first database, but what it looks like in your spreadsheet. For example, in my instance, the data in Excel was:
ID DT
1 05/19/2011 10:16 PM
I changed the format in preferences to MM/DD/YYYY HH:MI AM
and was able to import successfully, after initially getting the same error you reported before changing preferences.
You may want to change the way your data is imported into Excel originally if you need more precision from your time values.
2 code variants in this answer
- Variant 1 — 1 lines, starts with
Tools->Preferences->Database->NLS - Variant 2 — 2 lines, starts with
ID DT
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
.