-
1. Re: insert records from excel sheet
Manik Aug 16, 2016 4:05 AM (in response to user8969537)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.
-
2. Re: insert records from excel sheet
user8969537 Aug 16, 2016 5:15 AM (in response to Manik)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
-
4. Re: insert records from excel sheet
Marwim Aug 16, 2016 6:39 AM (in response to user8969537)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
-
5. Re: insert records from excel sheet
Etbin Aug 16, 2016 6:53 AM (in response to user8969537)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
-
6. Re: insert records from excel sheet
user8969537 Aug 16, 2016 8:28 AM (in response to HuaMin Chen)Hi HuaMin.
I am looking on it. let me try this way.
Thank You
Regards
Jay
-
7. Re: insert records from excel sheet
user8969537 Aug 16, 2016 8:30 AM (in response to Marwim)Hi Marwim.
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
Regards
Gunalan
-
8. Re: insert records from excel sheet
user8969537 Aug 16, 2016 8:31 AM (in response to Etbin)hi Etbin.
Here i am the only IT guy for oracle Database, so hopefully no any security issues here.
Regards
gunalan
-
9. Re: insert records from excel sheet
Marwim Aug 16, 2016 8:50 AM (in response to user8969537)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?
-
10. Re: insert records from excel sheet
Stefan Jager Aug 16, 2016 9:45 AM (in response to user8969537)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.
-
11. Re: insert records from excel sheet
Goran Stefanović Aug 16, 2016 2:47 PM (in response to user8969537)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.
-
12. Re: insert records from excel sheet
Mike Kutz Aug 16, 2016 6:05 PM (in response to user8969537)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
-
13. Re: insert records from excel sheet
user8969537 Aug 17, 2016 5:54 AM (in response to Marwim)Hi,
Yes i would like to automate it, but the data records wont be same
using .XLSX.
Thank you
-
14. Re: insert records from excel sheet
Marwim Aug 17, 2016 6:12 AM (in response to user8969537)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