How to Run Magento 2 Import if Required Columns Are Missed

- E-Commerce, Magento 2, Our extensions

Magento 2 Import without Required Columns

Below, we shed light on a very important aspect of every import process – columns (attributes) of an import file. What if your data table doesn’t contain one? By default, you face the inability to transfer the update to your Magento 2 website. There are lots of different entities that can be transferred into Magento 2, so you have to deal with different data depending on your particular business case. However, the absence of the smallest detail can ruin all your data transfer efforts. Especially if you use the default data transfer solutions. 

Luckily, we know how to prevent such issues. Meet the Improved Import & Export Magento 2 extension. With the help of this plugin, you can import data to Magento 2 if some required columns are missed. The following article describes this process. You can either add the necessary columns manually or automate the addition of the missing data using the module’s functionality. Besides, you will discover the required CSV file structure as well as core features of the Improved Import & Export Magento 2 extension

Magento 2 Product Import: CSV File Structure

First of all, let’s take a look at what columns and attributes are in a standard CSV file for importing product data. Note that files for different entities include different designations, but we describe them lately.  

  • sku – the first column of every product data file. It is a unique product identifier used as a key product attribute. SKU is required to be unique for all products. It can include both digits and letters without spaces separated with “_” and “-”.
  • store_view_code – a code of an associated store view. Note that you can leave this column empty in a case when an associated store/website has a single subitem.
  • attribute_set_code – a code of a product attribute set, which should be created and configured before importing products into Magento 2. Note that there is a strict requirement regarding the way it looks: It should be exactly the same as an attribute set name including capital letters.
  • product_type – this one specifies the type of an imported product. Only lowercase letters are allowed.
  • categories – the name of this column (attribute) speaks for itself. Note that product categories in Magento 2 differ from ones used in Magento 1. Thus, full names of assigned categories, including full path, are required. Also, note that assigned categories should be separated by “|”.
  • product_websites – a code of an associated product website specified with lowercase letters.
  • name – a name of a product.
  • description a description of a product.
  • short_description its short description. Note that you can use valid HTML tags for both description and a short description.
  • weight a weight of a product which is specified via the following format 1.00000.
  • product_online – in this column, you can enable or disable products using 1 or 0 respectively.
  • tax_class_name a tax class of a product. In this case, you can use capital letters and spaces.
  • visibility – this column allows specifying a visibility of a product.
  • price the price of a product.
  • special_price – and its special price.
  • special_price_from_date – in this column, you can specify a date when a default price is replaced with a special one.
  • special_price_to_date – specify time when Magento stops using a special price.
  • url_key – a URL key of a product. If a field is empty, a value is generated automatically from a product name.
  • meta_title – a product meta title.
  • meta_keywords – product-related meta keywords.
  • meta_description – a meta description of a product.
  • base_image the main product image and its path are specified here.
  • base_image_label – a base product image label.
  • small_image – a name and a path of a small product image.
  • small_image_label its label.
  • thumbnail_image – a name and a path of a thumbnail product image.    
  • thumbnail_image_label – its label.
  • created_at time when a product was created. If the field is empty, Magento uses date and time of a database record.
  • updated_at time when a product was updated in. If the field is empty, Magento uses date and time of a database record.
  • new_from_date – use this column to set a product as “new” from a certain date.        
  • new_to_date this one is designed to stop displaying a product as “new” from a certain date.       
  • display_product_options_in here, you can specify a place on a product page where a block with options should be displayed.
  • map_price – a minimum product price.
  • msrp_price – an MSRP price of a product.
  • map_enabled – in this column, you can enable/disable a minimum price of a product.
  • gift_message_available – here, the availability of a gift message is specified.
  • custom_design – this column contains a custom design of a product page.      
  • custom_design_from – its starting date.
  • custom_design_to its end date.
  • custom_layout_update – a custom XML layout for a product page.
  • page_layout a product page layout. If you leave a field empty, a default product layout is applied.
  • product_options_container – this column (attribute) is used for specifying a product options container.
  • msrp_display_actual_price_type   here, you can set a type of a product’s MSRP price.
  • country_of_manufacture – a country of a product origin.     
  • additional_attributes – this column is used for import of product custom options and data related to a simple product associated to a configurable product.
  • qty – here, you can specify a quantity of a particular product in stock.      
  • out_of_stock_qty an opposite option.
  • use_config_min_qty – here, you can enable the use of a minimum quantity value from the configuration.
  • is_qty_decimal set 1 to enable decimal quantity.         
  • allow_backorders set 1 to enable backorders.
  • use_config_backorders – enable/disable a default system value for backorder options.
  • min_cart_qty – in this column, you can specify a minimum quantity of a product in a cart.
  • use_config_min_sale_qty – this column lets you use a default config value to determine a minimum quantity of a sale product.
  • max_cart_qty – a top limit for a product quantity in a cart.     
  • use_config_max_sale_qty –  this column lets you use a default config value to determine a maximum quantity of a sale product.
  • is_in_stock – specify whether a product is in or out of stock.
  • notify_on_stock_below –  this column lets you set a minimum product quantity to send a notification about a low stock level.
  • use_config_notify_stock_qty – alternatively, you can use a default system value.
  • manage_stock enable/disable control over a stock quantity of a product.
  • use_config_manage_stock – at the same time, you can use a default system configuration for stock management.
  • use_config_qty_increments – enable/disable a product quantity increment from current store configuration.
  • qty_increments – here, you can set a particular product quantity increment.
  • use_config_enable_qty_inc this column lets you use a default store configuration value for the increment.
  • enable_qty_increments – enable/disable the product quantity increment functionality.
  • is_decimal_divided enable/disable decimal product quantity increment.
  • website_id here, an associated product website ID is listed.
  • related_skus related product SKUs separated with commas.
  • crosssell_skus – cross-sale product SKUs separated with commas.
  • upsell_skus upsell product SKUs separated with a comma.
  • additional_images – this column allows specifying additional product images separated with commas.                          
  • additional_image_labels comma separated labels for additional product images.
  • _associated_sku here, you can specify associated simple product SKUs for a configurable product.     
  • _associated_default_qty and don’t forget about their default quantity.
  • _associated_position the position of an associated product can be specified here.

As you can see, a product data file contains lots of columns. As for other entities, they have a different structure. Follow this link for further information about product file columns and attributes: CSV File Structure. You can find other columns and attributes related to other entities in this article: The Complete Guide to Magento 2 Product Import / Export.

Now, let’s take a look at how to run Magento 2 import if some required columns are missed.

How to Run Magento 2 Import if Some Required Columns Are Missed

There are two ways you can complete an import process successfully if some columns are not available in your data file.

Add Columns Manually

You can always edit the content of your import file manually. It is a time-consuming procedure that requires much time and effort especially when a data file is huge. Open it with an editor of your choice and type missing attributes. Next, specify their values.

Attributes On The Fly

Magento 2 mysql data import

Alternatively, you can rely on a third-party Magento 2 extension that allows creating attributes on the fly. Meet the Improved Import & Export module! This plugin entirely revamps the default import/export functionality of Magento 2 allowing you to leverage multiple new features and dramatically simplify the standard routine processes.

If an update file doesn’t have some columns (attributes), the import process can be interrupted, but our tool lets you solve the problem due to the ability to create attributes on the fly. Use the following general form to specify all parameters necessary to generate attributes:

Attribute|attribute_property_name:attribute_property_value|…

As you can see, not only columns are added but also attribute values! For further information, read this article: Product attributes import.

Now, we’d like to draw your attention to other features of the Improved Import & Export Magento 2 extension which simplify data transfer processes.

Improved Import & Export Magento 2 Extension Features

Magento 2 oracle import

Improved Import & Export enables you to turn your Magento 2 admin into a central hub where you control all parts of a complex business structure that combines multiple platforms and tools. Our module is designed to establish direct connections and data flows between your e-commerce website and external systems. As a Magento administrator, you get an opportunity to manage all integrations right in the Magento backend.

Note that the extension has already become a number one import/export tool for hundreds of merchants worldwide providing them with the opportunity to seamlessly exchange information between Magento 2 and any external systems. And you have a chance to join them right now, but let’s see how the tool simplifies your daily routine.

Automated Import & Export Processes

The first thing we’d like to start our description with is the ability to automate import and export processes. While you cannot do that with the default Magento 2 tools, our module lets you spend time on more important things. It allows configuring an import or export profile only once. Next, the plugin do all the work for you. There are two ways to establish the automation: schedules and event-based triggers.

Schedules

With the Improved Import & Export Magento 2 extension, you can use the default cron syntax to create any update schedule.  Our module lets you generate any custom intervals or select one of the predefined values which are fully customizable.

Alternatively, it is possible to use profiles which have no schedules. Right, they are not the most efficient solution for continuous integrations, but it is possible to leverage them in case of data migrations when one transfer is enough or some asynchronous actions are required.

Launch every scheduled profile manually whenever you want due to the asynchronous data transfers functionality. Thus, if a partner provides a file after a scheduled update, the Improved Import & Export Magento 2 extension lets you easily transfer it into your database manually within just a few clicks. There is no need to wait for the next planned import event.

And configuring a schedule of updates is quite a straightforward procedure, as you can see from the gif below:

Events

In their turn, event-based triggers provide improvements necessary for real-time updates. You need to specify a proper trigger and corresponding conditions to synchronize two systems more precisely right when a certain action on your store takes place. That’s how you enable instant data transfer on the basis of the Improved Import & Export Magento 2 extension. The same approach can be applied to export processes. For further information, follow this link: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

As formapping opportunities provided by the Improved Import & Export Magento 2 extension, they dramatically simplify every data transfer to and from your store. Instead of editing everything manually, our module offers several handy tools which not only simplify the way you match attributes but also eliminate the necessity to repeat the procedure for the second time. Once configured, a profile uses the specified mapping scheme automatically.

Mapping Presets

The easiest way of solving problems caused by different attribute standards is based on mapping poresets. The Improved Import & Export extension offers the ability to reduce them by matching external designations to the corresponding internal ones in a matter of seconds. Select and apply a preset related to your integration, and our module will do all the work for you.

The extension analyzes the input data and matches it to the internal attributes in the most user-friendly and fastest manner. No manual interaction with data files which requires tons of time and effort is needed. And as we’ve just mentioned, the procedure should be done only once. The process of applying a preset is illustrated below:

Matching Interface

If a certain preset is not available, you can use a matching interface. It is designed to map external attributes to the corresponding internal ones right within your Magento admin. Manual matching via our interface looks as follows:

As you can see, the interface also contains the third column called “Default Value.” Use it to specify hardcoded (default) values which are provided to all items related to a corresponding attribute.

Attribute Values Mapping

In addition to attributes, you can also map their values which dramatically simplifies every integration with external platforms. Even if all columns available and proper attributes are specialised, you may still face the lack of proper values. In the case of the default Magento tools, it is necessary to edit third-party designations manually. Luckily, the Improved Import & Export Magento 2 extension provides the ability to do that in a user-friendly interface.

The process of attribute values mapping is exceptionally straightforward: choose a third-party designation and specify a corresponding internal one in front of it. The procedure is similar to the attribute matching described above. Repeat values matching until no external designations are left. For further information, follow this link: Attribute Values Mapping.

Attribute Values Editing

The Improved Import & Export Magento 2 module also lets you modify attribute values. The process is available as a bulk action on the basis of the following rules:

  • Add a prefix to multiple attribute values;
  • Add a suffix to multiple attribute values;
  • Split multiple attribute values;
  • Merge multiple attribute values.

Combine the rules and create various conditions to increase the efficiency of editing. Follow this link for further information: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

And you can also map categories. This feature is useful when it comes to product data transfers from external stores or systems. You may face a situation when some items are bound to categories that are not represented on your e-commerce website. Thus, you need to edit external categories manually, apply changes after data is imported, or use our plugin.

While manual editing requires tons of time and effort, the Improved Import & Export extension simplifies your routine dramatically. Match external product categories to ones used internally with our extension as shown below:

And it is even possible to create new categories on the fly. Select a parent category and specify a new one to add a missing section to your catalog. Both features are described in this article: Category Mapping.

Extended Connectivity Options

Below, we shed light on mechanisms that help to connect Magento 2 to other platforms. The default instruments are limited when it comes to data transfers. The platform offers only one way to synchronize information between your e-commerce store and external systems which is based on CSV files. Luckily, there is the Improved Import & Export extension that not only provides several more ways to do so but also offers an extended range of supported file formats.

Multiple File Standards

A vital improvement to the default import functionality of Magento 2 is the number of supported file formats. With the help of The Improved Import & Export Magento 2 extension, you can run data transfers using XML, JSON, ODS, and Excel files. Furthermore, it is possible to apply all the features described above to any of them.

Multiple File Sources

To make file-based data transfers more flexible, the Improved Import & Export Magento 2 extension provides the ability to leverage several different file sources. The following connection options are available:

  • FTP/SFTP. You can transfer files using your local server. Another option is represented by a remote one.
  • Dropbox. A Dropbox account can be also used if necessary.
  • URL. For import processes only, it is enough to specify a file URL, and the module will import it.

And if an update is packed in an archive, you don’t need to extract it, because our module will do everything for you right during the import procedure.

Alternative Ways of Import & Export

The best thing about the Improved Import & Export Magento 2 extension is that you are not limited by file transfers only. Our extension provides two more ways of connecting your e-commerce store to external platforms. You can freely create API integrations or apply intermediary services to exchange data.

Our module lets you connect your website to an external system via REST, SOAP, and GraphQL APIs. Note that every API connection supports such modules features as mapping, scheduling, category matching and attributes on the fly. Thus, you can fully automate data transfers establishing even the most demanding integration.

As for intermediary services, our module lets you work with various online sheets. The Improved Import & Export Magento 2 extension supports Google Sheets, Office 365 Excel, and Zoho Sheet. They allow exchanging data between your website and external platforms in a way more simplified manner with the default tools offer. You can see the integration on the basis of Google Sheets below:

Magento 2 Google Sheet import

Watch the following video to see the extension in action:

Final Words

If a data file lacks some columns or attributes, it is no longer a problem, especially when you use the Improved Import & Export Magento 2 extension. Furthermore, the module lets you add even missing attribute values as well as match external designations to ones used internally in the easiest way. For further information about the module, follow the link below. Note that you can always contact our support to share your specific ideas regarding the extension. And we will discuss their implementation together!

Get Improved Import & Export Magento 2 Extension

Improved Import & Export FAQ

Let’s explore other nuances of transferring data to Magento 2 with the help of the Improved Import & Export Magento 2 extension.

Which data sources does Improved Import & Export support?

With the help of the module, you can transfer data between your e-commerce store and external systems using direct file uploads, FTP/SFTP servers, Google Sheets, Google Drive, OneDrive, DropBox, direct URL uploads, REST and SOAP APIs.

Which file types does Improved Import & Export for Magento 2 support?

The Improved Import & Export extension works with CSV, XML, XLSX, ODS, JSON, ZIP, and TAR files.

How to import and export data files in Magento 2 automatically?

If you need to import or export data files in Magento 2 automatically, the Improved Import & Export extension lets you use cron. Generate a schedule of updates and let the plugin do everything for you.

How to import third-party data to Magento 2?

The Improved Import & Export extension lets you create a mapping scheme where you match third-party attributes and values to the corresponding ones used within Magento 2. Thus, you teach the system how to replace the unsupported data. Apply mapping to export processes to create any custom output your partners may require.

How to import and export data via ODS files to Magento 2?

Improved Import & Export lets you move data via ODS files, so there is no need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import and export data via XLSX files to Magento 2?

Improved Import & Export lets you move data via XLSX files, so there is no need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import and export data via XML files to Magento 2?

Improved Import & Export lets you move data via XML files, so there is no need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import and export data via JSON files to Magento 2?

Improved Import & Export lets you move data via JSON files, so there is no need to convert them to CSV to fit the Magento 2 requirements. You can also export data using this file format.

How to import data to Magento 2 via compressed files?

If your data files are compressed, the Improved Import & Export extension lets you transfer them to Magento 2. Your file archive should be in a ZIP or TAR format, containing CSV, XML, JSON, ODS, and XLSX files.

How to import data to Magento 2 from Google Sheets?

The Improved Import & Export module lets you import tables from Google Sheets to your e-commerce website. It is also possible to export data to Google Sheets from Magento 2 with the help of our extension.

How to import and export data files between Magento 2 from Dropbox?

The Improved Import & Export module lets you transfer data tables between Magento 2 and Dropbox via CSV, JSON, ODS, XML, or XLSX files and ZIP or TAR archives.

How to import and export data files to Magento 2 from Google Drive?

The Improved Import & Export module lets you transfer data tables between Magento 2 and Google Drive via CSV, JSON, ODS, XML, or XLSX files and ZIP or TAR archives.

How to import and export data between Magento 2 and external systems via API?

While Magento 2 doesn’t let you create API connections within your administration, you can do that with the Improved Import & Export extension. The module’s import/export jobs provide an interface where you can specify the parameters necessary to sync your e-commerce website with any external systems via API.