How to import data from Informix to Magento 2

Let’s assume a situation when you have an update stored in a remote Informix table but need to move it to Magento 2. What is the most obvious solution? It seems that exporting a CSV file from Informix and then importing it into your e-commerce store seems the most obvious one. But what if there is a more efficient way to achieve the same goal? Let’s explore the first approach and compare it with the alternative method of importing data from Informix to Magento 2.

The Common Way of Moving Informix Tables into Magento 2

The universal algorithm of moving bases from any remote database to Magento 2 consists of three steps. Firstly, you should perform the export procedure creating a CSV output file that contains the desired data. Secondly, there is a huge stage of editing the information to make it suitable for Magento 2 requirements. Thirdly, the file can be imported into Magento 2. Below, we describe each step in more detail.

CSV Export from Informix Database

Unfortunately, Informix doesn’t offer a direct CSV export, but you can get close by using the following DELIMITER construct:

This command results in an output where fields are separated by commas. To get fields separated by double quotes modify the statement like this:

Now, you will have the desired output.

Luckily, there is a better way to achieve this goal. You need RazorSQL – an export tool for Informix. It allows creating output files in various formats including CSV. But since Magento 2 bound to this format only, we don’t list the others here. For further information, follow this link: Informix Export Tool.

Map Third-Party Attributes to Magento 2 Attributes

Now, you have to take the output file, review its content, and edit attributes and even their values if necessary. This step will provide you with a seamless import procedure. The problem is caused by various data standards used by external systems. Even Magento 2 and Magento 1 utilize different attributes so that you cannot import information directly from one platform to another.

As for attribute values, the update file may contain information which is not available in your database. Magento 2 will easily upload it, but you will need to edit everything in the admin if the data is not suitable for your catalog. Perhaps, it requires just small changes.

Import Informix Table into Magento 2

Now, when everything is suitable for the import procedure, you can leverage the default Magento 2 tools to run the update. Note that it is impossible to automate this process. Consequently, you have to repeat all these steps every time an update is required. Check the following guide to discover proper Magento 2 attributes and other nuances of the successful import process: The Complete Guide to Magento 2 Product Import / Export.

Pros & Cons

What are the pros and cons of the described approach to moving data from Informix to Magento 2? Let’s see.

Pros

As you might have already guessed, The only positive aspect is the ability to run the update by moving data from Informix to Magento 2. Yes, it is not the most efficient one. Yes, you should face lots of difficulties on your way to the desired update. But what do you want from the universal logic that can be applied to many different systems? Usually, such resolutions are far from being intuitive and user-friendly. You have to master multiple aspects of various environments before starting the process. Let’s see what negative moments are a part of the described algorithm and then fix them.

Cons

You have to work in three different environments: Informix or its export tool, a CSV file, the Magento 2 import interface. As a result, three different sets of tasks are involved, and it is not possible to automate any of them. You have to edit data files manually modifying both attributes and their values which is a time-consuming routine.

It seems that these are enough reasons to look for a better way of importing data from Informix into Magento 2, so let’s take a look at a more efficient solution.  

The Easy Way of Moving Informix Bases into Magento 2

The improved algorithm is based on a third-party Magento 2 extension, so meet the Improved Import & Export module. This tool is designed to enhance the default import and export processes of Magento 2 by introducing absolutely different approaches to each of them. Besides, it offers tons of features that are not available in Magento 2 by default. We will describe them below, but now let’s take a look at the improved algorithm of moving data from Informix to your e-commerce website.

Informix Database Connection

Unlike the universal approach, the Improved Import & Export doesn’t rely on files. The extension provides the ability to connect to the database directly. Thus, you have to neither export CSV files nor edit them! Instead, you need to create a new import profile (we describe the process below), configure it, and launch the transfer.

Magento 2 Informix Data Import

Since the direct connection doesn’t solve the problem of different attribute standards, you still have to fix the issue. And the Improved Import & Export Magento 2 extension offers several ways to do that. For instance, you can apply mapping presets – predefined schemes that match third-party attributes to ones used internally automatically. Besides, it is possible to do everything manually in a straightforward manner. And there are multiple features for editing attribute values. Everything is described further in this article.

Another important aspect of the Magento 2 Informix Data Import is the ability to automate all updates. The module provides the ability to create a schedule or set up triggers. You will find more information below.

How to Import Data From Informix to Magento 2

Now, we’d like to draw your attention to the data import process from Informix to Magento 2 on the basis of the Improved Import & Export extension. It consists of the following steps:

1. In the Magento admin, find the Improved Import & Export extension, go to its import section, and start a new job (profile) there.
2. Specify parameters required for the connection to the Informix database, map attributes, create an update schedule, and configure other options if necessary;
3. Save and launch the profile.

That’s it! These three steps replace the standard way of moving data from a remote database into Magento 2. As you can see, it is possible to save tons of time and effort connected your e-commerce store to Informix with the help of Improved Import & Export. Since the process is fully automated, you also prevent further time expenditures! Thus, this approach is much more user-friendly than the default one.

Pros & Cons

Let’s see what pros and cons the Improved Import & Export offers then it comes to the Magento 2 Informix integration.

Pros

1. You move data from Informix to Magento 2;
2. The connection between the two systems is direct;
3. Data files are eliminated;
4. CSV output is no longer necessary;
5. Manual attribute editing is still possible but can be avoided;
6. All updates are automated entirely.

Cons

The only negative aspect here is the necessity to install a third-party module. But you can always contact our support in case of any difficulties. The Firebear team will help you solve the problem. It is even possible to propose your particular changes to the module’s functionality, and we will discuss their implementation!

Thus, the installation of the Improved Import & Export extension is more a necessity than a drawback if you want to import data from Informix to Magento 2 in a more efficient way. Now, let’s take a look at the features of Informix and then proceed to a more in-depth exploration of the extension.

Get Improved Import & Export Magento 2 Extension

Informix Facts

Informix is often compared with Oracle, DB2, and Microsoft SQL Server. Being a secure embeddable database from IBM, it is positioned as a relatively low-cost product optimized for OLTP and Internet of Things data that exists separately from DB2. These are its core features:

• The primary database model of Informix is a relational DBMS;
• At the same time, it is a document and key-value store;
• The database first appeared in 1984;
• Its current release is 12.10.xC12, July 2018;
• Although the license model of Informix is commercial, there is a free developer edition;
• No cloud-based functionality is provided;
• The implementation languages of Informix include C, C++, and Java;
• Its server operating systems are AIX, HP-UX, Linux, macOS, Solaris, and Windows;
• Data scheme, typing, secondary indexes, and SQL are a part of the database;
• APIs and other access methods include JSON API, MQTT, RESTful HTTP API, JDBC, and ODBC;
• Supported programming languages are .Net, C, C++, Java, JavaScript (with Node.js), PHP, Python, and Ruby;
• Server-side scripts and triggers are supported;
• Partitioning methods are represented by sharding;
• Replication methods – by master-master and master-slave replication;
• Informix doesn’t offer an API for user-defined Map/Reduce methods;
• Consistency concepts are represented by Immediate Consistency;
• Foreign keys are supported;
• Transaction concepts are represented by ACID;
• Concurrency, durability, and in-memory capabilities are a part of Informix;
• Users can get fine-grained authentication, authorization, and auditing controls.

Improved Import & Export Magento 2 Extension

Below, we describe core features of the Improved Import & Export Magento 2 extension. This section sheds light on improvements that make the Magento 2 Informix integration as comfortable as possible. Let’s start the exploration of the automation features mentioned above.

Automated Import & Export Processes

As we’ve already written above, the Improved Import & Export Magento 2 extension provides two different ways of data transfers automation. First of all, you can create a schedule of updates. Next, the module lets you use events as triggers.

Schedules

Improved Import & Export supports cron allowing you to create any custom update schedule. Thus, you can automate data transfers from Informix to Magento 2 according to particular business needs. It is only necessary to understand the standard cron syntax. At the same time, you can select one of the predefined intervals which are also fully customizable.

Profiles that have no schedule represents another opportunity. They are useful for one-time migrations or non-periodical data transfers. At the same time, you can launch every scheduled update manually whenever you want. If a data file is provided after the planned date, there is no need to wait until the next automated import process – launch it asynchronously.

The following gif image illustrates the provided opportunities:

Events

As for event-based triggers, they have a slightly different nature but lets you automate data import processes from Informix to Magento 2 as well. Select a system event or a custom process that will be used as a trigger, create conditions necessary for the launch, and let the module do all the work for you. For further information about the feature, follow this link: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

And there are lots of various mapping tools and features designed to improve your work with attributes. The Improved Import & Export Magento 2 extension dramatically enhances your daily routine related to data editing and matching.

Mapping Presets

The easiest way of attributes mapping is based on presets. You only need to select a scheme suitable for your integration and apply it. Next, the extension will analyze all external attributes and match them to ones used internally. Your task is reduced to just a few clicks. The following gif image shows how to select a preset for the migration from Magento 1 to Magento 2:

Matching Interface

If there is no preset for your particular business case, the Improved Import & Export extension provides the ability to do everything manually. You need to specify third-party attributes and set the corresponding ones used in Magento 2 in front of them. There is also a third column that allows typing hardcoded attribute values which are added to all items automatically. The process of manual attributes matching looks as follows:

Attribute Values Mapping

And you can effortlessly match external attribute values to the corresponding internal designations. There is no need to import unnecessary data and edit it in your Magento 2 backend. The Improved Import & Export extension offers a more efficient way to do that via the same interface that we’ve just described.

Let’s take a look at a small example to illustrate the module’s functionality. For instance, a remote Informix table contains an external attribute “color” which has two values: “lime-green” and “avocado-green”. But your Magento 2 database includes only one corresponding value: “green”. With our module, you can match both external designations to the one used internally. For further information, follow this link: Attribute Values Mapping.

Attribute Values Editing

And you can freely edit multiple attribute values in bulk. Specify condition and apply one or several rules. With the Improved Import & Export extension, it is possible to add a suffix or prefix, merge or split data, etc. You can find more information here:  How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

If you need to import products from Informix, there is a possibility that they are bound to categories that don’t exist in your catalog. The Improved Import & Export Magento 2 extension lets you fix that problem by mapping external categories to ones used internally. The following gif image illustrates the process:

At the same time, you can create new categories on the fly. Both features are described here:  Category Mapping.

Attributes On The Fly

And if a file lacks some attributes, you can create them on the fly. The Improved Import & Export Magento 2 module enables you to specify parameters necessary for the automatic attribute generation. Use the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full algorithm is described here: Product attributes import.

Extended Connectivity Options

Another important aspect of our extension is the ability to establish connections in multiple different ways. Although the Magento 2 Informix integration is based on the direct integration of the two systems, numerous other options can be leveraged for transferring data.

Multiple File Standards

The Improved Import & Export extension adds numerous file formats extending the list of supported ones by Magento. While the system works with CSV by default, our plugin allows importing and exporting XML, JSON, ODS, and Excel files. Thus, the need to convert them to CSV and back is eliminated.  

Multiple File Sources

File transfers require at least one file source to complete the data exchange. The Improved Import & Export offers three options:

• The FTP/SFTP support allows you to leverage a local or remote server to transfer data.
• A Dropbox account can be utilized for the same goal.
• When it comes to import processes only, you can use a direct file URL.

Note that there is no need to unpack compressed data before import since our extension can unzip/untar it automatically.

Alternative Ways of Import & Export

In addition to file transfers, the Improved Import & Export extension lets you move data between Magento 2 and external systems directly or via third-party services. You can use such intermediaries as Google Sheets, Office 365 Excel, or Zoho Sheet. The Google Sheets integration lets you import data in the following way:

Alternatively, our module enables you to leverage APIs. The Improved Import & Export extension provides the ability to establish connections via REST, SOAP, and GraphQL to move information directly. Note that an external system should support one of these connection types.

It is also necessary to mention that all features and instruments described in this post can be applied to data file transfers and connections established directly or with the help of third-party services.

Watch this video to see the extension in action:

Final Words

There are two ways of importing data from Informix to Magento 2. Which one would you like to choose? Do you want to do everything manually or think about the automated procedure? Choose the improved approach, and you will forever forget about headaches caused by data transfers and integrations. If you still have questions regarding the Improved Import & Export extension and its role in the connection of Magento 2 and Informix, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension