How to Import Data from Teradata to Magento 2

In the following article, we discuss the improved approach to data import from Teradata to Magento 2. Although there is a community-accepted approach (described below as well), we propose a more user-friendly way of integration. Being based on a third-party extension, it not only moves all routine processes to a single environment but also allows automating them. Consequently, you no longer need to spend time on importing data from Teradata to Magento 2 daily. But let’s describe the common approach before discussing the enhanced one.

Traditional Approach to Data Import from Teradata to Magento 2

The traditional approach to data import from Teradata to Magento 2 requires three core things to be done. First of all, you need to create an output file that contains the information necessary for the update. If you use the default version of Magento, it is required to create a CSV file with the corresponding data since the platform doesn’t support other formats. If you create a file of another standard, don’t forget to convert it to the accepted one.

Next, when the output is generated, you should edit your data file. It is necessary because different systems use own attributes so that it is impossible to connect them directly. You always should match third-party attributes to ones used within Magento.

The third stage of the traditional data import from Teradata to Magento 2 requires importing the output file into your e-commerce website. The procedure looks relatively simple unless you face problems that occur during the second stage: if attributes are unmatched or matched incorrectly, you should fix that. Otherwise, no import will be possible. Besides, you cannot automate data migrations, so get ready to repeat all these steps when a new update is required.

How to Export Data from Teradata to Local File

We’ve just mentioned that it is necessary to export data in a CSV file, but what the only possible option is TXT? Let’s take a look at a primary example of how to export data from Teradata to a local TXT file. According to one of the multiple tutorials, you can export the output of a Teradata SQL command to a local file with the help of the EXPORT Bteq command. Use the following syntax to achieve this goal:

Below, you can see the example of a simple output command:

The snippet above writes the SELECT statement output into the following file: C:\TX\out.txt. Note that each column content will be separated by ‘|’ since it is specified in the third row.

If you cannot create a CSV file instead, it is necessary to convert your TXT output to the supported format. To make the long story short, we won’t describe this process since our focus is the improved procedure of data import from Teradata to Magento 2. Let’s proceed to the second stage of the traditional approach.

How to Match Teradata Attributes to Magento 2 Attributes

Now, when you have a CSV file with the necessary information, you should make it suitable for the Magento standards. Note that all attributes should be reviewed and edited if necessary. Thus, you will be able to proceed to the last stage of importing data from Teradata to Magento 2. We recommend you to use the following sample files to edit the Teradata output: Magento 2 Import & Export Sample Files.

What if the import file remains in its initial state? You won’t import it to your e-commerce store, and no update will take place. However, attributes editing is a very dull and time-consuming procedure that cannot be eliminated, but you can substantially improve it. The tool described below not only makes this task user-friendly but also provides an opportunity to reduce it to a one-time configuration which consists of applying a necessary preset. Looks very promising, isn’t it? But this is a part of the enhanced approach to Magento 2 data import from Teradata. Before describing it, we have to draw your attention to the normal import process.

How to Import Data from Teradata to Magento 2 via CSV

Now, you need to take the CSV output and transfer it into Magento 2. It is possible to use the standard Magento 2 tools to complete the procedure, but there is one crucial nuance. If the output wasn’t edited correctly, you won’t either import all the information or, in worst case scenario, move anything into your database. In case of errors, it is necessary to return to the second stage of the traditional approach to importing data from Teradata to Magento 2. When all errors are fixed, you should start the import process once again. It is fully described here: The Complete Guide to Magento 2 Product Import / Export.

User-Friendly Approach to Data Import from Teradata to Magento 2

The previous algorithm doesn’t look intuitive and easy, does it? Even if you consider it so, there is a much more user-friendly way to complete the same goal. As mentioned above, you need to install a third-party tool, so meet the Improved Import & Export Magento 2 extension.

With the help of the module, you will reduce multiple environments described above to a single admin interface of the plugin. The extension connects to Teradata directly so that you don’t need to create an output file. Thus, the inability to generate a CSV file smoothly is no longer a problem – you just don’t need it.

The stage of attribute mapping is also dramatically improved – you do everything in a convenient interface with the help of presets: select a mapping scheme suitable for your business needs and apply it within just a few clicks. The difference between this approach and its traditional equivalent is exceptionally vast: instead of spending hours of time on manual data editing, you need to press a few buttons. Even if there is no a preset that satisfies your needs; you can still apply the changes manually within the same admin section of the Improved Import & Export module. The procedure is always simple and lightning fast.

And the most critical aspect of attributes editing with the help of our extension is the ability to apply all changes to further update files automatically. Yes, it means that you don’t need to recreate a mapping scheme every time you run an update. The same is about the import procedure itself: the plugin will launch it automatically when necessary.

Direct Teradata Connection

As we’ve just mentioned the Improved Import & Export Magento 2 extension allows you to establish a direct connection between your e-commerce store and Teradata so that file transfers are eliminated from updates. The information is transferred from one system to another without any third-parties (we consider the extension a part of your website).

Thus, you don’t need to figure out how to export files from the database. And it is no longer necessary to convert them to CSV. Besides, manual data editing is eliminated; instead, you can leverage the following procedure.

Data Mapping for Magento 2 Teradata Integration

As mentioned above, data mapping for the Magento 2 Teradata integration can be performed in two ways. First of all, you can apply the presets. Secondly, you can still match external attributes to once used in Magento manually, but it is not like manual data file editing. With the Improved Import & Export Magento 2 extension, you don’t need to deal with a massive amount of data stored in a file. The module lets you select attributes that are not suitable for the requirements of Magento 2 and map them to the corresponding attributes of the platform. That’s all!

Direct Data Import from Teradata to Magento 2

Let’s take a look at the core stages of the improved data import from Teradata to Magento 2 and compare them with the traditional approach

1. First of all, in the Magento 2 admin, you need to find the Improved Import & Export extension and open its import interface.
2. There, configure a new import profile specifying various parameters: schedule, connection type, import behavior, data source, etc.
3. Don’t forget to select a database and a table you want to import – it is our primary goal!
4. Now, you should map external attributes to ones used internally. Apply a preset or create a new scheme.
5. You can save the profile now launching the import process.  

Congratulations! You’ve just connected your Magento 2 store to the Teradata database. Now, our module will effortlessly move data from one system to another automatically. Only five little steps are necessary to establish the connection. What about the traditional approach?

As you can see, it requires many more actions to be performed and as a result time to be spent. The first thing about it is the inability to automate everything. You need to repeat multiple stages that take place in different environments every time the update is required. The Improved Import & Export Magento 2 extension eliminates this necessity. Let’s say a few more words about Teradata and then return to core features of our module.

Get Improved Import & Export Magento 2 Extension

Teradata Features

Teradata is often compared with Oracle, Microsoft SQL Server, and Netezza. Being a relational DBMS, it is mainly used for data warehousing. The secondary database model of Teradata is the document store. At the same time, it is also a key-value store. Also, note that it is one of the oldest projects of its kind: the initial release of Teradata is dated from 1984. These are some other features of the database:

• Teradata operates under a commercial license.
• It provides no cloud-based options.
• The server operating system of the project is Linux.
• Teradata supports such features as data scheme, typing, XML, secondary indexes, and SQL.
• The system’s APIs and other access methods include .NET Client API, HTTP REST, JDBC, ODBC, JMS Adapter, and OLE DB.
• Supported programming languages are C, C++, Cobol, Java, PL/1, Python, and R.
• Teradata supports server-side scripts and triggers.
• Partitioning methods include sharding while replication methods are characterized by master-master replication.
• MapReduce is not available.
• Consistency concepts are represented with Immediate Consistency.
• Foreign keys are supported.
• Transaction concepts are represented with ACID.
• Besides, Teradata offers concurrency,  durability, and in-memory capabilities.
• User concepts are represented by fine-grained access rights according to SQL-standard.

Improved Import & Export Magento 2 Extension

As promised above, we return to the critical features of the Improved Import & Export Magento 2 extension. Below, we describe its rich scheduling options, extended connectivity opportunities, and all possible mapping enhancements.

Flexible Schedules

Since the Improved Import & Export Magento 2 extension supports cron, you can create any custom schedule of updates. The module enables you to configure the necessary parameters by specifying several symbols only. At the same time, it is possible to select one of the predefined intervals. If you don’t want to deal with the cron syntax, you don’t need to do that. Flexible options of our plugin are aimed at users with all levels of technical skills. Also, note that the module allows selecting a predefined interval and customizing it according to your needs.

Besides, you can create import jobs that have no schedule. Such profiles can be launched in a manual mode only. And don’t forget that the ability to start the update manually is provided with scheduled jobs as well. The corresponding section of the extension has the following look and feel:

Extended Connectivity Options

Although the Magento 2 Teradata integration is based on the direct connection between the two systems, it is necessary to draw your attention to the extended connectivity options provided by the Improved Import & Export extension. It adds multiple file formats, data sources, and alternative ways of import/export into the game. Let’s explore each feature individually.

Multiple File Formats

It’s not a secret that the Magento platform has limited functionality when it comes to different areas and import/export processes are not an exception. One of the most dramatic drawbacks is the support for CSV files only. Unfortunately, most third-party extensions don’t extend the basic option much. The usually add XML support, but there are still many more formats used by external systems and platforms. To extend the default connectivity options, the Improved Import & Export module introduces support for XML, JSON, ODS, and Excel in addition to the default standard. Thus, you no longer need to find ways to convert files of these formats to CSV making them Magento-importable.

Numerous File Sources

Numerous file sources are another strong benefit of the Improved Import & Export Magento 2 extension. The plugin works with three core data transfer points:

• FTP/SFTP. With this option, our module allows you to transfer information using your local server. At the same time, a remote server can be leveraged.
• Dropbox. If necessary, you can freely use a Dropbox account to connect the two systems together.
• URL. Direct URL uploads are possible as well. Specify an address of a data file, and the Improved Import & Export Magento 2 extension will transfer it into the system.

It is also necessary to mention that the plugin knows what to do with compressed data. All file archives are extracted automatically, allowing you to save time on importing them directly.

Alternative Ways of Import & Export

As for the ability to eliminate files from data transfers, it is represented by Google Sheets integration. If you have data stored on this service, the Improved Import & export Magento 2 extension enables you to transfer it into your store. The process looks as follows:

At the same time, it is possible to establish a direct connection between two platforms via SOAP API, REST API, and GraphQL API. Note that all features – schedules, mapping, and other usability improvements – are fully compatible with all file standards, sources, entities, and connection types represented within the Improved Import & Export Magento 2 extension.

Advanced Mapping Functionality

The Improved Import & Export extension offers one of the most flexible mapping interfaces in the whole Magento ecosystem. At the same time, it is the most powerful tool that is aimed not only att attributes but also their values and product categories.

Attributes Mapping

The standard mapping opportunities of the Improved Import & Export Magento 2 extension includes the revamped way of attributes matching. The module dramatically simplifies mapping by introducing the ability to use presets. Developed by our specialists, they cover all needs of import and export processes. As mentioned above, you need to press only a few buttons to make an external output suitable for import. The following gif image illustrates how to apply a preset for the migration from Magento 1.x to 2.x:

Note that the same principle is used in case of other presets. As for manual attributes matching, it is also much more user-friendly than manual data file editing. The process looks as follows:

Please, contact our support regarding mapping presets for your specific business case. Leave your requests here: Firebear Help Center.

Attribute Values Mapping

In addition to attributes, you can effortlessly match their values with the Improved Import & Export Magento 2 extension. Our module offers this opportunity in its standard mapping interface. After mapping a pair of attributes, you can connect their values importing only the necessary data to your e-commerce store.

If an import file contains the “color” attribute with multiple values including “blue” and “green”, but the corresponding attribute utilized in your database utilizes “ultramarine” and “lime”, you can easily specify that in a mapping scheme importing new products with the necessary attribute values: “ultramarine” and “lime” instead of “blue” and “green”.

Attribute Values Editing

If the feature mentioned above is not enough, you can leverage attribute values editing opportunities introduced in Improved Import & Export. The extension lets you use multiple rules and create various conditions to make improvements applied to attribute values as precise as possible. You can:

• Add prefix. Set a prefix that is added to a specific attribute value.
• Add suffix. Set a suffix that is added to a specific attribute value.
• Split data. Split data into several cells if it is stored in one section.
• Merge data. Merge data stored in multiple sections into a single one.

You can edit source files using RegEx and combine these rules to solve your business goals.

Attributes On The Fly

Another important feature related to attributes is the ability to create them on the fly. The Improved Import & Export Magento 2 extension allows specifying all parameters necessary to generate a missing attribute in a corresponding field. Use the following general format to create attributes on the fly:

Category Mapping

The last feature described in this article is category mapping. When you import products into Magento 2, our module provides the ability to match external categories to ones used in your database. To achieve this goal, proceed to the corresponding section of an import profile, select an external value and specify its local analog. The process looks as follows:

If a category is not available in the database, you can create it right during the import:

Watch the video below to discover more details regarding the extension:

Conclusion

As you can see, the Improved Import & Export extension is a powerful and flexible tool that simplifies every data transfer. Let’s summarize all benefits it provides when it comes to data import from Teradata to Magento 2:

• Since data transfers are eliminated, you no longer need to:
  • Run Teradata file export;
  • Edit data files;
  • And import them.
• Magento 2 and Teradata are connected directly.
• All import processes are fully automated.
• Mapping takes place in the comfortable admin interface.

You save lots of time and effort in comparison to the traditional approach to data import from Teradata to Magento 2. Furthermore, the Improved Import & Export extension allows you to implement the same functionality with other integrations. For further information, follow the link below and contact our support.

Get Improved Import & Export Magento 2 Extension