Import data from MS Excel to Oracle

Advantages of using dbForge Studio for Oracle for data import

dbForge Studio for Oracle has the embedded data import and export utility. It makes the direct data import from XLS files into Oracle databases fast and user-friendly, and also simplifies organizational procedures and leads to substantial cost savings, especially when incorporated into automated workflows.

Here are some of the advantages of using the Studio for Oracle for data import:

• Import data from Excel (1997-2003) and Excel 2007 formats
• Smart and comprehensible task wizards
• Smooth and controlled data transfer process
• Robust customization options
• Templates for regular data import tasks
• Data import automation through the command line

How to import data from an Excel file into the Oracle table

The in-built utility for data migration in dbForge Studio for Oracle allows you to configure data import and export tasks with the help of the dedicated wizard, in several clicks. You can import data into a new table or an existing table.

To start configuring the task of importing data from an Excel file into Oracle, choose Import Data from the Database menu.

Note: If you want to import data into an existing database table, you can specify it at once: navigate to the necessary table and right-click on it, and then select Import Data from the menu.

After that, the Data Import wizard will appear to configure the task.

1. On the Source file tab, select the data format to import. In this scenario, choose Excel. Then, browse to the source data file. Click Next.

2. On the Destination tab, specify the details of the target database and the table. If you selected the destination table in the Database Explorer as described earlier, you will see the details at once. In case you need to add a new Oracle connection for the target database or edit an existing connection, use the corresponding options in the Wizard.

3. On the Options tab, preview the data from the source Excel file and provide additional data import parameters if necessary. You can select the custom data range, specify if you want to import the data in rows or columns, and if you want to define the header position. Click Next.

4. On the Data formats tab, check and adjust the data formats for the source Excel file. Click Next.

5. On the Mapping tab, align the source columns with their corresponding destination columns.

If you're importing data into a new table using dbForge Studio for Oracle, the software automatically creates and aligns the columns. However, when importing into an existing table, you must manually verify the mappings. The Studio auto-maps only those columns with identical names. For any mismatched columns, you'll need to map them yourself.

If the names of the source file columns don't align with those in the target table at all, the system will sequentially map them – the first source column to the first target column, and so on. To undo mapping, select Clear Mappings. To reinstate it, click Fill Mapping.

6. On the Modes tab, select the import mode. dbForge Studio for Oracle allows you to append data by adding new records to existing ones, update existing data with new inputs, delete data in the destination that matches data from the source file, and fully repopulate the destination table. Choose the method that aligns best with your specific needs. Click Next.

7. On the Output tab, you can define the data import mode. The default setting is configured for direct data import. Additionally, you have the option to generate a data import script, which can be edited as needed. This script can also be saved for future reuse.

8. On the Errors handling tab, define the way to handle errors that may happen during the data import process. Click Import.

When the data import task is finished, dbForge Studio for Oracle will notify you about the results. All the information about the process will be written into the log file.

Click Finish to close the wizard.

Note: The Studio offers a feature to save the task import settings as a template for future use. To create a template, simply click Save, which is available on every tab of the import wizard.

You can apply that template whenever needed. Simply select the previously saved template and locate your source data file. This template will automatically apply all the settings you need for the task.

Conclusion

dbForge Studio for Oracle features a powerful data migration module, ideal for importing data from Excel and other widely-used formats into Oracle databases. This module enables quick and efficient data import and export and allows precise configuration options for various work scenarios. It also offers the feature of saving the settings for future use, streamlining the setup process. Moreover, the ability to schedule and automate data import tasks greatly boosts its practicality in daily workflows.