Home » SQL & PL/SQL » SQL & PL/SQL » Import data from excel to oracle Db
Import data from excel to oracle Db [message #668705] Mon, 12 March 2018 01:27 Go to next message
malu
Messages: 14
Registered: December 2017
Junior Member
Hello All,

I have a requirement to import data from excel to oracle Db.
i have installed XE 11g in my local machine .
I got PL SQL package as_read_xlsx written by Anton Scheffer . compiled the package .
created directory and placed an excel test.xlsx in that folder with two records
and used below query to get data.

SELECT *
FROM TABLE(as_read_xlsx.read (
as_read_xlsx.file2blob ('DOC', 'test.xlsx')))

It is not giving any error but not showing any records .
Anyone has any idea why no records are fetching .
is there any specific way to do .

Regards,
Malu
Re: Import data from excel to oracle Db [message #668709 is a reply to message #668705] Mon, 12 March 2018 03:27 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
You might want to ask the author.

Usually I'd just turn it into a csv and sqlldr it into the DB.
Re: Import data from excel to oracle Db [message #668739 is a reply to message #668709] Tue, 13 March 2018 06:08 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,

Please create the external table and load the .xls data into DB.

U can refer below link


https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
Re: Import data from excel to oracle Db [message #668766 is a reply to message #668739] Tue, 13 March 2018 13:06 Go to previous message
EdStevens
Messages: 1018
Registered: September 2013
Senior Member
msol25 wrote on Tue, 13 March 2018 06:08
Hi,

Please create the external table and load the .xls data into DB.

U can refer below link


https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
Whether or not sqlldr (as mentioned by Roachcoach) or external tables is best would be dependent on details we do not have. But either way, the process will not load a true .xls file. It must a delimited text file. Again, as mentioned by Roachcoach.
Previous Topic: select more rows and insert less
Next Topic: Help required for taking distinct count without using DISTINCT keyword
Goto Forum:
  


Current Time: Mon Dec 10 20:29:44 CST 2018