How to import data from Microsoft SQL Server to Magento 2

We hate to say it, but there is no way to import data from Microsoft SQL Server to Magento 2 by default. There are two ways of achieving this goal. The traditional approach requires much time and effort to complete the procedure, while a user-friendly one is based on the use of a third-party tool. Let’s pay more close attention to each one.

Traditional Approach to Moving Data from Microsoft SQL Server to Magento 2

Imagine a situation when you have a remote Microsoft SQL Server database with a product table. You need to take this data and update your e-commerce store with its help. The process is quite complicated since it is necessary to perform several independent actions manipulating the existing information and transforming its appearance within files and systems. Spoiler: there is a much easier way to achieve the same goal, but let’s return to the community-proven practices.

If you’ve read our previous posts related to the Magento 2 database synchronization, you should know that both MySQL and Oracle require you to complete three sets of various processes. Firstly, you should get the desired information out of your external database. Secondly, it is necessary to edit the data to make it suitable for Magento 2 requirements. Finally, you can update your store by importing the modified file from the database.

The algorithm is always the same, but the devil is in the detail; Therefore, let’s take a look at how to export data from Microsoft SQL Server to CSV.

CSV Export from Microsoft SQL Server

There are multiple ways of exporting data from the Microsoft SQL Server database to a CSV file. Let’s take a look at several common practices. In the following article, we briefly describe the following four approaches to the Microsoft SQL Server data export to CSV

1. Via the SQL Server export wizard.
2. Via the bcp Utility.
3. Via SQL Server Reporting Services in SQL Server Data Tools within Visual Studio.
4. Via the ApexSQL Complete.

Export Microsoft SQL Server data to CSV by using SQL Server export wizard

You can utilize the SQL Server Import and Export Wizard to export SQL Server data to CSV. Follow the steps below to complete the procedure:

• Open SQL Server Management Studio (SSMS);
• Next, connect to the SQL instance;
• In Object Explorer, choose a database;
• Then, right click on the database, go to Tasks, and select Export Data;
• In the SQL Server Import and Export Wizard window, click the Next button;
• Next, choose the data source and select a SQL Server instance;
• Now, you can choose authentication for the data source connection;
• Finally, you can select a database from which a data will be copied;
• Press the Next button.

And this is just the beginning. You have to configure numerous options on almost ten more screens. Check the following video for further information:

Looks very complicated, isn’t it?  Therefore, let’s proceed to another approach to data export from Microsoft SQL Server to CSV.

Export SQL Server data to CSV by using the bcp Utility

The bcp (bulk copy program) utility is a tool designed to simplify the data flow between SQL Server tables and files. It requires using cmd.

In the new cmd window, type the “bcp ?” command. You will see a screen with all available switches.

Next, type “bcp” followed by the name of the desired SQL table. It should have the following structure: “name of the database” + “dot” + “schema name” + “dot” + “table name”. After the name of the SQL table, press the Space key followed by the word “out”. Press Space once again and add a location of a CSV file. For instance, “D:\Tables\Export.csv”.

And we are still far from being at the end of the procedure. To export SQL Server data to CSV file, you need several more switches to be added to the command. First of all, it is -S followed by the name of the SQL Server instance: “-SWIN10\SQLEXPRESS”.

Now, you need to add two more switches – “-c” and “-t” – as well as add a separator. In our example, a comma (,) separator is used. Consequently, the command now includes the following additional elements: -c -t”,”.

Finally, you can specify how to access to the SQL Server:

• -T – the trusted Windows authentication;
• -U – the SQL Server authentication;
• -P – the SQL Server user password.

Let’s use the trusted connection to export Microsoft SQL Server data to CSV via SQL Server export wizard. The command should look as follows:

Enter the command, press the Enter key, and get the information regarding the copied data.  

Export SQL Server data to CSV by using SQL Server Reporting Services in SQL Server Data Tools within Visual Studio

With the help of SSRS, you can save data output of the Microsoft SQL Server not only in CSV but also in multiple other file formats including PDF, XML, Word, etc.

Open SSDT;

• Go to File ->New -> Project;
• Next, proceed to Business Intelligence -> Reporting Services and choose the Report Server Project Wizard option.
• Specify name, location, and solution name for the CSV output.
• Then, configure numerous more parameters for multiple screens like in case of the first approach.

The procedure is very complicated but user-friendly. Don’t afraid that we miss so many vital parts – you will find a link to the full tutorial below, but let’s take a look at another way to export data from SQL Server to CSV.

Export SQL Server data to CSV by using the ApexSQL Complete

ApexSQL Complete offers the fastest way of exporting data from SQL Server to a CSV file. A free add-in for SSMS and Visual Studio provides a command, Copy code as, that copies the data from the Results grid as CSV, XML, or HTML. The procedure consists of the following steps:

• Firstly, open a query editor;
• Next, execute the following code:

• Now, you can select all the necessary data;
• In the context menu, go to the Copy results as sub-menu;
• Then, you can select CSV as your output format.

Finally, you can create a file and paste data there. You must admit that this is the fastest approach among the provided four solutions, but it is still hard to export data from Microsoft SQL Server to CSV. And don’t forget that there are two more stages necessary to import data from a remote Microsoft SQL Server database to Magento 2.

As for the four approaches above, they are fully described here: How to export SQL Server data to a CSV file. Although you may explore the article, we recommend you to rely on a user-friendly way of data import from Microsoft SQL Server to Magento 2 which is described after the following two sections.

CSV Mapping before Import

The second crucial stage of the data import from an external Microsoft SQL Server database to Magento 2 is related to matching attributes of the selected table to the attributes of Magento 2. You cannot move a data file to your e-commerce store directly since different systems use own attribute standards. Consequently, it is necessary to edit the CSV output to make it suitable for Magento 2 standards.

The standard mapping process has two huge drawbacks:

1. It is necessary to edit data files manually;
2. You should do that every time an update is planned.

You won’t fill any difficulties until it is necessary to work with substantial CSV files or frequent updates. Both situations usually turn your daily routine into a massive headache since even the smallest mistake doesn’t let you import data files to Magento 2. It often takes even more time to discover the issue than to edit a whole document.

Therefore, we recommend you to avoid manual data file editing since you will save tons of time and effort. If you still want to make the CSV output of your remote SQL Server suitable for import into Magento 2 manually, check this article:  The Complete Guide to Magento 2 Product Import / Export. We describe the structure of a standard CSV file with all possible attributes and their values there. Thus, you will discover how to map any external attributes to the Magento 2 requirements.

Microsoft SQL Server Database Import

Finally, you have a CSV output that satisfies all requirements of the e-commerce platform so that you can update your Magento 2 store with the desired information. The default system tools are enough to complete this final step, but you can use some third-party import solutions to improve the native functionality of the platform.

The default Magento 2 import is slow and must be done manually. If you use third-party solutions, you can update your store with new information much faster. Furthermore, the updates will be fully automated (if the selected import extension offers the appropriate functionality).

Also, note that in case of errors, you should return to the previous stage. If there is an error in the edited CSV file, it won’t be uploaded to Magento. As a result, you should edit the output once more matching its attributes more carefully.

Finally, your CSV file is entirely suitable with what Magento 2 wants from it. You’ve imported all the necessary information and can spend time on another daily work. How much time did you spend on all that stages? An impressive amount! We’ve been tired only by describing all of them in this article. And don’t forget that the first stage is explored briefly. In practice, you have to spend much more time to import data from Microsoft SQL Server to Magento 2. Therefore, we insist on the user-friendly approach instead of a traditional one.

User-Friendly Approach to Moving Data from Microsoft SQL Server to Magento 2

The advanced import functionality of the Improved Import & Export Magento 2 extension lets you revamp the traditional way of updating your system from an external SQL Server table entirely. Our module reduces the number of steps necessary to complete the procedure, eliminates the use of data files, offers extended mapping opportunities, automates the workflow, and as a result saves a plethora of time and effort. Below, we shed light on the enhanced data import from an external SQL Server table to Magento 2.

Direct SQL Server Connection (Unreleased)

As we’ve just mentioned, the Improved Import & Export Magento 2 extension eliminates the use of CSV files from the update. You can still use them, but there is a better way to import data from Microsoft SQL Server to Magento 2.

The initial idea behind this unreleased feature was to connect to a database directly, reducing the export routine described above. Thus, you could perform the update at least 30% faster than in the case of the traditional approach. In practice, the time necessary to complete the process would be reduced since

• The first stage usually requires more time than the next two stages together.
• The remaining two stages are optimized and merged into one.

However, we haven’t implemented the direct connection yet. Please, contact us if you need this functionality.

Mapping & Data Import from SQL Server to Magento 2

So, let’s take a look at the continuation of the data import from an external SQL Server table to Magento 2. If you use the Improved Import & Export Magento 2 extension, the procedure is reduced to a few simple steps described in this paragraph. First of all, the only place where the update is configured and performed is your Magento 2 admin. Open the interface of our module and create a new import profile. Specify an external SQL Server database and select tables you want to import. Next, map attributes of the table to the attributes of Magento 2.  Update the system. Optionally, you can create a schedule of automated updates, so the next connections to the remote database will be fully automated.

The improved mapping process and scheduling are described later in this post. Before going any further, let’s summarize the benefits of the user-friendly approach to moving data from a remote Microsoft SQL Server database to Magento 2 over the traditional one.

• No Data Files. You eliminate the use of either CSV or other files from the update reducing the first time-consuming stage of the update.
• Unified Space for All Processes. All configurations are gathered in the Magento admin. You don’t need to use multiple interfaces to perform the update.
• Single App. No third-party apps or systems are used. You do everything within Magento with the help of our module.
• Flexible Schedules. You can fully automate the updates by creating schedules.
• Advanced Mapping. No more data file editing! Now, you can do everything within a convenient interface. The issue of different attribute standards is solved in a way more efficient manner.

Now, when you know how to import data from Microsoft SQL Server to Magento 2 more efficiently, let’s say a few words about SQL Server itself and proceed to a more in-depth exploration of our extension.

Get Improved Import & Export Magento 2 Extension

Microsoft SQL Server Facts

Microsoft SQL Server is a relational database management system that stores and retrieves data as required by other apps. It exists in multiple editions aimed at different audiences and workloads. Also, note that SQL Server includes an assortment of add-ons that provide value-added services on top of the core database management system. The most important are

• Machine Learning – machine learning and data analytics without sending data across the network or being limited by the memory of your computer;
• Service Broker – components synchronization via TCP/IP;
• Replication Services – replication and synchronization of database objects;
• Analytics Services – OLAP and data mining capabilities;
• Reporting System – reports as RDL files;
• Notification System – data-driven notifications sent to subscribers;
• Full-Text Search – an indexing and querying service for unstructured text stored of databases;
• SQLCMD – a command line application.

Besides, there is native support for SQL Server in Microsoft Visual Studio as well as such useful tools as SQL Server Management Studio, SQL Server Operations Studio, and Business Intelligence Development Studio. For further information about Microsoft SQL Server, check this Wikipedia article.

Improved Import & Export Magento 2 Extension

Now, we’d like to draw your attention to some core features of the Improved Import & Export Magento 2 extension. Below, we shed light on its advanced connectivity options as well as describe advanced mapping functionality and extended opportunities related to schedules.

Supported File Formats

The core goal of the Improved Import & Export Magento 2 extension is to connect the Magento platform to an external system or data source in the most user-friendly manner. To achieve it, the module offers several connectivity options as well as extended file formats support.

By default, most systems use SCV to transfer data. Even when you choose the traditional approach to data import from Microsoft SQL Server to Magento 2, you have to deal with CSV files.

Although Magento 2 supports them by default, it is always better to use Improved Import & Export because it not only speeds up the upload process but offers a more user-friendly and fully-featured interface.

The second popular file format is XML. Many third-party modules rely on it and our extension is not an exception, but it offers better import/export conditions. As for unique support options, they include two things. First of all, you can freely import/export data in JSON. It means that the migration from Shopify to Magento 2 becomes much more affordable now. Next, the Improved Import & Export Magento 2 Extension uses XLS and XLSX formats for various internal needs.

Supported File Sources

Of course, it is also necessary to say a few words about file sources in the context of supported file formats. Our module provides the ability to transfer data via files using several different file sources. The first option is based on FTP/SFTP support. With the Improved Import & Export Magento 2 extension, you can freely move data files through a local or remote server.

If this option is not enough to satisfy your business needs, use your Dropbox account. The Improved Import & Export Magento 2 extension fully supports this cloud allowing you to move files with its help.

For import processes only, our plugin supports direct URL uploads. You need to specify the address of a file to move it into the system. Note that compressed data is unzipped automatically. Thus, our extension improves your routine even more.

Alternative Ways of Import/Export

When it comes to data import from an external Microsoft SQL Server database to Magento 2, file transfers are not the best possible option. Therefore, the Improved Import & Export Magento 2 extension allows you to connect to the table directly avoiding the painful CSV export from the database.

Besides, our module provides the ability to import data from Google Sheets. The process looks as follows:

It is also necessary to mention that you can fully leverage the REST API connection for the integration with various ERP systems. The Improved Import & Export Magento 2 plugin not only extends the range of systems you can connect your website to but also substantially simplifies the way you do that due to scheduling and mapping.

Scheduling

Due to cron support, you can create any custom update schedules. Note that import and export plans for the same integration may be different. To create a custom schedule, you need to use the standard cron syntax. If you don’t know it, select one of the predefined intervals which can be customized as well. It is also necessary to mention that you can start every update manually aside from its schedule. Alternatively, the Improved Import & Export Magento 2 extension provides the ability to create import/export profiles suitable for a manual run only. The following gif image illustrates how to create a schedule:

Mapping

Another outstanding feature of the Improved Import & Export module is mapping. As it was mentioned above, different platforms rely on different attribute standards to represent the same data. As a result, you cannot import information from one system to another without modifying attributes. Even if it is evident that “price” and “price_” are the same attribute, you need to apply mapping to make the integration possible.

The Improved Import & Export extension offers a user-friendly interface where you can match attributes without headaches. Just look at how simple the process becomes:

Instead of endless data files which contain lots of all possible information, you have to deal with attributes directly matching everything right within the Magento backend. Furthermore, the Improved Import & Export Magento 2 Extension offers mapping presets so that the aforementioned procedure is reduced to a few clicks like illustrated below:

You just need to apply a preset that suits your integration and the module will map all attributes automatically. Thus, data import from Microsoft SQL Server to Magento 2 becomes even more comfortable. As for other features of Improved Import & Export, they are described in the following video:

The benefits of our extension over the traditional approach to importing data from Microsoft SQL Server to Magento 2 are apparent. Let’s recollect them once again:

• Magento 2 is connected to SQL Server directly -> No file transfers -> No data file editing;
• Simple mapping inside Magento -> Essentially reduced number of errors -> Fever headaches;
• Automated scheduled updates -> More time for management processes.

If you also want to improve the way data is imported from Microsoft SQL Server to Magento 2, follow the link below:

Get Improved Import & Export Magento 2 Extension