CODEWITHSUNDEEP
oracle-databasedateoracle-sqldeveloper
sreekem bose
sreekem bose

Reputation: 471

Date format while importing table in sql developer

I am trying to import details from a csv file which i am creating through BI Publisher query from a table.

In the query , I have a date formatted column:

effective_start_date

1951-01-01T00:00:00.000+00:00 
1901-01-01T00:00:00.000+00:00

Now when i am importing the entire table in excel. How should i import in sql developer in such a way that the date format comes as 'DD-MON-YYYY'

Right now few records for date are getting imprted in table as null !

Also my targer column format is not an editable screen in sql devleoper

Upvotes: 1

Views: 16957

Answers (1)

MT0
MT0

Reputation: 167822

Oracle Setup:

CREATE TABLE TEST_LOAD ( id INT, effective_start_date DATE );

CSV:

ID,EFFECTIVE_START_DATE
1,1951-01-01T00:00:00.000+00:00
2,1901-01-01T00:00:00.000+00:00

Import (SQL Developer 4.1.0.19):

  • In the connections panel, right-click on the table name and choose "Import Data..."
  • Browse to the csv file then click "Next >".
  • Select "Insert Script" as the insert method and click "Next >".
  • Click "Next >" to accept the default selected columns.
  • In the column definitions, EFFECTIVE_START_DATE will have a warning triangle next to it and will say "Data is not compatible with column definition...". Change the format to YYYY-MM-DD"T00:00:00.000+00:00" then click "Next >"
  • Click "Finish"
  • This should bring up a dialog saying "Task successful and sent to worksheet."
  • Optionally: Do a find/replace on the script replacing to_date\(('.*?'), '.*?'\) with CAST( TO_TIMESTAMP_TZ( $1, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM' ) AS DATE ) and select the regular expression option and do a "Replace all".
  • Run the script (press F5).

Output

SELECT * FROM TEST_LOAD;

ID EFFECTIVE_START_DATE
-- --------------------
 1 1951-01-01 00:00:00
 2 1901-01-01 00:00:00

(The format of the date will depend on your NLS settings.)

Upvotes: 3

Related Questions