How to Import data from a excel file

In this section we are going to disscuss the step how we can import data in database from the excel.for this section we have used the tools oracle10g,toad,excel.

Step1: First create a excel file which consists of field name like

S_NO

FNAME

MNAME

SNAME

SHORTNAME

Step2: Add a extra field

S_NO

FNAME

MNAME

SNAME

SHORTNAME

FORMULA FIELD

Step3: Add the formula in FORMULA FIELD like the below image

Press F2 then add this line

="UNION ALL SELECT '"&A2&"' AS S_NO,'"&B2&"' AS FNAME,'"&C2&"' AS MNAME,'"&D2&"' AS SNAME,'"&E2&"' AS SHORTNAME FROM DUAL"

Step4: Add this formula in all rows

Easy way copy(ctrl+c) formula and then press ctrl+shift+down arrow  all the row will be selected then paste  (ctrl+v)

Step5: Create a database with field name S_NO,FNAME,MNAME,SNAME,SHORTNAME .To know more about table creation visit here

Table creation for this session code is

CREATE TABLE INFO

(

    S_NO   INTEGER,

    FNAME  VARCHAR2(100),

    MNAME  VARCHAR2(100),

    SNAME  VARCHAR2(100),

    SHORTNAME VARCHAR2(100)

)

Step6: Copy the new added fomula field  and paste it in sql editor and remove the first union all and run the sql the whole data is in your dual table

Step7: Now write down the insert query to insert all data in table from  the dual table the sql insert look like this. To know more about sql insert visit here

insert into INFO (S_NO,FNAME,MNAME,SNAME,SHORTNAME)

 SELECT '1' AS S_NO,'William' AS FNAME,'F.' AS MNAME,'Cordes' AS SNAME,'William F. Cordes' AS SHORTNAME FROM DUAL

Step8:  Now chek  the data is in your table is correct or not by writing simple select query like this.

SELECT * FROM INFO

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: