How to import data from Firebird to Magento 2

- E-Commerce, Magento 2, Our extensions

Magento 2 Firebird Data Import

Below, we discuss two ways of importing data from a remote Firebird database to Magento 2. First, this article sheds light on a common approach that can be applied to any database. Next, we offer a more user-friendly way of moving data from Firebird to Magento 2. To make the long story short, let’s see how to move tables from a remote database.

The Common Way of Moving Firebird Tables into Magento 2

The common way of moving data from Firebird to Magento 2 is based on three pillars. First of all, you need to export a table from the database into CSV. Next, you should edit the output making it suitable for the Magento 2 requirements. Then, you can import the CSV file into Magento 2. Since each step is a little bit complicated, let’s discuss them individually.

CSV Export from Firebird Database

To export a table from Firebird to CSV, you need an additional tool to be installed. There are many possible solutions developed for completing this goal. For instance, you can choose FBExport – a solution for exporting and importing data with Firebird and InterBase.

It is designed to export/import data from/to Firebird table into a text file. It supports the XML format, but since the size of exported file matters, there is own format which uses less space. And FBExport can also create a CSV output as INSERT statements.

Being a command-line tool, FBExport can replace batch scripts. Linux and Windows versions are available. There is also a GUI version available for FBE 1.40-1.60. The latest version is 1.67 and includes the following features:

  • -B option – to set a different separator instead of comma for CSV export;
  • Fixed hang with INSERT statement export when a field starts with a single-quote;
  • Fixed hang with CSV export when a field begins with a quote;
  • Asterisk character is no longer removed when loading statements from a file;
  • NUMERIC and DECIMAL columns don’t suffer from conversions to doubles anymore

Map Third-Party Attributes to Magento 2 Attributes

Now, when the CSV output is generated, you should edit the data file. Since different external systems use own attributes, it is impossible to establish a direct data flow without any modifications of information. Even Magento 2 and Magento 1 use different standards, so you should edit data files when migrating from 1.x to 2.x.

What should be done when you move an external table from Firebird to Magento 2? First of all, review and edit all attributes. Thus, you will make the CSV file suitable for the import procedure. At the same time, you can edit attribute values, since the external designations may differ a lot from the ones you already specified in your database.
Note that both processes are very time-consuming and require some extra attention. If any attribute is edited incorrectly, the system won’t import it leading to an error.  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.

Magento 2 Product Import Export Tutorial

Import Firebird Table into Magento 2

Now, when everything is edited, you can go you your Magento admin, open its import tools, and start the update. Note that this process cannot be automated. You have to repeat all the three steps every time an update is required, but let’s take a look at a full list of pros and cons.

Pros & Cons

The pros and cons of the traditional approach to data import from Firebird to Magento 2 are quite obvious since we are talking about a traditional algorithm of data migration.

Pros

  1. Magento 2 Firebird data import.

You establish the connection between the two systems. It is the only positive aspect of this approach. The ability to complete the import process is what led you here. Unfortunately, the default procedure is far from being user-friendly, and there are many drawbacks.

Cons

  1. Complicated data export via third-party tools;
  2. Time-consuming data editing (attributes + their values);
  3. Limited import capabilities (only the default Magento 2 functionality);
  4. Lack of automation (all the three steps should be re-applied from the very beginning).

In our time, these reasons were enough to look for a more user-friendly way of importing data from Firebird to Magento 2, and below we’ve prepared one.

The Easy Way of Moving Firebird Bases into Magento 2

Magento 2 mysql data import

Faced towards the inability to move data from external bases to Magento 2 automatically, we’ve decided to create our own tool that significantly improves this process. Therefore, meet the Improved Import & Export Magento 2 extension – a number one time saver when it comes to data migrations to and from your e-commerce store. Below, we shed light on the critical differences incorporated in the renewed approach.

Firebird Database Connection

The first significant benefit over the default data import from Firebird to Magento 2 is the lack of data files. With the Improved Import & Export Magento 2 extension you connect to the external database directly. Thus, the first two steps of the default approach are eliminated.

Magento 2 Firebird Data Import

Another advantage is the ability to automate all data transfers. There is no need to repeat all processes every time new information is provided. You can either create a schedule of automated imports or specify triggers that will launch the update automatically helping you to save tons of time. Both features are described below.

And since the direct connection doesn’t solve the problem of different attribute standards, we propose two entirely different ways to solve it. Firstly, you can apply mapping presets matching the external attributes to the corresponding ones used internally within just a few clicks. Secondly, it is possible to match everything manually right in your Magento admin in a user-friendly manner. And you can match attribute values as well as well! We describe all of these opportunities below.

How to import data from Firebird to Magento 2

The data import process from Firebird to Magento 2 with the help of Improved Import & Export is as simple as the following few steps:

  1. In the Magento admin, find the Improved Import & Export extension, open its import section, and create a new import job (profile) there.
  2. Specify parameters required for the connection to the Firebird database, map attributes, create a schedule (or set triggers), and configure other settings if needed;
  3. Save and launch the profile.

That’s it! You must admit that the Improved Import & Export extension dramatically decrease the time and effort necessary to connect your e-commerce store to Firebird. Since the process is fully automated, you also prevent further time expenditures!

Pros & Cons

Let’s see what pros and cons the Improved Import & Export offers for the Magento 2 Firebird integration:

Pros

  1. User-friendly Magento 2 Firebird integration without headaches;
  2. The direct connection between the two systems;
  3. No file export/import processes are involved;
  4. No file editing is necessary;
  5. Fast attributes matching instead of a tedious and time-consuming procedure;
  6. Fully automated data transfers.

Cons

So what are the cons? You have to install a third-party module. But we have a friendly support that entirely fixes that problem. Contact us, and the Firebear team will help you solve any difficulties related to the installation. We can even discuss your particular changes to the module’s functionality and propose their implementation!

Below, we describe the Improved Import & Export Magento 2 extension in more detail, but let’s take a look at the Firebird’s features first.

Get Improved Import & Export Magento 2 Extension

Firebird Facts

Magento 2 Firebird Data Import

Being a fork of Borland’s InterBase, Firebird is an open source relational DBMS and key-value store. It is often compared with MySQL, PostgreSQL and Microsoft SQL Server. Developed by Firebird Foundation, the database was initially released in 2000. Its current release is 3.0.3, dated back to February 2018. Other features are listed below:

  • The license model is open source (initial developer’s public license);
  • Firebird is not available as a cloud-based service;
  • Implementation languages are C and C++;
  • Server operating systems include:
    • AIX
    • FreeBSD
    • HP-UX
    • Linux
    • OS X
    • Solaris
    • Unix
    • Windows
  • Server-less functionality is represented by Firebird Embedded Server;
  • Data scheme, typing, secondary indexes, and SQL are supported;
  • APIs and other access methods include:
    • C/C++ API
    • OLE DB
    • ADO.NET
    • JDBC info
    • ODBC
  • Supported programming languages are:
    • C
    • C#
    • C++
    • Delphi
    • Java
    • JavaScript
    • Lua
    • Perl
    • PHP
    • Python
    • Ruby
  • Server-side scripts are represented by PSQL;
  • Triggers are available;
  • No partitioning and replication methods;
  • MapReduce is not available;
  • Foreign keys are supported;
  • Transaction concepts are represented by ACID;
  • Concurrency and durability features are available;
  • Fine-grained authorization concept is implemented.

Improved Import & Export Magento 2 Extension

Magento 2 oracle import

Now, we’d like to draw your attention to some aspects of the Improved Import & Export Magento 2 extension that enhance the integration with Firebird. Let’s take a look at automation practices.

Automated Import & Export Processes

As mentioned above, our module provides the ability to automate data transfers between Magento 2 and Firebird with the help of schedules and events. You can use one of these approaches or combine them to boost the efficiency.

Schedules

The Improved Import & Export extension supports cron and provides the ability to generate any custom schedule using this technology. It is only necessary to be familiar with its standard syntax. At the same time, you can select one of the predefined intervals which are fully customizable.

Besides, Improved Import & Export offers an opportunity to create profiles that have no schedule. They are useful for one time migrations (for instance, from an external e-commerce system to Magento 2) or asynchronous updates when schedules cannot be effective.

And don’t forget that every automated profile can be launched manually whenever you want. Thus, no update is missing if there is a schedule, but your partner delayed the data. There is no need to wait until the next planned import.

Below, you can see how to configure the schedule section of the Improved Import & Export Magento 2 extension:

Events

At the same time, events also improve your daily routine dramatically. Specify a system process or use a custom one to launch data updates automatically. For further information about this feature, follow this link: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

Another part of Improved Import & Export that enables the automation of data updates is its mapping interface that offers multiple features.  

Mapping Presets

The first one is represented by presets. It is the easiest way to solve the conflict between different attribute standards. You have to select and apply the presets suitable for your integration. Next, the Improved Import & Export will analyze the input data editing attributes automatically. You have to click a few buttons only.

Below, we illustrate the functionality of mapping presets with the example of the migration from Magento 1 to Magento 2:

Matching Interface

At the same time, it is possible to apply all the necessary changes manually. Select a third-party attribute and specify the one used internally in front of it, just like shown below:

You can also add a hardcoded value that will be applied to all items that include this attribute.

Attribute Values Mapping

The same logic is applied to attribute values. You can effortlessly map them instead of editing every product individually via the admin section. Thus, the Improved Import & Export Magento 2 extension will help you save tons of time and effort. For further information about the feature, follow this link: Attribute Values Mapping.

Attribute Values Editing

Besides, our module lets you edit attribute values. If matching doesn’t solve your problems, select one of the multiple rules to modify external values in bulk. You can either add a prefix or alter third-party designations with a suffix. If data related to different values are merged, the Improved Import & Export module provides the ability to split it. At the same time, you can combine data available separately. The following article explains the feature in more detail: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

Another important time-saver is category mapping. If you import product data from an external catalog, the chances are that categories differ from ones used in your database. Thus, the Improved Import & Export Magento 2 extension offers two ways to solve this problem. First of all, you can map categories during the import process. Select an external designation and match it to the corresponding one used internally like displayed below:

At the same time, you can create new categories on the fly. Specify a parent one and type a new name. That’s it! A new category is added so that you can match an external one to it. For further information, read this article: Category Mapping.

Attributes On The Fly

In case some data is missing, you can create attributes on the fly. The Improved Import & Export Magento 2 module enables you to specify parameters necessary to do that via the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full algorithm is described here: Product attributes import.

Extended Connectivity Options

Although the Magento 2 Firebird integration is based on the direct connection, we should also say a few words about the extended connectivity options of our module.

Multiple File Standards

First of all, the Improved Import & Export Magento 2 extension essentially increaseі the number of supported file formats. While the Magento platform works with CSV only, our tool adds support for XML, JSON, ODS, and Excel. You can freely import and export data in these formats.

Multiple File Sources

When it comes to file transfers, it is necessary to mention that the Improved Import & Export Magento 2 module supports numerous sources. You can entirely rely on the FTP/SFTP connection using a local or remote server as a point of transfers. The same functionality can be enabled for a Dropbox account.

When it comes to import processes only, the Improved Import & Export module provides the ability to import files via a direct URL. Note that all compressed data is unzipped automatically.

Alternative Ways of Import & Export

As for alternative ways of data transfers, the Improved Import & Export extension offers two different approaches. Firstly, you can use various online sheets such as Google Sheets, Office 365 Excel, or Zoho Sheet. The Google Sheets integration looks as follows:

Magento 2 Google Sheet import

Secondly, you can establish a direct API connection with the help of our module. Use REST, SOAP, and GraphQL integrations to synchronize the Magento 2 store with any external systems.

Watch the following video to see the extension in action:

 

Final Words

You must admit that the Improved Import & Export extension dramatically improved the Magento 2 Firebird integration. If you want to import external tables to your database automatically, our module is the only tool that can help you achieve this goal. For further information, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension