EXPORT EXCEL SHEET DATA IN ORACLE VIA SQL-DEVELOPER

 EXPORT EXCEL SHEET DATA IN ORACLE VIA SQL-DEVELOPER

Its quite easy task ..you just need to build the table in database according to the type of data you have in your excel sheet.

For Example:

Lets create the test table where we include some columns as similar to excel sheet columns:

CREATE TABLE "TEST"

  (

    "STATION_CODE" VARCHAR2(50),

    "YEAR"         NUMBER(4,0),

    "MONTH"        NUMBER(2,0),

    "DAY"          NUMBER(2,0),

    "MAX_TEMP" FLOAT(126),

    "MIN_TEMP" FLOAT(126),

    "RH_8_30" FLOAT(126),

    "RH_5_30" FLOAT(126),

    "DATES" DATE

  );

2. Now just we need to Right click on the table created in sql developer and select the

IMPORT DATA OPTION .

3. Open the excel sheet need to be uploaded...

4. Select the worksheetname need to be uploaded

5. Remove the header if you don’t want to include that in database..

6.Click on next and next and  next and finish..

Its done ...easy na!!!!!!!!!!!

Now let us add some more data in the created table

U have notice(OR not) ..k

Notice that i have created a date column in the table ..this will contains the date formed by the year-month –date in excel sheet.

Now as data in the excel sheet is in character format and in database we need to combine the three column into one to make the proper date so we need to concatenate those column...

Its done like

: to_date(to_char(day)||'-'||to_char(month)||'-'||to_char( year),'DD-MM-yyyy')

And sql query will be:

update "TEST" set DATES=to_date(to_char(day)||'-'||to_char(month)||'-'||to_char( year),'DD-MM-yyyy') ;

thus We got the excel sheet data in oracle as well as created new column and combine the data in one.