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.
Table of contents
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:
SELECT * FROM SalesOrders;
OUTPUT TO ‘c:\test\sales.csv’
WITH COLUMN NAMES;
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?
- You can transfer data from SAP ASE to your Magento 2 website.
- CSV export is complicated and requires coding skills.
- You should map attributes manually.
- 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.
- Under Magento 2 backend -> extension admin -> import section, create a new import profile.
- Configure the job by adding a schedule (or choosing a trigger), selecting an imported entity, matching attributes, etc.
- 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.
- No file export is required.
- No data files should be edited.
- No coding skills are necessary.
- The whole import process is automated.
- Magento 2 and Sybase are connected directly.
- 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.
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:
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.
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.
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.
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:
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:
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.
How to import and export data between Magento 2 and SAP ASE automatically?
If you need to import or export data between Magento 2 and SAP ASE on a regular basis, you can achieve this goal with the Improved Import & Export extension. The module leverages cron to let you create a schedule of updates, importing and exporting tables for you at a specified time.
How to import data with third-party attributes to Magento 2 from SAP ASE?
While Magento 2 blocks the import of third-party data that doesn’t follow its requirements, the Improved Import & Export extension lets you create a mapping scheme matching third-party attributes and their values to ones used within the system. Consequently, you can easily adapt data stored in SAP ASE to the native standards of your e-commerce website. Note that you can also apply mapping to export processes to generate the output your partners need.
How to import and export data between Magento 2 and SAP ASE via API?
The Improved Import & Export Magento 2 extension provides the ability to create API connections to let you import data between Magento 2 and SAP ASE eliminating standard data file transfers from this process. You can find the corresponding option in every import/export job. Specify the parameters necessary to sync the two systems and freely move data in a bi-directional API connection. Note that the plugin also lets you leverage its mapping functionality for API connections.
How to import and export data between Magento 2 and external systems via ODS files?
You can leverage our extension to move data via different file formats. If the update is stored in an ODS file, you no longer need to convert it to CSV to fit the Magento 2 requirements. With the Improved Import & Export Magento 2 module, it is possible to import ODS files to your ecommerce website without any necessity to run the conversion. You can also export data using this file format.
How to import and export data between Magento 2 and external systems via XLSX files?
You can leverage our extension to move data via different file formats. If the update is stored in an XLSX file, you no longer need to convert it to CSV to fit the Magento 2 requirements. With the Improved Import & Export Magento 2 module, it is possible to import XLSX files to your ecommerce website without any necessity to run the conversion. You can also export data using this file format.
How to import and export data between Magento 2 and external systems via JSON files?
You can leverage our extension to move data via different file formats. If the update is stored in a JSON file, you no longer need to convert it to CSV to fit the Magento 2 requirements. With the Improved Import & Export Magento 2 module, it is possible to import JSON files to your ecommerce website without any necessity to run the conversion. You can also export data using this file format.
How to import compressed data between Magento 2 and external systems?
If data files are stored in a compressed file, you can also use the Improved Import & Export extension to transfer them to Magento 2. Note that your file archive should be in a ZIP or TAR format, containing CSV, XML, ODS, XLSX, or JSON files.
How to import data to Magento 2 from Google Sheets?
If your data is stored beyond SAP ASE, the Improved Import & Export extension will also help you transfer it to your Magento 2 database. Thus, it supports Google Sheets, letting you import the update to your e-commerce website without any need to create an export file and then transfer it to Magento 2. The Improved Import & Export lets you move the corresponding data straight from Google Sheets to your Magento 2 database. It is only necessary to specify the address of the table you want to transfer.
How to import and export data between Magento 2 and Dropbox?
In addition to Google Sheets, the Improved Import & Export module supports other file sources, including Dropbox. This time, a bi-directional exchange is possible. You only need to save data files in one of these formats: CSV, XML, ODS, XLSX, or JSON. It is possible to use ZIP or TAR compression if necessary. If these conditions are followed, you can use Dropbox as a file transfer point.
How to import and export data to Magento 2 from Google Drive?
In addition to Dropbox, you can use Google Drive in the exact same way. The Improved Import & Export extension lets you transfer data between Magento 2 and the popular file storage if it is stored in CSV, XML, ODS, XLSX, or JSON formats and compressed as ZIP or TAR files.