Import Data to Oracle in 10 Easy Steps

Having problem importing huge databases from different formats to Oracle, including the latest DOCX and XLSX? Here is the solution to your worries—EMS Data Import for Oracle—a tool to import your data easily and efficiently from common file format like XLS, DBF, XML, TXT, DOC, etc. With its user-friendly interface, it lets you adjust import parameters, set the range (number of rows you want to import, skipping rows if you want), and set the time after that it should make the changes and much more. Its graphical interface provides a great relief to those who import files from DOC or TXT formats, in which you can graphically set the position and also the get the size of the variables with couple of clicks. You also have the option to change certain values during the import process, where you can specify which word has to be replaced with what. Let’s see how you can import Excel 2003 to Oracle 10g XE.

Installing the tool is pretty simple. You have to install the file downloaded from the website or purchase it. Double click on the set-up file and follow the instructions. It gets installed in minutes.

Importing data

Launch the application from All Programs>EMS>Data Import for Oracle>Data Import for Oracle. Click on Continue to proceed. To start importing data, login on the Connection Properties
Window. You should have the ‘privilege’ of inserting new data to the Oracle table. Select the database and proceed. Next select the data you want to import (Excel 2003 for us). Before that, choose ‘MS Excel’ from ‘Import from’ option. Now click on ‘Add File’ to add Excel file and the Oracle table you want to import it to. Now click on Next. Then comes the Excel Mapping feature, which sets the correspondence between the source data columns and the fields of the table according to source data format. Select the grid range for the target field, selected in the Fields list. Set the range of the data, which needs to be imported from the file. Click on ‘+’ to add the range. This is set because you are not going to import label to the database. Once mapping is done, click on Next.

In the Connection Properties box, you can even connect through Secure Shell tunnel	Set the range of the rows, that is, how many you want to import into the table

In the ‘set base format’, you can modify base data format like changing Data/Time format, choosing separator, setting representation for Boolean true, false and null values. Thereafter, in ‘Set data formats’ on next screen, select any field for ‘Field’ list and do Field tuning in case additional formatting is needed. You can also specify some replacement and click on Next. Now select the ‘Insert Mode’ if you want to skip old data present on the table and inserting new data into it. Next you will have to select the key columns from the Available columns’ list and move it to selected columns’ list. You can also choose to exclude any field. Next ‘Set final import options’. Check on ‘Commit After Done’ checkbox to commit data and set the number for records after which it should be committed. If you want to import a specific number of reports, set the number of records in the ‘Records Counter’ else leave it as it is. Now if you have a script that should be executed before or after importing, do it here, else you can finally update the Oracle database.

Besides inserting new data, you can also update or delete it in either native or universal modes	You can choose to save the log file and close the window automatically after the process is completed