Forum Stats

  • 3,855,055 Users
  • 2,264,455 Discussions
  • 7,905,882 Comments

Discussions

How to read excel data and insert into table using PL/SQL procedure

Paul Susanto
Paul Susanto Member Posts: 185 Blue Ribbon
edited Sep 24, 2018 4:20AM in SQL & PL/SQL

Hi All,

Oracle Database Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

Requirement: I have an excel file  with some data in it. I want to read the excel file and eventually insert data from my excel file into the database table using Oracle PL/SQL procedures/functions. Is there any solution to my requirement.

Please note the data is present in the excel file with .xlsx extension, and not .csv.

Thanks & Regards,

Susanto Paul

Ahmed Haroon

Best Answer

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Sep 19, 2018 2:53PM
    Paul Susanto wrote:Hi All,Oracle Database Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bitRequirement: I have an excel file with some data in it. I want to read the excel file and eventually insert data from my excel file into the database table using Oracle PL/SQL procedures/functions. Is there any solution to my requirement.Please note the data is present in the excel file with .xlsx extension, and not .csv.Thanks & Regards,Susanto Paul

    Oracle has no built in capability to read raw *.xlsx formatted files.

    Excel when properly configured with ODBC can issue SQL directly against Oracle database.

    BTW any PL/SQL solution reads the OS file to reside on the Oracle DB Server system itself & not on some remote client system

  • Unknown
    edited Sep 19, 2018 3:47PM

    Sure - just read the FAQ for how to read/process Excel data.

    That question has been asked and answered many times on the forum and the FAQ has an entry that explains it.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 4,022 Silver Crown
    edited Sep 19, 2018 3:56PM

    You can connect to the Excel file as if it is an Oracle Database by using Heterogeneous Services (Oracle Gateway for ODBC).

    That way you can access the Excel contents and treat it as an Oracle database.

    The ODBC Gateway is free (no extra cost) and simple to configure and use.

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited Sep 19, 2018 5:46PM

    Is this just a one off thing? If so, most client tools such as Road, SQL developer etc have this functionality built in.

  • Paulzip
    Paulzip Member Posts: 8,759 Blue Diamond
    edited Sep 19, 2018 6:13PM Answer ✓

    Odie's Exceltable, best solution out there.

    https://github.com/mbleron/ExcelTable

    Ahmed Haroon
  • cormaco
    cormaco Member Posts: 1,990 Silver Crown
    edited Sep 20, 2018 1:32AM
    Is this just a one off thing? If so, most client tools such as Road, SQL developer etc have this functionality built in.

    You mean Toad, right?

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited Sep 20, 2018 3:14AM

    Of course! Victim of phone autocorrect!

This discussion has been closed.