How to import data from external Oracle database to Magento 2
As it turns out, neither Oracle nor most databases provide any utilities for exporting data to Magento 2. Furthermore, they don’t even have user-friendly tools for exporting proper CSV files. But how to import data from an external Oracle database to Magento 2? Good question; below, we’ve described two different approaches that will help you achieve this goal. Let’s start with community-proven practices that consume tons of time and effort and then proceed to a more user-friendly way.
Table of contents
Community-Proven Practices of Moving Oracle Bases to Magento 2
If you have a remote Oracle database which contains a product table, you can choose one of several painful ways to take the corresponding data and move it to your e-commerce store. Why are they painful, you will ask? Because of consumed time and effort, especially in comparison with the alternative approach described below. But let’s return to our mutton – community-proven practices of moving Oracle bases to Magento.
Like in case of Magento 2 MySQL data import, you have to pass three stages before your store is updated with the desired information:
- Export a CSV table from Oracle Database;
- Match table attributes to Magento 2 attributes;
- Import the table into Magento 2.
Although the procedure looks similar to the aforementioned Magento 2 MySQL data import, there is a massive difference in the implementation of the first stage. Let’s describe it in more details.
CSV Export from Oracle Database
Oracle offers a few ways to export a CSV file. Firstly, it is possible to use SQL*Plus, but it may be a painful routine since you have to set up all the formatting with the help of variables used by the SQL*Plus system. Secondly, some specialists recommend using. Thirdly, the community offers a wide array of third-party commercial tools designed for CSV export from Oracle Database. For instance, you can explore by Dell. Let’s examine the first way.
CSV Export from Oracle Database with SQL*Plus
SQL*Plus offers a fast way to spool CSV from Oracle Database. Use the following script to see the CSV output in SQL*Plus:
|
1 2 3 |
set feedback off select department_id, department_name from departments where department_id < 110; exit |
As a result, the script displays the output as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @t.sql DEPARTMENT_ID DEPARTMENT_NAME ------------- ------------------------------ 10 Administration 20 Marketing 30 Purchasing 40 Human Resources 50 Shipping 60 IT 70 Public Relations 80 Sales 90 Executive 100 Finance |
If you are using the, you will see the following picture:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> set markup csv on SQL> @t.sql "DEPARTMENT_ID","DEPARTMENT_NAME" 10,"Administration" 20,"Marketing" 30,"Purchasing" 40,"Human Resources" 50,"Shipping" 60,"IT" 70,"Public Relations" 80,"Sales" 90,"Executive" 100,"Finance" |
As for the full CSV syntax, it looks as follows:
|
1 |
SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}] |
It allows you to:
- change the delimiter from a comma;
- disable the quoting of fields.
Now, you can enable the SET MARKUP option. From the command line, use the -m option:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$ sqlplus -s -m 'csv on' cj@localhost/pdb1 @t.sql "DEPARTMENT_ID","DEPARTMENT_NAME" 10,"Administration" 20,"Marketing" 30,"Purchasing" 40,"Human Resources" 50,"Shipping" 60,"IT" 70,"Public Relations" 80,"Sales" 90,"Executive" 100,"Finance" |
It doesn’t seem that this approach is the most intuitive and user-friendly. Check the following article for further details:. You can also watch this video since it covers the same topic:
CSV Export from Oracle Database with SQL Developer
CSV export from Oracle Database with Oracle SQL Developer incorporates the same principles as we’ve just described. But the implementation is much easier. The procedure is reduced to the following steps:
- Query the necessary records in SQL developer;
- Right-click on the results window;
- Hit export;
- Select a format: CSV;
- Select a destination;
- Press “Next”;
- Press “Finish”.
Remember, the more data you need to export, the more time the process will take. Check this discussion for further information:
CSV Export from Oracle Database with Third-Party Tools
The procedure is much faster if you use a third-party utility. Just take a look at how OraLoader simplifies the process described above:
Attributes Mapping
Finally, you have a CSV file with the necessary information, but you still cannot import it to Magento 2. You’ve spent so much time exporting it from Oracle Database, and it is not yet ready for the corresponding import procedure: it is necessary to match attributes from the table to default attributes of Magento 2. If you work with a massive sheet with lots of columns, get ready to spend much more time applying the necessary changes.
It is not even possible to move data from Magento 1.x to 2.x avoiding this obstacle, and community-proven practices of moving Oracle bases to Magento are not an exception. So prepare for long and meticulous work.
The structure of a CSV file compatible with Magento 2 is fully described here: The Complete Guide to Magento 2 Product Import / Export. Read the article for further information on how to map external attributes to the default requirements of Magento 2.
Oracle Table Import
You’ve spent several more hours modifying your CSV files. Now, the most laborious work is done, but there is one more stage of importing data from an external Oracle database to Magento 2: the final import procedure itself. You can rely on the default Magento 2 import interface to complete the data migration or use a third-party import solution of your choice. If everything is ok with attributes (they are mapped correctly), you won’t see any errors transferring data from an external Oracle database to Magento 2. If the import process is interrupted, try to find the issue in your CSV file – chances are, some attributes were matched incorrectly. Fix problems and relaunch the import.
These are community-proven practices of importing data from an external Oracle database to Magento 2. As you can see, they are time-consuming and counterintuitive for people who have no programming skills. But we’ve prepared a more user-friendly way to achieve the same goal. Proceed to the next section of this article for further information.
User-Friendly Practices of Moving Oracle Bases to Magento 2
The overall complexity of moving data from external databases to Magento 2 inspired us to enhance out Improved Import & Export extension with the entirely new functionality – the ability to connect to a database and move all the necessary data to Magento 2 directly. The module reduces the number of steps needed to import the desired information, eliminates the use of CSV files from the process, and saves lots of time. Below, we shed light on how the simplified Magento 2 Oracle data import looks and feels.
Direct Oracle Database Connection
With the Improved Import & Export Magento 2 extension, the first stage of the community-proven practices becomes unnecessary. As a result, you have two stages instead of three. It means that the plugin helps to reduce at least 30% of work. In practice, the reduction is much higher since the first stage usually consumes more time and effort than two others, which are optimized for higher performance if you use our module.
Mapping & Data Import from Oracle to Magento 2
Now, let’s take a look at the improved procedure of importing data from a remote Oracle database to Magento 2. The complicated community-proven practices are reduced to the following simple steps:
- Go to your Magento admin;
- Open the interface of the Improved Import & Export Magento 2 extension;
- Create a new import profile;
- Specify an external Oracle database;
- Select tables you want to import;
- Map table attributes to Magento 2 attributes(the simplified procedure is described below);
- Update your Magento 2 database.
Note that, our extension allows creating a schedule of updates in case you need to import data from Oracle to Magento 2 on a regular basis. The process is described below in the section dedicated to the features of the Improved Import & Export plugin.
Unfortunately, neither Magento 2 nor most third-party import solutions provide a user-friendly way of matching attributes. You still have to edit data files manually, unless the Improved Import & Export extension is used. The mapping procedure which our module offers is dramatically simplified in comparison to the default one. It offers the following benefits:
- CSV files are eliminated;
- Everything is managed via a convenient backend;
- No third-party apps are used;
- Flexible schedules with cron automate updates;
- Mapping solves the issue of different attribute standards.
As a result, our module offers the easiest and fastest way to import data from a remote Oracle database into Magento 2. Now, let’s say a few words about the database and then described the Improved Import & Export Magento 2 extension more detailly.
Get Improved Import & Export Magento 2 Extension
Oracle Features & Options
Oracle Database is a multi-model database management system by Oracle Corporation widely used for running online transaction processing, data warehousing, and mixed workloads. The database is available on-premise, in the cloud, or in a hybrid environment.
The extended availability of the platform is provided by that allows read-only access to data in a physical standby database. You can leverage reporting, ad-hoc queries, data extraction, backups, and multiple other features.
Oracle’s advanced scalability is provided by such tools as – a one-node version of Oracle Real Application Clusters. It enables such capabilities as database failover and high availability, online instance migration, application continuity, etc.
With, you can easily access to in-database data mining algorithms. Partitioning of tables and indices is possible with. Next, advanced analytics functionality is provided with.
Extended Performance capabilities are possible due to,,, and.
And with, you get Transparent Data Encryption and Data Redaction security features along with multiple other enhancements.
Robust Management functionality is provided by and numerous management packs aimed at diagnostics, tuning, database lifecycle management, data masking/subsetting, cloud management, etc.
As for Magento 2, it doesn’t support Oracle Database. You can view system requirements here:.
Improved Import & Export Magento 2 Extension
The core goal of the Improved Import & Export extension is to connect Magento 2 to any third-party systems, sources, or databases. It is possible to use our module to turn your e-commerce store into a hub where data from numerous external platforms and services are synchronized. To provide broad connectivity options, we have implemented several approaches to data import/export. First of all, you can rely on file transfers connecting your store to a third-party system.
Supported File Formats
By default, Magento works with CSV files to import/export data. That’s why we’ve described the CSV export from Oracle Database at the beginning of this article. Many platforms and tools widely support the format and the Improved Import & Export Magento 2 extension is not an exception. So if you have a CSV file, you can easily import it into Magento 2 with the help of our extension.
Since most third-party import/export extensions rely on XML to achieve the same goal and this format is widely used by many services, the Improved Import & Export Magento 2 extension also works with it. Our tool can effortlessly import/export XML files synchronizing Magento 2 with external systems.
Besides, our module works with JSON. Not many third-party extensions offer similar functionality. Thus, JSON support is one of the substantial competitive advantages that our module provides. It is also necessary to mention that the Improved Import & Export Magento 2 extension works with native Excel files. It utilizes XLS and XLSX formats for various internal purposes.
Thus, our module supports the most used file formats. If they are not enough to satisfy your business goals, check alternative ways of data migration, but let’s say a few words about supported file sources first.
Supported File Sources
When it comes to data updates via files, it is necessary to specify a file source. Often, this functionality is limited even if you use a third-party module. But the Improved Import & Export Magento 2 extension supports numerous options that satisfy most business requirements.
First of all, the module provides the ability to leverage an FTP/SFTP connection resulting in that you can import/export data files from/to a local server. Since this option may not be applicable for external platforms, the Improved Import & Export Magento 2 extension provides an alternative solution – you can quickly leverage any remote server for the same purpose. Fill in the corresponding form providing our module with the required details, and it will quickly transfer all the necessary files from either a local or remote server.
The second possible source is related to the use of cloud technologies. The Improved Import & Export Magento 2 extension provides the ability to leverage Dropbox to transfer data files between two platforms. Create a new account or use an existing one to connect your store to a third-party system.
The third data source is related to import processes only. Perhaps, it is the most intuitive way to run the update. The thing is that the Improved Import & Export Magento 2 extension supports direct URL uploads. Thus, you can only specify a link to the file to import it into Magento. Also, note that our module will automatically unzip/untar all compressed data saving your time and effort.
Alternative Ways of Import/Export
If the approach mentioned above is not enough, the Improved Import & Export Magento 2 extension offers several more ways to connect your store to an external platform. For complicated integrations, it provides the ability to use REST API connections. Thus, the use of data files is eliminated since the extension establishes a direct connection between your store and the desired system. Of course, such features as schedules and mapping are still available so that you can fully automate the integration.
An alternative connection that eliminates data files is based on Google Sheets. If you have data stored within this service, the Improved Import & Export Magento 2 extension will easily move it to your website. The process is detailly illustrated below:
Now, when you know about the extended connectivity opportunities of the Improved Import & Export Magento 2 extension, we’d like to draw your attention to its core time-savers: scheduling and mapping.
Scheduling
Since our module supports cron, you can fully automated data updates. Furthermore, the extension provides the ability to create a custom schedule for every import/export process individually. You should only be familiar with the standard cron syntax.
Alternatively, it is possible to select one of the predefined intervals. But don’t worry – they can be customized as well.
Besides, the Improved Import & Export Magento 2 extension preserves the ability to launch every update manually. You can either start data transfer asynchronously despite the current schedule or create import/export profiles designed for a manual run only. The extended flexibility allows you to configure the module in accordance with your business needs. The following material illustrates the update:
Advanced Mapping Functionality
Now, let’s take a look at the extended mapping functionality of the Improved Import & Export extension. As mentioned above, the platform requires editing data files manually before every import. Our extension solves this problem entirely by introducing the opportunity to match attributes right in your Magento backend and save the created schemes for all further updates. Now, imagine manual data file editing of a document with dozens of columns and compare it with the following procedure:
You must admit that the Improved Import & Export Magento 2 extension provides much-simplified attribute matching than in case of the default Magento platform. You not only avoid continuous data editing but also complete data file preparations much faster especially if using mapping presets:
Mapping presets are predefined mapping schemes provided by the team of our specialists. We try to add as many new schemes as possible, but you can always ask our support regarding presets for your particular integration.
For more features of the Improved Import & Export Magento 2 extension, watch the following video:
As for the data import from an external Oracle database to Magento 2, it is much more comfortable with our module; and now you know why. Let’s summarize the benefits of user-friendly practices of moving Oracle bases to Magento 2:
- The direct connection between Magento 2 and Oracle;
- File transfers are eliminated – no file editing;
- User-friendly one-time mapping;
- Schedules and fully automated import into Magento 2.
If you also want to leverage these benefits, quickly grab your extension copy here:
Get Improved Import & Export Magento 2 Extension