OraFAQ Forum: SQL & PL/SQL » How to transfer data from EXCEL to ORACLE

Home » SQL & PL/SQL » SQL & PL/SQL » How to transfer data from EXCEL to ORACLE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to transfer data from EXCEL to ORACLE [message #9958]

Wed, 17 December 2003 06:08

sancha
Messages: 31
Registered: October 2003

Member

Hi All,

I need to transfer data from MS-EXCEL to ORACLE.

Please suggest some way to transfer this data into oracle.

Thanks in anticipation.

Report message to a moderator

Re: How to transfer data from EXCEL to ORACLE [message #9960 is a reply to message #9958]

Wed, 17 December 2003 06:19

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

You'll save the Excel spreadsheet as a CSV (comma-separated values) file, then use SQL*Loader to import that file into a pre-existing database table.

Your SQL*Loader file (say it's called "loademp.ctl") might look like

LOAD DATA                                                                                                        
INFILE 'was_excel.csv'                                                                                
TRUNCATE                                                                                                         
INTO TABLE scott.emp                                                                                
FIELDS TERMINATED BY ","                                                                                         
OPTIONALLY ENCLOSED '"'                                                                                          
TRAILING NULLCOLS (    
    empno                       NULLIF empno=BLANKS     "TRIM(:empno)"   
,   ename                       NULLIF ename=BLANKS     "TRIM(:ename)"   
,   job                         NULLIF job=BLANKS       "TRIM(:job)"     
,   mgr                         NULLIF mgr=BLANKS       "TRIM(:mgr)"     
,   hiredate DATE 'MM/DD/YYYY'  NULLIF hiredate=BLANKS  "TRIM(:hiredate)"
,   sal                         NULLIF sal=BLANKS       "TRIM(:sal)"     
,   comm                        NULLIF comm=BLANKS      "TRIM(:comm)"    
,   deptno                      NULLIF deptno=BLANKS    "TRIM(:deptno)"                                       
)

Then, from the UNIX, prompt, you'd type