The solution is used to load elements and its properties from the xlsx-list into a specific infoblock. Most often this is required to import a price list in the .xlsx format to the Trade catalogue.
After the module is installed, a new item in the administrative section appears (Services — Import from Excel). When you click on this menu item, a page with import profiles opens (you can create several profiles).
To create a new data import profile, click «New import profile».
Fill in the following fields:
— Profile name — enter the profile name to identify it in profiles list.
— File — attach the Excel file with the extension .xlsx.
— Infoblock ID — select the infoblock type, into which you want to import the elements. In the next field, specify the infoblock.
— Number of items processed per import step — specify the number of elements that will be processed in one step.
— Only update elements (don’t create) — tick this box only to import data into existing elements, without creating new ones.
— Deactivate new elements — tick this box and newly created elements will be not active (deactivated).
— Deactivate an element if the quantity is zero — tick this box to deactivate the element in case the quantity is zero and quantitative accounting is enabled.
— Deactivate an element if the price is zero — tick this box to deactivate the element in case its price is zero and quantitative accounting is enabled.
— Activate the element if the quantity and price are NOT zero — tick this box to activate the element, if the price is greater than zero, the amount is greater than zero, and the quantitative accounting is enabled.
— Header row — specify the row number of the Excel-file, that contains the column headers.
— First row with data — specify the row number of the Excel-file, from which the data for import begins.
— Last row with data (if not indicated, to the end of the sheet) — specify the row number of the Excel-file, that ends the data to be imported. If this field is empty, the file will be processed to the end of the sheet.
— ID of the section for new infoblock elements (default is root) — to load data into the selected subdirectory.
After filling in all the fields click «Next».
At the second step of the import configuration, at the top of the window, you will see the first rows from your Excel-file.
In the second part, you have to configure the comparisons for the import.
To add new import rule, click on the «+» next to the label: «Unique item identifier». Fill in the row with the fields:
— Column in the file — the name of column with data in the Excel file.
— Infoblock field/property — the name of the infoblock field/property to import data from the specified column.
— Conversion — if necessary, select the conversion type — Date, Date with time, Picture, URL.
— Add. Settings — arbitrary settings, which are passed to event processors (which are described in README.md), developers can use them at their discretion.
— Must be filled — tick this box if you don’t want to import the row, if this cell is empty.
Before proceeding to the next step, make sure that the switch in the column «Unique item identifier» is set to the correct row — this field will be used to identify elements.
When you select an infoblock field/property of the «Picture» type — if there is a picture in the cell of Excel-file, then you have to specify the conversion — «Picture». If the column contains a link, you have to choose, what to record into the element: if it will be a link, choose «Link» in the field «Conversion», if it will be the name of the link — without conversion.
To download images from imported links, use the «Automaton» solution.
After filling in all the rules click «Save and import». If no errors are found, the module will display the number of updated and added elements.
— Data is imported only from the first sheet of the Excel-file, regardless of number of sheets.
— For correct operation of the module, use xlsx-files, NOT xls.
— Zeros are only checked for elements that have a quantitative accounting enabled.
Attention! For the correct operation of the module in php settings you have to enable extensions: zip, xmlwriter and dom:
If the extension php-zip is not installed, install it:
yum install php70*zip
If the extension is installed, activate it in the file /etc/php.d/40-zip.ini:
; Enable ZIP extension module
extension=zip.so //or other path to zip.so (can be found by searching), for example extension=/opt/remi/php70/root/lib64/php/modules/zip.so
xmlwriter and dom
In the folder /etc/php.d/ replace the contents of the file:
20-xmlwriter.ini.disabled в 20-xmlwriter.ini
20-dom.ini.disabled в 20-dom.ini
Don’t forget to reboot the web server and check the application settings through phpinfo –i.
Simplified mode for groups
If necessary, you may configure the simplified mode for groups in the section Settings — Module settings — Import from Excel.
On the settings page you may choose groups and specify the settings of the first step for them, then it will not be available to change to users of these groups and will be set by default when importing.
1. Download the solution on the Marketplace, specify the path to install.
2. In the administrative part of Bitrix, click Install Updates. In the next window, under the license agreement, tick two boxes and click Apply.
3. Then click Install.
4. After installation, the module doesn’t require any additional settings (however, if necessary, you may configure the simplified mode for groups in the section Settings — Module settings — Import from Excel). In the administrative part of the portal, in the Services section, new item will appear — Import from Excel.
Working with lists
Now the module allows to import data into fields of type «List». Only those fields, values of which are present in the selected list, are imported. If there is no such value in the list, the field in the imported product (element) will be empty.
The import is case-sensitive (large and small). For a successful import, the names (or other imported List data) must be written exactly the same in the file and in the List values.
Let's consider a simple example.
For example, the portal has a list, one of the fields of which contains a list of cities (field type — List).
Create an import file containing the names of the cities. Please note that one of the names is written with a small letter — it will not be imported into the list, because the value of the field on the portal is written with a capital letter.
Proceed to import. Select the file we created and specify the desired info block (list) to import.
Specify matching columns for import.
19 elements added.
Now look at the result of the import. As you can see, the fields with directions that were not in the list values remained empty. Belgorod is also not imported, because it was written with a small letter in the file.