how excel data can b inserted in my new oracle inventory manaement software?

i make a software for the organization and they want to convert their excel data in new oracle software.


i make their inventory management system in oracle 10 g.

Comments for how excel data can b inserted in my new oracle inventory manaement software?

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 26, 2015
Rating
starstarstarstarstar
Loading data into Oracle
by: Anonymous

It is quite common to need load data from Excel spreadsheets into an Oracle database and there are a number of ways of doing this - including some where you don't actually load the data at all!

The first method is to use the Apache Jakarta POI to create external tables to access the data in an Excel spreadsheet. This has the advantage that it can handle multiple spread sheets in the same file.
See http://www.oracle.com/technetwork/articles/saternos-tables-090560.html for more details. By using Oracle's external table interface you are keeping the data outside the database in its native format. The disadvantage is that this data is read only.

If you don't want to go down that route and you're familiar with Oracle Application Express you can create an application that will load data from a spreadsheet after its been coverted to text file (csv). This not only loads the data into the database, it also gives you a user-firenly application to manage the data. The disadvantage is that you have to convert the spread shttes into csv format. See http://docs.oracle.com/database/121/HTMDB/bldapp_wiz_using.htm#HTMDB25227 for details on how to do this.

Another tool that you can use to load data directly from an Excel spreadhsheet is Oracle's SQL Developer. This tool is very easy to use and for thios you just right click on the name of the table that you want to load and select "Import" from the menu. See http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/ for a tutorial.

We've already mentioned using Oracle's external table functionality with Apache Jakarta POI but if you have the data in csv format on the server you can use the external table functionality directly to acess the data. In this case when you create the table you define it as ORGANIZATION EXTERNAL and specify the location of the file which must be in a pre-defined Oracle directory. See the Oracle SQL Reference (http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402) for more details. As per the first method, the data is held outside the database and is not modifiable.

There is one other Oracle tool you can use - SQL*Loader which again will operate on a text file (csv format). This is a very efficient and powerful tool but can be a little difficult to understand initially. See the Oracle Database Utilities manual - http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402 - for details on using this tool.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.