Tuesday, 12 November 2013

Import data from EXCEL to Oracle table using PL/SQL Developer

After long time I am writing this article.

Here I am explain how we can import EXCEL data into Oracle table by using PL/SQL developer.
Here is the sample EXCEL sheet.


Now create a table with the columns, "EID","ENAME" and "Salary" in the database as below.

Choose "ODBC Importer" from Tools Menu. 





Select "Excel Files" from the drop down and click on "Connect". Now choose the Excel file and click "ok".






Note : Drives can be choose from the drop down as marked in the above image

Listed the all sheets in selected Excel file. Choose the sheet which having data. We can see preview of the data from "Result Preview".



Click on "Data to Oracle" tab and select "Owner" , "Table" from drop down.



In this case I created table "Test" under "SYS" owner. After selecting the table fields will be mapped automatically, because we created the columns withe same name of the excel columns. Check the preview and click on "Import".

Now Excel data transfer into Oracle table, "TEST".










Posted by at

32 comments:

  1. Rafeeuddin Shaik19 February 2014 at 09:13

    great post !!!!

    ReplyDelete
  2. Shahbaz Ali3 April 2014 at 22:35

    great info, keep it up...

    ReplyDelete
  3. us valley28 April 2014 at 10:54

    many thanks! great info!

    ReplyDelete
  4. Surender, Rasakatla14 July 2014 at 02:54

    Thanks friend! Really helped me....

    ReplyDelete
  5. Adelard17 September 2014 at 07:07

    Thanks so much!!that was very helpful

    ReplyDelete
  6. Anonymous30 October 2014 at 21:54

    Thanks for this.

    ReplyDelete
  7. Unknown26 November 2014 at 08:49

    Cool thanks for this

    ReplyDelete
  8. Kis Attila5 April 2015 at 03:57

    It was helpful! Thank's a lot! Regards from Hungary :)

    ReplyDelete
  9. Unknown5 October 2015 at 15:13

    It was a great help. Thanks !!!

    ReplyDelete
  10. Unknown20 October 2015 at 05:56

    thanks

    ReplyDelete
  11. Bilguun10 November 2015 at 00:29

    thank you so much

    ReplyDelete
  12. Leonardo Santos15 January 2016 at 05:54

    Thanks!

    ReplyDelete
  13. Dk1 April 2016 at 00:52

    Is it also possible to create a new table from an excel file.

    ReplyDelete
  14. Kumara Swami Reddy1 April 2016 at 21:03

    Hi Dk, Could you please provide more details...

    ReplyDelete
  15. Unknown22 April 2016 at 05:33

    good explanation

    ReplyDelete
  16. Unknown10 May 2016 at 18:46

    when each record is added pl/sql keeps asking 'Do you want to continue?' you have to press yes every time. any way to get rid of that message? i have 10k records to load

    ReplyDelete
  17. Kumara Swami Reddy12 May 2016 at 01:35

    Which tool you are using? is that PL/SQL Developer?

    ReplyDelete
  18. Unknown20 June 2016 at 00:24

    Thanks, it was very useful

    ReplyDelete
  19. Unknown30 June 2016 at 06:31

    Thanks it works.. But in the beginning it shows error that table doesn't exist.. it works after clearing the value of "owner"

    ReplyDelete
  20. HomeBiz4Real28 July 2016 at 14:00

    Thank you very much. Very useful.

    ReplyDelete
  21. Unknown26 September 2018 at 05:29

    Great!

    ReplyDelete
  22. Unknown26 October 2018 at 06:44

    Thanks! Best which I found about topic.

    ReplyDelete
  23. Chandra R Matta16 April 2019 at 11:52

    This comment has been removed by the author.

    ReplyDelete
  24. Unknown29 April 2019 at 03:38

    How i insert data into oracle from excel without SQL developer
    and using PL/SQL for loop

    ReplyDelete
  25. MoeK8 May 2019 at 04:41

    when I go to ODBC I don't see anything in my list especially Excel files. I am on Win 10 and tried 32-bit and 64-bit ODBC and none worked. Do I need 64-bit ODBC driver or 32-bit for Win 10

    ReplyDelete
    Replies
    1. KY27 May 2020 at 18:59

      I am also facing same issue. No Excel Files showing up.
      Windows 10; 32-bit.

      Delete
  26. AKLILU2 June 2019 at 23:55

    Thank you , Useful information

    ReplyDelete
  27. Vale Co Xenia3 November 2019 at 21:04

    Great Article. As I read the blog I felt a tug on the heartstrings. it exhibits how much effort has been put into this.
    IEEE Projects for CSE in Big Data

    Spring Framework Corporate TRaining

    Final Year Project Centers in Chennai

    JavaScript Training in Chennai

    ReplyDelete
  28. Rama13 February 2020 at 01:40

    Thank you , it is working

    ReplyDelete
  29. Unknown29 April 2020 at 01:38

    Thank you!!! For a very long time I could not find how to do this, in TOAD it is much more convenient.

    ReplyDelete
  30. Zahidul Islam3 May 2020 at 21:54

    This comment has been removed by the author.

    ReplyDelete
  31. Zahidul Islam3 May 2020 at 21:57

    See this blog post to insert from blog excel's .xlsx file:
    https://apexrecipe.blogspot.com/2020/05/how-to-import-data-from-excels-xlsx.html

    and see blog post to insert from blog excel's .csv file:
    https://apexrecipe.blogspot.com/2020/05/how-to-import-data-from-excels-csv-file.html

    ReplyDelete

Subscribe to: Post Comments (Atom)