How to Import Data from Microsoft Access to Magento 2

If you are tired of importing data from Microsoft Access to Magento 2 via the traditional algorithm that includes exporting CSV files, editing them and then transferring to a new database, you’ve come to the right place: below, we shed light on how to simplify this procedure dramatically. The following article discusses how to establish a direct data flow from Microsoft Access to Magento 2 to move data between the two systems seamlessly. But let’s say a few more words about the traditional way of how to import files from Microsoft Access to Magento 2.

Traditional Approach to Data Import from Microsoft Access to Magento 2

The traditional approach to MS Access data import to Magento 2 is divided into three steps:

1. You need to export a CSV file first;
2. Next, edit it matching its attributes to ones used in Magento 2;
3. Finally, you can import the file updating your Magento database.

Below, we describe each step in more detail.

How to Export Data from MS Access to CSV File

• In Microsoft Access, go to the External Data tab and find the Export group.
• Hit the Text File button and change it to CSV.
• Alternatively, it is possible to go right to the query (exported table), right click on it, go down to Export -> Text File.
• In a new window, change “.txt” to “.csv” in the File name field.
• You can also change a file destination by clicking the Browse button.
• Now, click Save and then OK and proceed to the Export Wizard.
• Select the Delimited option as a way of your export and hit the Next button.
• Choose Coma as a file delimiter. Alternatively, you can select Tab, Semicolon, Space or specify your own one.
• If necessary, it is possible to add field names on the first raw. Enable the corresponding option.
• Finally, you can click Next and Finish to export the desired MS Access table as a CSV file.

Now, you can find the file in the specified folder. Quite a lengthy procedure, isn’t it? Note that two more steps are left!

How to Match MS Access Attributes to Magento 2 Attributes

Unfortunately, you cannot just take the CSV table created above and move it to your Magento 2 website. Since different systems have different attributes, painful data editing is a required procedure. You cannot even move data between 1.x and 2.x versions of Magento without applying changes to it. Thus, get ready to analyze your CSV file and edit the available attributes making them suitable for Magento 2. You can find all the necessary sample files here: Magento 2 Import & Export Sample Files. Use them to see what changes are necessary to be applied. Check this article for further information about manual data file editing: The Complete Guide to Magento 2 Product Import / Export. Note that you should repeat this procedure every time you want to import a data file to Magento 2. By the way, let’s say a few words about the import process itself.

How to Import Data from Microsoft Access to Magento 2 via CSV

By default, Magento allows importing CSV files only, that’s why we described the CSV data export above. When you have the corresponding file with matched attributes, it can be moved into your store database. Just go to the Magento admin and launch the process. Note that you should start it manually right before every update. Thus, the traditional approach to moving data from MS Access to Magento 2 becomes exceptionally time-consuming. Instead, we recommend you to apply a user-friendly way of updates.

User-Friendly Approach to Data Import from Microsoft Access to Magento 2

The approach mentioned above can be replaced with a completely automated procedure. Meet the Improved Import & Export Magento 2 extension. This module provides the ability to establish a direct connection between Magento 2 and Microsoft Access automating attributes editing and data updates. Let’s say a few more words about the user-friendly approach to data import from Microsoft Access to Magento 2

Direct Microsoft Access Connection

As we’ve just mentioned, you connect to the remote database directly, but it is still possible to use file transfers with the Improved Import & Export Magento 2 extension. In both cases, data updates can be fully automated. The module allows creating individual schedules for all update types. Since we want to describe the most efficient algorithm, this article concentrates on the direct connection that eliminates file transfers. It means that you don’t need to create CSV files as described at the beginning of this blog post. The Improved Import & Export extension transfers all data to your store without building them. As a result, you save tons of time, but what about different attribute standards?

Data Mapping for Magento 2 Microsoft Access Integration

The problem of different attributes still has to be solved. Either you use data files or not, the Improved Import & Export Magento 2 extension offers a user-friendly mapping interface with tons of beneficial features. Its goal is to simplify attributes matching and make the process as fast as possible. As for the Magento 2 MS Access integration, our extension allows you to map all attributes right in the extension admin and save the created scheme for further updates. As a result, the process of attributes matching is turned into a one-time procedure – unlike the traditional approach. All mapping opportunities of the Improved Import & Export Magento 2 extension are described further in this article. Now, we’d like to draw your attention to the data import process that takes place during the Magento 2 Microsoft Access synchronization.

Direct Data Import from Microsoft Access to Magento 2

Instead of multiple steps and procedures related to the traditional approach, the Improved Import & Export Magento 2 extension requires the following things to be done:

• Go to the Magento 2 admin, find Improved Import & Export and open its import interface.
• Create a new Import profile:
  • Specify a schedule of updates;
  • Configure other parameters, such as the type of your connection and import behavior, etc.
• Next, specify a database and a table you want to import data from;
• Map attributes within a convenient interface of the new profile.
• Launch the update. If a schedule is created, the extension will launch all further updates automatically.

You must admit that this algorithm is much more comfortable than the one described in the traditional approach section. You not only simplify data transfers dramatically but also automate all further updates. Now, let’s take a look at some core features of Microsoft Access and then proceed to the functionality of the Improved Import & Export Magento 2 extension.

Get Improved Import & Export Magento 2 Extension

Microsoft Access Features

People often compare Microsoft Access with MySQL, Microsoft SQL Server, and SQLite. It combines a backend RDBMS (JET / ACE Engine) with a GUI frontend for data manipulation and queries. The primary database model of MS Access is Relational DBMS; the secondary one – Key-value store. Some other features of Access are described below:

• The initial release year of the database is 1992.
• Its current release is 16.0.4229.1024.
• The database has a commercial license.
• No cloud-based options are available.
• The implementation language of MS Access is C++.
• As for the server operating systems they include Windows only.
• The data scheme is available, as well as typing and secondary indexes.
• Although SQL is supported, Access is not compliant to its standards.
• APIs and other access methods include DAO, ADO.NET, OLE DB, and ODBC.
• As for the supported programming languages, they are C, C#, C++, Delphi, Java (JDBC-ODBC), VBA, and  Visual Basic.NET.
• Server-side scripts are used for stored procedures.
• Triggers are implemented via the ACE engine.
• No partitioning or replication methods are available.
• Although Access lacks Map/Reduce, it offers API for user-defined Map/Reduce methods.
• Foreign keys are available.
• Transaction concepts include ACID.
• Concurrency is available as well.
• The same is about durability.
• User concepts are not available.

Now, we should say a few more words about the nature of the Improved Import & Export Magento 2 extension. Below, you can see its core improvements that entirely revamp the way data is transferred between Magento 2 and external systems.

Improved Import & Export Magento 2 Extension

Below, we shed light on the advance system of schedules introduced in Improved Import & Export, describe its extended connectivity options, and list broad mapping opportunities of the plugin.

Rich Scheduling Options

With the help of the Improved Import & Export Magento 2 extension, you can create any custom schedule of updates on the basis of cron. It is only necessary to apply the standard cron syntax. If you don’t know what it is, our module offers a list of the predefined intervals. After choosing one that is the most suitable for your e-commerce needs, you can always customize it.

At the same time, the Improved Import & Export Magento 2 extension allows creating import/export profiles that don’t have schedules. Thus, they can be launched only manually. Besides, you can run every update before schedule. If your partner provides necessary data but the corresponding import process is planned for tomorrow, you don’t need to wait. Just launch the appropriate profile in an asynchronous mode. Below, you can see how to create various schedule types:

Extended Connectivity Options

The Improved Import & Export Magento 2 extension dramatically increases not only the list of supported file formats, but it also adds numerous new file destinations and offers alternative ways of data transfers. Let’s take a look at each improvement separately.

Multiple File Formats

While Magento is designed to move CSV files only, the Improved Import & Export extension dramatically enhances the default functionality of the platform. The module provides the ability to import/export not only CSV but also the following file formats:  XML, JSON, ODS, and Excel.

Numerous File Sources

In addition to multiple file formats, you can also leverage several different file sources. The Improved Import & Export Magento 2 extension supports the following options:

• FTP/SFTP. You can use a local or remote server to import/export data files to/from Magento 2.
• Dropbox. A Dropbox account is another way to establish the connection between your store and an external system.
• URL. Direct URL uploads are possible as well. Note that this option is viable for import processes only.

It is also worth mentioning that the Improved Import & Export Magento 2 extension unzips all compressed data automatically.

Alternative Ways of Import & Export

With our extension, you can freely transfer data from Google Sheets as shown below:

SOAP API, REST API, and GraphQL API integrations are possible as well. They are utilized for more complex connections. Note that schedules, mapping, and other features are available for all connection types, file types, and file sources.

Advanced Mapping Functionality

Mapping functionality is another crucial aspect of the Improved Import & Export extension. The module offers lots of improvements in this area providing numerous features that save your time and effort and eliminate manual data editing. Let’s take a look at each function individually.

Attributes Mapping

We’ve already described the problem caused by different attribute standards, so to make the long story short, let’s take proceed to the cure for this disease. The Improved Import & Export extension enables you to use a mapping interface to match external attributes to ones used in Magento 2. Thus, you can easily save tons of time with the help of our module. The module lets you make any external output suitable for the Magento 2 import within just a few clicks. You should only apply a corresponding preset for the selected import table.

Mapping presets are pre-made mapping schemes that dramatically improve every integration. Created by the team of specialists, they completely eliminate the necessity to match attributes on your own. Instead, you need to:

• select a platform which provides the import file;
• apply the corresponding mapping preset.

That’s all. You neither need to edit data files directly or configure mapping presets manually in the admin. Let’s take a look at how to apply a mapping preset to enable data migration from Magento 1 to Magento 2.

The process is as simple as going to the ‘Select a Platform’ field, choosing the necessary preset, and clicking the ‘Load Map Attributes’ button. You can also take a look at sample files which are available below the drop-down.

With the help of mapping presets, the Improved Import & Export extension maps attributes automatically. To achieve this goal, the module compares system attributes with ones specified in the import file. Next, it matches the unsuitable table fields to the corresponding system values. As a result, you get a list of mapped attributes:

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

At the same time, you can quickly complete the same procedure manually creating the same import or export profile in the admin section. First of all, go to the mapping section mentioned above. Here, you can add a new mapping row and fulfill its columns. In the ‘System Attribute’ column, select a default Magento 2 attribute. In the ‘Import Attribute’ column, specify a corresponding import attribute.

As for the third column, you can apply hardcoded (default) values which will be specified for each imported item in the corresponding column.

The created mapping scheme is saved for further updates. Our extension applies them for new updates automatically so that the mapping procedure is turned into a one-time process. Nothing similar is possible in case of default Magento or most third-party extensions. But the Improved Import & Export Magento 2 extension is more powerful.

Attribute Values Mapping

With the Improved Import & Export Magento 2 extension, you can map not only attributes but also their values. As a result, there is no need to edit them manually making suitable for the specific e-commerce requirements. And you no longer need to move random values into your e-commerce website.

The Improved Import & Export Magento 2 extension lets you quickly match external attribute values to the corresponding values inside your database. All necessary tools are available in the mapping section mentioned above. The process of value synchronization is reduced to the following steps:

• select an external attribute;
• map it to the internal one;
• match their values.

Let’s take a look at a tiny example that illustrates the functionality. Imagine that you need to import new products which contain product data with the “color” attribute with “deep-blue” and “ultramarine-blue” values while the same attribute in your database contains only one value that matches the ones above – “blue.”

With the help of the Improved Import & Export Magento 2 module, you can easily map both external values to the one used inside your store so that products will be imported with the edited color value: “blue” instead of “deep-blue” and “ultramarine-blue.”

Attribute Values Editing

If the functionality described above isn’t enough to satisfy your business goals and you still need to edit attributes manually, take a look at the ability to modify attribute values during the import process. The previous feature has an equivalent that allows editing of multiple attribute values in bulk using various rules. The primary rules are described below:

• Add prefix. You can add any prefix to any attribute value. Let’s return to the aforementioned example that describes manipulations with the “color” attribute. For instance, you want to replace its “green” value with “dark-green.” Just add the “dark-” prefix to the existing value, and the extension will do everything automatically.
• Add suffix. The ability to add a suffix is possible as well. And you can always combine them if necessary.
• Split data. If data related to a few Magento attributes is stored in a single bloc, the Improved Import & Export Magento 2 extension allows splitting it into several sections.
• Merge data. And you can easily combine several cells creating a single block of data.

Use RegEx to edit values stored in the source file. For further information regarding the corresponding functionality of the Improved Import & Export extension, read this article: How to Modify Attribute Values During Import and Export in Magento 2.

Attributes On The Fly

If you think that no more features can improve your interaction with attributes during the import, you are mistaken. The Improved Import & Export extension allows creating them on the fly. Thus, you are always saved from issues caused by the lack of attributes in the import file. Replace the attribute name with the data necessary to create a missing attribute and prevent multiple future problems. To generate missing attributes on the fly, use this general format:

Note that a pipe symbol separates values; each attribute column starts with ‘attribute’. The full scheme for creating attributes has the following appearance:

To discover more information about how to create attributes on the fly, follow this link: Product attributes import.

Category Mapping

The category mapping is the last mapping feature we’d like to draw your attention to. With the Improved Import & Export Magento 2 extension, you can save tons of time by matching external categories to the existing ones right in the import profile. Furthermore, the module lets you create something utterly new right during the update process.

In the Map Categories section of a new import profile, you can load values from the ‘categories’ column of the import table. To achieve this goal, follow these steps:

• Click the ‘Load Categories From Import File’ button.
• Hit the ‘Add New’ button.
• Find the ‘Import category path’ column.
• Choose an external category from the import file.
• Match it to an appropriate Magento 2 category in the ‘New category path’ column.

The process looks as follows:

To create a new category, click the ‘New Category’ button, specify its name, and select a parent category. The process is illustrated below:

For further information about the Improved Import & Export Magento 2 extension, watch the following video:

Conclusion

As you can see, the Improved Import & Export extension not only improves the way you import data from Microsoft Access to Magento 2 but also makes every transfers much more user-oriented and intuitive. If you are looking for the number one time-saver when it comes to import and export processes, give it a try. Follow the link below for more information:

Get Improved Import & Export Magento 2 Extension