How to import data from SAP ASE (Sybase) database to Magento 2

What if a vital data update is stored within the SAP Adaptive Server database but you need to transfer it to your Magento 2 website? The default tools built into your e-commerce store are useless if you need to create a direct connection. The same is about most third-party tools. So, how to move the crucial information to your e-commerce store most efficiently? Below, you will find the answer. We describe a common way of importing SAP ASE bases into Magento 2 and then compare it with a more user-friendly approach.

The common way of moving SAP ASE tables into Magento 2 is a complex procedure that consists of several steps. First of all, you need to perform a CSV export from the Sybase SQL server. We describe this process in the dedicated step-by-step guide below. Next, you need to map third-party attributes to Magento 2 records. After that, it is possible to import an SAP ASE table into Magento 2. You will find the pros and cons of this approach below.

As for the easy way of moving Sybase SQL server bases into Magento 2, it is way more straightforward since it involves Improved Import & Export. You provide the connection parameters and configure other aspects of your data transfer. That’s it! Let’s explore each step of both procedures in more detail and compare two approaches side by side.

‘

The Common Way of Moving SAP Adaptive Server Bases into Magento 2

There is a community-proven way of moving any data from a remote database to Magento which consists of three steps. First of all, you need to create a CSV output with the desired information. Usually, this step requires either coding skills or third-party utilities. And you are bound to the CSV format due to the Magento limitations.

Next, when an output file is available, you should solve a bunch of problems by editing the document. If there are no column names, you have to add them manually. If all the necessary information is available, it is still required to edit your CSV file to make it suitable for Magento requirements.

Only when all fixes are applied, it is possible to launch the import process updating your Magento 2 database. To be more specific, we describe each step individually.

CSV Export from SAP ASE (Sybase SQL Server) Database

As we’ve mentioned above, you need coding skills to create a CSV file out of the SAP ASE database. Use the OUTPUT statement to export all the desired data.

The OUTPUT statement allows writing out the result set of a SELECT statement in several different file formats. Thus, it is necessary to specify a file type on each OUTPUT statement. Note that Interactive SQL can execute a command file with multiple OUTPUT statements. The default output format is defined on the Import/Export tab of the Interactive SQL Options window.

The Interactive SQL OUTPUT statement provides the ability to export a table or its parts in a specified format and automate the export process via a command file. But how to export a CSV file out of Sybase?

Connect to the SQL Anywhere database in Interactive SQL and execute an OUTPUT statement with the clauses FORMAT TEXT, QUOTE ‘”‘, and WITH COLUMN NAMES. Use the following code snippet:

It will create a comma-delimited format with the column names in the first line of the file. Note that string values will be enclosed with quotation marks.

Map Third-Party Attributes to Magento 2 Attributes

Now, when you have a CSV file with the desired data and column names, it is necessary to review its attributes. Since column names are already available, you have less headache, but it is still unsuitable for Magento 2.

The problem of different attribute standards makes two different systems incompatible. It means that you cannot transfer data between them directly without editing it. The Magento 2 SAP ASE integration is not an exception so that you have to review all provided attributes and modify them if necessary.

The bigger your CSV file is, the more work should be done especially if you also want to edit attribute values. Note that incompatible designations won’t be transferred. As a result, you will import the desired update only partly.

Import SAP ASE Table into Magento 2

As for the import process itself, it also has several problems. As mentioned above, the incorrect attributes editing prevents you from transferring all the data. Besides, you cannot automate the procedure. It is necessary to launch import every time an update is provided. Furthermore, you have to run the export procedure and edit CSV files as well. Luckily, there is a more user-friendly way of importing data from SAP ASE (Sybase SQL Server) to Magento 2 but let’s take a look at the pros and cons of the default approach first.

Pros & Cons

As you might have already guessed, the standard way has lots of drawbacks, but what are its positive aspects?

Pros

1. You can transfer data from SAP ASE to your Magento 2 website.

Cons

1. CSV export is complicated and requires coding skills.
2. You should map attributes manually.
3. It is necessary to repeat the whole procedure every time an update is required.

Doesn’t look very promising, but what do we offer instead?

The Easy Way of Moving Sybase SQL Server Bases into Magento 2

Exhausted by the inconveniences mentioned above, we’ve decided to create our solution that automates the whole process of data transferring between Magento 2 and SAP ASE (Sybase). Meet the Improved Import & Export Magento 2 extension – a module that completely revamps the procedure described above. So, what are the critical differences between the two approaches?

SAP Adaptive Server Database Connection

The first significant benefit you get with the Improved Import & Export extension is the ability to connect Magento 2 to an SAP Adaptive Server database directly. You no longer need to run any commands to create a CSV file. Furthermore, there is no need to edit data files since you literally don’t have them!

Magento 2 SAP Adaptive Server Data Import

The fact that you no longer have CSV file in transfers doesn’t solve the problem of different attribute standards, and the Improved Import & Export Magento 2 extension offers several ways to solve it. You can either apply mapping presets – all attributes will be matched automatically – or specify external designations and set the corresponding ones used internally manually in an intuitive interface. Furthermore, there is even an opportunity to match attribute values in a user-friendly manner. Below, we describe all these and other features in more detail.

Besides, you can fully automate data transfers from SAP ASE to Magento 2. The Improved Import & Export extension provides the ability to create schedules or specify triggers. The corresponding section of this article sheds light on both features.

How to Import Data From SAP ASE to Magento 2

Now, we’d like to draw your attention to the exact import procedure that takes place when you want to transfer data from a remote SAP ASE database to Magento 2 with the help of the Improved Import & Export Magento 2 extension.

1. Under Magento 2 backend -> extension admin -> import section, create a new import profile.
2. Configure the job by adding a schedule (or choosing a trigger), selecting an imported entity, matching attributes, etc.
3. Save the job and launch the update.

Now, the Improved Import & Export extension will transfer data from SAP ASE (Sybase SQL Server) to Magento 2 running all steps described above automatically – you should only configure the profile. What are the pros and cons of this approach?

Pros & Cons

While the only positive aspect of the traditional approach to data transfers is the ability to move data from SAP ASE to Magento 2, Improved Import & Export offers many more benefits? At the same time, the situation with drawbacks is the opposite. Let’s see how our extension changes the default order.

Pros

1. No file export is required.
2. No data files should be edited.
3. No coding skills are necessary.
4. The whole import process is automated.
5. Magento 2 and Sybase are connected directly.

Cons

1. Installing a third-party extension is necessary.

Is it a real drawback? If you have any difficulties with the installation process and its further configuration, our specialists will help you, so it is not a problem at all. Thus, the Improved Import & Export extension offers an entirely revamped process of transferring data from Sybase to Magento 2.

Now, we want to list some core SAP Adaptive Server features and then proceed to the more in-depth exploration of our module.

Get Improved Import & Export Magento 2 Extension

SAP Adaptive Server Facts

SAP Adaptive Server is often compared with Microsoft SQL Server, SAP IQ, and Oracle. Being an enterprise-class RDBMS, it is a relational DBMS. The second SAP ASE model is a key-value store. The database developer is SAP. Its initial release is dated back to 1987, and the current version is 16.0. Other features are:

• SAP ASE operates under a commercial license;
• It has no cloud-based functionality;
• Implementation languages of the database are C and C++;
• Its server operating systems include AIX, HP-UX, Linux, Solaris, Unix, and  Windows;
• There is no XML support, but data scheme, typing, secondary indexes, and SQL are a part of the database;
• APIs and other access methods include OLE DB, Tabular Data Stream (TDS), ADO.NET, JDBC, and ODBC;
• As for the supported programming languages, they are C, C++, Cobo, Java, Perl, PHP, and Python;
• Server-side scripts include Java and Transact-SQL;
• Triggers are fully supported;
• Partitioning methods are represented by horizontal partitioning;
• Replication methods are represented by master-master and master-slave replication;
• MapReduce is not available;
• Consistency concepts include Immediate Consistency;
• Foreign keys are supported;
• Transaction concepts are represented by ACID;
• Concurrency and durability are among available features;
• User concepts are represented by fine-grained access rights according to SQL-standard.

Improved Import & Export Magento 2 Extension

Now, when you know that Improved Import & Export offers a way more user-friendly approach to data transfers from SAP ASE to Magento 2, we’d like to draw your attention to multiple details of each improvement. Let’s start with the automation.

Automated Import & Export Processes

As it was mentioned above, the Improved Import & Export Magento 2 extension offers two ways to automate import and export processes: cron schedules and event-based triggers.

Cron Schedule

Due to the full cron support, you can create any custom update schedule. It is only necessary to know the default cron syntax. If it seems a little bit complicated, select one of the predefined intervals, which are fully customizable.

At the same time, you can create an import or export job that has no schedule. It is suitable for one-time migrations and updates that have a random frequency.

If necessary, a scheduled job can be launched manually. Thus, you will avoid situations when the vital update is delayed since the corresponding import procedure is planned for another day.

The schedule configuration looks as follows:

Events

Besides, the Improved Import & Export Magento 2 extension lets you automate import and export processes on the basis of events and observers. You can specify triggers and create conditions to launch import or export processes in a fully automated manner. For further information, follow this link: How to Run Magento 2 Import or Export After Specific System Event or Process.

Now, when you know how to automate data import from SAP ASE to Magento 2, we can proceed to the next block of the article dedicated to mapping features.

Advanced Mapping Features

We’ve already described the problem caused by different attribute standards, and now it is time to shed light on the features of the Improved Import & Export extension designed to solve the issues.

Mapping Presets

First of all, you can apply mapping presets. It is the easiest way of improving your daily routine related to attributes matching. The Firebear team has created multiple mapping schemes that match third-party attributes to ones used internally within a few clicks.

Apply a prest and the Improved Import & Export Magento 2 extension will analyze the input file, discover third-party attributes, and match them to ones used internally. Your role is reduced to clicking a few buttons. It is a way more user-friendly approach to attributes matching than one described at the beginning of this article, isn’t it?

Below, you can see how to map Magento 1 attributes to their 2.x analogs solving the problem of different standards:

Note that it is always possible to do the same procedure manually within a convenient interface if specific presets are not available.

Matching Interface

The Improved Import & Export Magento 2 extension provides the easiest way to match attributes manually. The module offers a user-friendly mapping interface where you can do all the works within a few seconds. Select all third-party attributes in one column and specify the corresponding designations in front of them as shown below:

And don’t forget that you can set hardcoded values. If you are not familiar with the feature, it lets you specify attribute values that are added automatically to all items in the table for the corresponding attribute.

Attribute Values Mapping

As we’ve already mentioned, you can match not only attributes but their values. The Improved Import & Export Magento 2 extension incorporates the same principle as the one described above. Select an external attribute, specify its values, and link them to ones used internally. You can freely use this feature during the Magento 2 SAP ASE import.

Let’s assume that there is an external attribute “color” with two values (“lime-green” and “avocado-green”) in a remote SAP ASE table. But your Magento 2 database includes only one corresponding value(“green”). Match both external designations to the one used internally in the extension interface instead of importing unnecessary data. You can find more information regarding attribute values mapping here: Attribute Values Mapping.

Attribute Values Editing

In addition to the procedure above, the Improved Import & Export Magento 2 extension also offers the ability to edit multiple attribute values in bulk. The module supports numerous commands and lets you combine them to increase the efficiency of editing. To apply changes more precisely, You can create specific conditions.

The following commands are at your disposal:

• Add prefix, suffix or both – add a prefix, suffix or both to multiple attribute values in bulk;
• Split data – split data related to different values;
• Merge data – merge data related to the same value.

You can find more information here:  How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

Another mapping feature offered by the Improved Import & Export Magento 2 extension is related to categories. You can seamlessly integrate products from another catalog into yours by mapping categories. Select a third-party category and match it to the one used internally as illustrated below:

Also, note that it is possible to create new categories during the import process. You only need to specify a parent category and a name for a new one. Both features are described in this article: Category Mapping.

Attributes On The Fly

One more feature that simplifies your routine related to the data import is left. It is the ability to create attributes on the fly. If an imported table is missing any data, add parameters necessary to generate it during the import. The Improved Import & Export Magento 2 extension will add all the necessary attributes automatically. The following general format should be used to achieve this goal:

Attribute|attribute_property_name:attribute_property_value|…

You can discover the full algorithm in this article: Product attributes import.

Extended Connectivity Options

While the Magento 2 SAP ASE integration is based on the direct connection, there are several more ways of integration with external platforms. You can either use data files, online sheets or connect to external systems directly.

Multiple File Standards

The Improved Import & Export extension dramatically extends the default list of supported file formats. While Magento works with CSV only, our module adds  XML, JSON, ODS, and Excel into the game. Since you can freely import and export data using the specified file types, there is no need to convert everything to CSV and vice versa.

Multiple File Sources

When it comes to data file transfers, you need a point of connection where two systems can exchange them. The Improved Import & Export Magento 2 extension offers three different options.

• FTP/SFTP: your local server can become the point of connection; a remote one can be utilized for the same purpose;
• Dropbox: create a new account or use an existing one to transfer data files between Magento 2 and an external system;
• URL: use it if you need to establish a one-way connection; specify a file address, and the extension will import files automatically.

It is also necessary to mention that compressed data is no longer a problem. The Improved Import & Export Magento 2 extension can unzip/untar it automatically.  

Alternative Ways of Import & Export

Additionally to file transfers, our module provides the ability to establish the connection between your e-commerce store and an external system without data files. To achieve this goal, you can leverage one of the two different approaches. First of all, it is possible to rely on intermediaries, such as Google Sheets, Office 365 Excel, or Zoho Sheet. The Google Sheets Magento 2 integration looks as follows:

The second way is based on API connections. The extension supports REST, SOAP, and GraphQL. Note that you can freely apply all the features described above to any of them. Furthermore, integrations via intermediaries also support all extension features.

Watch the video below to see the extension in action:

[embedded content]

Final Words

As you can see, there is a much more user-friendly way of importing data from SAP ASE to Magento 2 than one used by the community. Automate all processes without any coding skills in a few clicks. You no longer need to spend hours of time editing CSV files or converting an external output into this standard. The Improved Import & Export Magento 2 extension will do all the work for you. For further information about the module, follow the link below and contact our support team:

Get Improved Import & Export Magento 2 Extension

Magento 2 SAP ASE Database Integration FAQ

Let’s take a look at other nuances of importing and exporting data between Magento 2 and SAP ASE database with the help of the Improved Import & Export Magento 2 extension.

‘