Import from Excel

What this solution does

The module enables importing data from .xlsx files into a selected portal info block. It transfers elements and their properties from an Excel sheet to a target info block, commonly used for loading price lists into a trading catalog.

Prepare an .xlsx file with the required data before starting. After installation, a new item named 'Import from Excel' appears in the 'Services' section of the administrative panel. Accessing this item opens the import profiles management page, where multiple configurations can be created.

To create a new profile, click 'New Import Profile' and fill in the fields:

— Profile Name: a name for identification in the list.

— File: attach the .xlsx file to define the column structure.

— Info Block ID: select the info block type and specify the target info block for loading.

— Elements per Step: set the number of elements processed in a single import iteration.

Update only existing items: import updates data in existing records without creating new ones.

Deactivate new items: newly created items during import are marked as inactive.

Deactivate item if out of stock: if quantity tracking is enabled and the quantity is zero, the item is automatically deactivated.

Deactivate item if price is zero: if quantity tracking is enabled and the price is zero, the item is automatically deactivated.

Activate item if price is non-zero and in stock: if quantity tracking is enabled, price is greater than zero, and quantity is greater than zero, the item is activated.

Header row: specify the row number in the Excel file containing column headers.

First data row: specify the row number in the Excel file where import data begins.

Excel Import Profile Configuration

Specify the row number of the last data row in the file. If this field is left blank, the entire sheet will be processed. Enter the section ID for the information block where new elements will be placed; the root section is used by default.

After filling in the fields, click "Save". You will be redirected to the import profiles list. Locate the created profile, click the settings button next to its name, and select "Mappings".

Configuring Mappings

This step links columns in the Excel file to fields in the target information block. Select the import profile, open its settings, and navigate to the "Mappings" section.

Each mapping between a file column and an information block field must be created as a separate item. Ensure that one item is marked as the unique identifier by checking the "Unique element identifier" flag. This is required for correct record processing.

Click "Add" and fill in the mapping parameters:

— Column in file: select the name of the data column from the Excel file list.

Skip rows if the cell is empty — enable this option to skip rows where the current column cell is blank.

Element unique identifier — select this option if the column contains a unique element identifier.

Important: At least one field or property must be set as a unique identifier for the import process to start.

Conversion — choose the required conversion type: Date, Date with time, Image, or Link. Data in this column will be converted to the corresponding format.

Element field/property mapping — select the target area: InfoBlock fields, InfoBlock properties, or Trade catalog. Set the switch to the appropriate item to define where the imported column data will be stored.

Click "Save".

Repeat the mapping procedure for each column to be imported.

Variable fields (Variable Name, Variable Value) are available only in custom handlers for rows without headers.

Import process

After filling in data on the Profile and Mapping tabs, proceed to import the file. Select the required profile from the list, open it, and navigate to the Import tab. Alternatively, click the settings button next to the profile name and choose Import. In the File field, specify the path to your .xlsx file in the computer's file system. Click the Import button.

Important limitations and requirements:

Data is imported only from the first sheet of the Excel file; other sheets are ignored.

The module functions correctly only with .xlsx format files. .xls files must be converted by saving them as .xlsx using Excel 2007 or later versions.

Zero quantity validation applies only to items where quantity tracking is enabled. This setting can be activated globally in trade catalog parameters or individually for each product.

To ensure proper module operation, the following PHP extensions must be enabled: zip, xmlwriter, and dom.

The 'Import from Excel' module requires the PHP extensions: zip, XMLwriter, and DOM.

To activate the zip extension, run the command yum install php70*zip. If the extension is already installed, add the line extension=zip.so to the configuration file /etc/php.d/40-zip.ini. If necessary, specify the full path to zip.so, for example: extension=/opt/remi/php70/root/lib64/php/modules/zip.so.

To enable the XMLwriter and DOM modules, move the files 20-xmlwriter.ini.disabled and 20-dom.ini.disabled from the /etc/php.d/ directory to 20-xmlwriter.ini and 20-dom.ini, respectively. After making these changes, restart the web server and verify the configuration using the phpinfo –i command.

In the 'Product Settings' — 'Module Settings' — 'Import Price from Excel' section, a simplified mode for user groups is available. You can select specific groups and set the parameters for the first import step. For users in these groups, these settings will become read-only and will be applied by default during import.

General iT can handle the implementation of “Import from Excel”, from installation to configuration and compatibility checks.