Questions

How to load data from excel into Oracle Table using PL/SQL

+
1 Votes
Locked

How to load data from excel into Oracle Table using PL/SQL

Matip.Mpom
How to load data from excel into Oracle Table using PL/SQL
  • +
    0 Votes
    feral

    Im no Oracle guru however I queried what you are trying to do with google and came up with this link for you.
    http://orafaq.com/faqloadr.htm
    Hope that helps, mind you if you had tried this in the first place you might not have had to wait so long for a solution

    +
    0 Votes
    hsteeman

    There are many possibilities to do that, but probably the easiest one is via a databaselink from Oracle to Excel, after configuring ODBC (Open DataBase Connectivity). More details can be found on: http://www.e-ammar.com/Oracle_TIPS/HS/configuring_generic_database_con.htm

    Good luck!

    +
    0 Votes
    gashpole

    1. The first thing you need to do is save
    the Exel spreadsheet as a .csv file.

    2. Then create a table to hold the data
    within the Oracle database you are trying
    to insert into.

    3. Copy the csv file to the local server
    where the database is

    4. Create a control file similar to the one
    below:
    LOAD DATA
    INFILE '<path and file name of csv file>'
    INTO TABLE <your table name>
    FIELDS TERMINATED BY ','
    (
    feature_name CHAR OPTIONALLY ENCLOSED BY '"',
    feature_type CHAR,
    county CHAR,
    latitude CHAR,
    longitude CHAR,
    elevation INTEGER EXTERNAL,
    update_time DATE "YYYYMMDDHH24MI"
    )
    replace column names with correct column names and data types for your table. Remember that you need to use char instead of varchar 2 and integer for number columns save your control file as name.ctl

    5. Once you have created this file
    you need to start sql loader from the command line like this:
    sqlldr username/password@connect_string control=ctl_file.ctl log=log.log
    you should see the rows inserted and commited as they load


    I have only briefly outlined the details of how to do this here. for a better overview and training on sql loader have a look at this link

    http://examples.oreilly.com/orsqlloader/

    like I say hope this helps

    +
    0 Votes
    ToR24

    I do not believe there is an easy way to read an Excel file directly into an Oracle database using PL/SQL, especially in its native format.

    I use three different methods, depending upon the amount of data, the repetitiveness of the process, and the amount of programming I am willing to invest.

    First and easiest for the one-offs. I use the SQL*Loader method via Enterprise Manager (Maintenance, Data Movement, Move Row Data, Load Data from User Files) for the ad-hoc imports. You still need to save a copy of your spreadsheet in a text format like CSV. You will also need to stage the data in a place where the database can read it. I use EM because it walks me through the process. Once I've gone through the EM process, I save the control file, tweak it as needed, and reuse it in SQL*Plus scripts.

    Second method, where I have full control of the spreadsheet, but less control of the data because users send me the spreadsheets back with data. I create another worksheet within the same Excel file, which has locked down INSERT statements referring back to the sheet with the data. When I receive the spreadsheet, I copy and paste the INSERT statements directly into SQL*Plus, or indirectly staging them in a SQL script.

    The last method, one used rarely, is to use ODBC and Microsoft Query to get the data, then save the sheet of the Excel spreadsheet with changes as a CSV file. Next use procedures and functions in the UTL_FILE package to read each line, parse the line, convert the datatype if needed, validate the data and use the INSERT command to put the data into the appropriate table and column. Your database must allow at least read access to the location specified by the utl_file_dir parameter. Take extra care in saving the Excel data, once for the native format and once for the CSV format if you want to stage your changes with a fallback. I use this method for automated reading of data.

    http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093

  • +
    0 Votes
    feral

    Im no Oracle guru however I queried what you are trying to do with google and came up with this link for you.
    http://orafaq.com/faqloadr.htm
    Hope that helps, mind you if you had tried this in the first place you might not have had to wait so long for a solution

    +
    0 Votes
    hsteeman

    There are many possibilities to do that, but probably the easiest one is via a databaselink from Oracle to Excel, after configuring ODBC (Open DataBase Connectivity). More details can be found on: http://www.e-ammar.com/Oracle_TIPS/HS/configuring_generic_database_con.htm

    Good luck!

    +
    0 Votes
    gashpole

    1. The first thing you need to do is save
    the Exel spreadsheet as a .csv file.

    2. Then create a table to hold the data
    within the Oracle database you are trying
    to insert into.

    3. Copy the csv file to the local server
    where the database is

    4. Create a control file similar to the one
    below:
    LOAD DATA
    INFILE '<path and file name of csv file>'
    INTO TABLE <your table name>
    FIELDS TERMINATED BY ','
    (
    feature_name CHAR OPTIONALLY ENCLOSED BY '"',
    feature_type CHAR,
    county CHAR,
    latitude CHAR,
    longitude CHAR,
    elevation INTEGER EXTERNAL,
    update_time DATE "YYYYMMDDHH24MI"
    )
    replace column names with correct column names and data types for your table. Remember that you need to use char instead of varchar 2 and integer for number columns save your control file as name.ctl

    5. Once you have created this file
    you need to start sql loader from the command line like this:
    sqlldr username/password@connect_string control=ctl_file.ctl log=log.log
    you should see the rows inserted and commited as they load


    I have only briefly outlined the details of how to do this here. for a better overview and training on sql loader have a look at this link

    http://examples.oreilly.com/orsqlloader/

    like I say hope this helps

    +
    0 Votes
    ToR24

    I do not believe there is an easy way to read an Excel file directly into an Oracle database using PL/SQL, especially in its native format.

    I use three different methods, depending upon the amount of data, the repetitiveness of the process, and the amount of programming I am willing to invest.

    First and easiest for the one-offs. I use the SQL*Loader method via Enterprise Manager (Maintenance, Data Movement, Move Row Data, Load Data from User Files) for the ad-hoc imports. You still need to save a copy of your spreadsheet in a text format like CSV. You will also need to stage the data in a place where the database can read it. I use EM because it walks me through the process. Once I've gone through the EM process, I save the control file, tweak it as needed, and reuse it in SQL*Plus scripts.

    Second method, where I have full control of the spreadsheet, but less control of the data because users send me the spreadsheets back with data. I create another worksheet within the same Excel file, which has locked down INSERT statements referring back to the sheet with the data. When I receive the spreadsheet, I copy and paste the INSERT statements directly into SQL*Plus, or indirectly staging them in a SQL script.

    The last method, one used rarely, is to use ODBC and Microsoft Query to get the data, then save the sheet of the Excel spreadsheet with changes as a CSV file. Next use procedures and functions in the UTL_FILE package to read each line, parse the line, convert the datatype if needed, validate the data and use the INSERT command to put the data into the appropriate table and column. Your database must allow at least read access to the location specified by the utl_file_dir parameter. Take extra care in saving the Excel data, once for the native format and once for the CSV format if you want to stage your changes with a fallback. I use this method for automated reading of data.

    http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093