Ohhhhhhh. . . . . MG’ i ve never been touching my blog for around a week. But. . .
😛 it’s ok, i ve some experience about this one that helped me to import xls file to my Oracle database
i ll implement a faster method to create data from xls file
Here it’s :
1. You need to exp your .xls to .csv with save as your xls file
and change file type be comes CSV(text csv)
with Filed options :
*Character Set Unicode (UTF-8)
*Filed delimited ,
*and Text delimited “
and then OK and click save
open your .csv file with your text editor till U get data like this below :
“0061031”,”Mr”,”B”,”A”,”Williams A”
“0061032”,”Mr”,”B”,”A”,”Williams B”
“0061033”,”Mr”,”B”,”A”,”Williams C”
and on
2.Create a new file with your text editor and copas scripts below :
explanation
LOAD DATA
INFILE name.csv –>your csv path
APPEND
INTO TABLE RE.contacts –>your table name on your DB
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ –>your column name on your table
(
URN,
TITLE,
FIRST_NAME,
OTHER_NAME,
LAST_NAME
)
this is mine
LOAD DATA
INFILE name.csv
APPEND
INTO TABLE RE.contacts
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
(
URN,
TITLE,
FIRST_NAME,
OTHER_NAME,
LAST_NAME
)
save and named file with name.ctl
3.Run your name.ctl on Terminal with :
sqlldr userid=user/pass control=name.ctl
and done!
hope my reviewed could help your problem as mine . . .
have a nice day, keep learning, and see yaaa. . . . .
i’m Using
DB#Oracle DB 10g under Ubuntu Platform
Converter xls to csv #OpenOffice.org 3.1.1