insert records from excel sheet

A good option is to get input as CSV and then create an external table to point to that file and then insert into your stage/extract tables.

https://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm

Cheers,

Manik.

Read

http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

If it is a one time thing then you can use SQL Developer as mentioned above.

Otherwise there is a recent thread Insert data from excel file (.xls) to a Database Table

and a FAQ Re: 5. How do I read or write an Excel file?

Regards

Marcus

Did you consider exporting data from Excel directly (so it becomes an Excel problem ... and - exposing username and password - a security problem as well ).

(We did it years ago to demonstrate a security breach)

Regards

Etbin

Its not one time task, may be i have to do it once a week, so whats the best solution as i m still new for this task

 

The best solution? Default answer: "It depends"

The 2 links in my post showed some possibilities.

Do you want to automate it?

What is the format of the Excel, CSV/XLS,XLSX?

user8969537 wrote:

 

hi Etbin.

 

Here i am the only IT guy for oracle Database, so hopefully no any security issues here.

 

Regards

gunalan

Wrong. On so many levels completely wrong. Don't take security lightly, and especially when you use the word "hopefully" you've got a very big security problem. Fix it beforehand, not after your whole database can be found on wikileaks.

user8969537 wrote:

 

Hi Manik

 

Thank you for the feedback, this can be done in sqlplus or sqlloader?

will work for oracle database version 12.1.

 

Regards

Gunalan

Once you create .csv file from Excel file it is trivial to load it using sqlldr. Check documentation on SQL*Loader if you don't know how it works.

user8969537 wrote:

 

hi Etbin.

 

Here i am the only IT guy for oracle Database, so hopefully no any security issues here.

 

Regards

gunalan

If you create an APEX Application that uses either XLS2Collection or Excel2Collection, then you can push the responsibility of uploading the from you and onto the person that produced the file. 

MK

but the data records wont be same

 

Do you mean that you get different columns in your Excel sheets?

Is it a limited number of different sheets?

Beside this, reading XLSX can be automated

https://odieweblog.wordpress.com/2012/01/28/xml-db-events-reading-an-open-office-xml-document-xlsx/

HOWTO: Using the Oracle XMLDB Repository to Automatically Shred Windows Office Documents (Part 1)

https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/

https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/

The last option might be the simplest to implement, others like the first will read the files automatically when a new file is written into a directory.

Or you can use Heterogeneous Services as described by BluShadow Re: Read CSV/XLS file to insert into Oracle database.

So the answer is still "It depends", you have to evaluate the pros and cons of these solutions. Maybe we can help you if you give use more details on your environment (e.g. Heterogeneous Services are not a primary option on Unix systems) and the kind of data you want to read.

Regards

Marcus