Skip to Main Content
  • Questions

  • SQL loader to load data from excel spreadsheet

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vin.

Asked: January 17, 2017 - 12:44 pm UTC

Last updated: October 29, 2019 - 11:36 am UTC

Version: oracle 11g.

Viewed 10K+ times! This question is

You Asked

Hi Team,

The scenario is.

we have saved an excel file in a folder. how can i load the data from excel sheet to oracle table. i am using oracle 11g.

how can we approach this using SQL loader.

please help us guys

please post if any links to refer.


Thanks,
Vin

and Connor said...

To be read by sqlloader, the file must be saved in a CSV or similar format, ie, ascii as opposed to binary.

If you it in xlsx format, consider using AMIS's nifty little tool

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

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another tool

Stew Ashton, January 18, 2017 - 8:43 am UTC

Marc Bleron (odie63 on OTN) recently posted a tool I now use extensively.

https://github.com/mbleron/oracle/tree/master/ExcelTable

using SQL Developer

Rajeshwaran, Jeyabal, January 18, 2017 - 10:52 am UTC

Jeff Smith from SQL Developer Team has an excellent blog post on this, along with screen prints.

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

using SQL Developer - New Tables

Rajeshwaran, Jeyabal, January 18, 2017 - 10:54 am UTC

Also this shows, how to insert the data from excel into a "New Table"

http://www.thatjeffsmith.com/archive/2012/08/how-to-import-from-excel-to-a-new-oracle-table-with-sql-developer/

Vin, January 18, 2017 - 12:59 pm UTC

Please tell us the steps to be followed to load data from csv/xlsx file into DB table using SQL loader.


Post if any links.


Thanks,
Connor McDonald
January 19, 2017 - 1:10 am UTC

Um....didnt we just do that. Across the people the contributed to this post we covered:

- save the sheet as csv and use sqlloadder to load that
- use a tool from AMIS to read the xlsx file with plsql
- use a tool from Marc Bleron to do the same
- use SQL Developer


A reader, June 20, 2018 - 9:29 am UTC


How do I automate that

Shouvi kSaha, October 29, 2019 - 2:49 am UTC

My issue is repeating the refined load process from an Excel.
Even after saved the load script on windows( .sdimp file) I have to manually open the gui load widow for a table & open the saved .sdimp file & clicks few more to load the data from excel.
Is there a batch/or SQL plus command to load data into a fixed table from a fixed windows Excel file (same sheet name) & using the saved a .sdimp load control file.
What if Excel has multiple tabs to load into multiple tables
Connor McDonald
October 29, 2019 - 11:36 am UTC

In terms of automating it *and* handling multiple sheets then you probably need to head down the pipelined function path

https://github.com/mbleron/ExcelTable/blob/master/README.md


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.