Setup module Update prices and products from XLS and CSV files for the online shop {SEO-Shop}, OpenCart 2.x, 3.x, ocStore

1. Module installation “NeoSeo Update prices and products from XLS and CSV files”

To install the module “NeoSeo Update prices and products from XLS and CSV files” for OpenCart, you must:

1. Go to admin panel and click on “Extras”->”Modules”:
2. In the list of modules that opens, select “NeoSeo Price Update” and click the “Install” button:
3. Go to the "Add-ons" -> "Add-ons Manager" section and click on the "Update" button in the window that opens.

This completes the installation of the module and you can proceed to editing the module settings.

2. Editing module settings

After updating the module, a new menu item “Catalog” -> “Price Update” is available in the site’s administrative panel:

Quite often, situations arise when it is necessary to update the prices of goods on the website from suppliers' prices. At the same time, price lists mainly have various formats, i.e. in these files the field locations may not match. For example, the “Price” field in one price is in the second column, and in the other price in the third. Therefore, for each price, I had to adjust the settings.

In this solution, this drawback is eliminated: for each price list, the module allows you to add a format in which the required field arrangement will be set. Thus, when loading the price list, you do not need to change any settings, just select the format corresponding to this file.

To create a new format, in the “Catalog” -> ”Price Update” section, click on the “Formats” button. A form opens with a list of suppliers. On this form, click on the “Add” button:

As a result, the form for adding a new provider will open:

This form contains the following fields:

• The code. Mandatory field for entering the unique code (number) of the supplier. What is this field for? It is possible that several suppliers with whom you work use the same article, but for different products.

For example, Supplier 1 provides the product “Lenovo Smartphone” with the article number 100345 and the price of 10,000 UAH. Supplier 2 provides the product “Nike Sneakers” with the same article number 100345, but at a price of 3000 UAH.

When updating the price of goods, the database is searched by article number. When finding a product with the necessary article, the price of this product will change to the price set in the price list. Suppose that you need to change the price of the product “Nike Sneakers” from 3000 UAH to 3500 UAH. In this case, the product will be searched with the article number 100345. And for a product with this article, the price will change by 3500 UAH. Since there are two products with this article in the database, the price will change for both the first and the second.

As a result, “Nike Sneakers” and “Lenovo Smartphone” with the article number 100345 will cost 3500 UAH.

In this module, this drawback is eliminated by introducing the field “Supplier code”. Each product will be assigned a code, which is formed of two parts “Supplier Code + Article”. As a result, the search for the product in the database to update the price will be carried out not by the article of the goods, but by “Supplier code + Article”.

To do this, enter the vendor code in the “Code” field:

• And in the “Processing Type” field, select "By supplier code + article number":
• Provider. Mandatory field for adding a vendor name. For example, we introduce “Provider 1”:
• Sheet. Mandatory field for specifying the sheet number in the price list, in which the prices of goods are indicated. Numbered from 1.
• Amount. The field is intended to indicate the column number in the price, in which the quantity of goods is indicated. Numbered from 1.
• Summarize the amount. If you need to add the quantity of goods from the price to the current quantity of goods, select “Enabled” in this field. If you want to rewrite the quantity of goods to the quantity of goods from the price, then select “Disabled” in this field.
• Price. Mandatory field designed to indicate the column number in the price, in which the price of the goods is indicated. Numbered from 1.
• Purchase price. The field is intended to indicate the column number in the price, in which the purchase price of the goods is indicated. Numbered from 1.
• Barcode. A field intended to indicate the column number in the price, in which the product barcode is indicated. Numbered from 1.
• Vendor code. Mandatory field, intended to indicate the column number in the price, in which the article number is indicated. Numbered from 1.
• Manufacturer. Mandatory field, intended to indicate the column number in the price, in which the manufacturer of the goods is indicated. Numbered from 1.
• Type of processing. This field is used to select the method of searching for goods in the database to update the price. If different suppliers can use the same article number for different products, in this field select the value “By supplier code + article number”.

To search for a product in the database to update the price by article, in this field select the value “By article”.

To search for a product in the database to update the price by product barcode, in this field select the value “By barcode”.

• Connected options. This field must be filled in if the price of the goods depends on a combination of options, for example, color + size. In this case, you need to create the appropriate combination in the settings of the "Connected Options" module and specify it in the list here.

For example, we introduce:

10 = Rings; weight-size; 3.4

Such a record means that if you have the value “Rings” in the 10th column of the price, then the Weight-Size connected option is used for this line, and the Weight will be taken from the third column of the price, and the size from the fourth.

• The usual options. This field must be filled in if the price of the goods is presented by options, for example, by size. In this case, you need to specify for which lines this applies.

For example:

10 = Pendants; size; 3

Such a record means that if you have the value “Rings” in the 10th column of the price, then the “Size” option is used for this line, and the “Size” will be taken from the third column of the price.

• Additional fields when creating. This field must be filled in if, when creating the product, you need to write additional parameters to the database. In this case, you need to specify from which columns to take values and where to write.

For example, 2 = ean means that the value from the 2nd column of the price should be written in “EAN”.

• Additional fields when updating. This field must be filled in if, when updating the product, you need to overwrite certain parameters in the database. In this case, you need to specify from which columns to take values and where to write.

For example, 2 = ean means that the value from the 2nd column of the price should be written in “EAN”.

• Images. A field designed to indicate the column number in the price that contains images. Numbered from 1.

If there are several such columns, enter them separated by semicolons. In this case, the image from the first specified column will become the main product image (cover), and the images from the remaining columns will become additional product images.

• Update prices. If you need to update the site only the number of products, then in this field, select the value "Disabled":
• Create new products. Supplier prices may contain new products. If you need to add new products to the site, in this field, select the “Enabled” value. If you want to skip new products in the price list, then in this field select the value “Disabled”.
• The initial status for the created goods. When adding new products from the price list, you can immediately display them on the site without prior editing (that is, when adding a new product, assign it the status “Enabled”). To do this, in this field, select the “Enabled” value:

If, however, a new product needs to be edited before being displayed, select “Disabled” in this field.

• Starting category for new products. This setting is designed to select the category in which you want to place new products. This list contains all the categories available on your site.
• Filter. This field must be filled in case if it is not necessary to update all products from the suppliers file, but certain products. In this case, indicate the words by which you can determine the usefulness of the product. For example, from the price list of suppliers it is necessary to download data only for products with the names “Pendants” and “Gold Earrings”. In this case, in the “Filter” field, enter these names. Each name must be entered on a new line:
• The default values. The import file may not contain all the information characterizing the product. If when creating the product it is necessary that it has certain parameters, then these parameters can be specified in this field.

For example:

tax_class_id = 1

Such a record means that a tax class with code 1 will be set for the new product.

Link. If it is possible to download products via a link, then specify the link in this field.

Important!!! After adding all the changes, click the “Save” button:

As a result, a new file format has been added to the list of suppliers price list formats:

In addition to the settings of the suppliers' file formats, the module has general settings. In order to proceed to editing the general settings of the module, you need to go to the “Catalog” -> “Price Update” section and click on the “Settings” button:

Or go to the admin panel of the site and select the “Add-ons” -> ”Modules” section. In the list of modules that opens, select “NeoSeo Price Update” and click on the “Edit” button:

After that, the window for editing module settings will appear on the screen:

The “General” tab is used to configure the following parameters:

• Status. In order to connect this module to the store, select “Enabled” in the “Status” field:
• Field Barcode. If the article of goods is necessary for work and you need to get it from the price list of the supplier, then, accordingly, you must select the field in which this information will be recorded when updating prices from prices. In the product settings there are empty fields that are not used for work. For example: UPS, EAN, JAN, ISBN, MBN:
• This solution proposes to store the product barcode in one of these fields. To do this, specify the name of the field in which the barcode will be written in the “Barcode Field” field. For example, write the purchase price of the goods in the EAN field:

Now, when adding a supplier in the section “Catalog” -> “Price Update”, in the field “Processing Type”, it is possible to select the value “By barcode”.

In this case, when searching for a product in the database to update the price by barcode, the product barcode will be retrieved from the “EAN” field.

• Field Article. If the article of goods is necessary for work and you need to get it from the price list of the supplier, then, accordingly, you must select the field in which this information will be recorded when updating prices from prices. In this decision, it is proposed to store the article number in the “Article” field. To do this, in the “Field Article” field, specify the value “SKU”:

Now, when adding a supplier in the section “Catalog” -> “Price Update”, in the field “Processing Type”, it is possible to select the value “By article number” or “By supplier code + article number”.

In this case, when searching for a product in the database to update the price by article number or by supplier code + article number, the article number will be retrieved from the “Article” field.

• Purchase price field. In OpenCart there is no concept of “Purchase price”, that is, when creating a new product in OpenCart there is no field in which you can enter the purchase price of the product. If such a field is necessary for work and you need to get it from the price list of the supplier, then, accordingly, you must select the field in which this information will be recorded when updating prices from prices. In the product settings there are empty fields that are not used for work. For example: UPS, EAN, JAN, ISBN, MBN:

In this decision, it is proposed to store the purchase price of the goods in one of these fields. To do this, specify the name of the field in which the purchase price of the goods will be recorded in the field “Purchase price field”. For example, write the purchase price of the goods in the UPS field:

• Connection symbol. When adding a supplier in the section “Catalog” -> ”Price Update”, in the field “Processing Type”, it is possible to select the value “By supplier code + article number”.

In this case, a code will be generated for each product, consisting of two fields “Supplier Code + Article”.

Using the “Connection Symbol” field, you can specify the symbol connecting the “Supplier Code” field and the “Article” field.

For example, enter the value “_”:

In this case, the product code will look like this:

101_100345, where 101 is the code of the supplier, 100345 is the article of goods.

• The default quantity. This field is intended to indicate the default quantity of goods.
• Synonyms of zero quantity. If the product has a zero quantity, then using this field, you can specify a text that will notify that the product is missing.
• Whom to notify. This field is used to enter the email addresses of the recipients of notifications on updating prices and products on the site:

Important!!! After adding all the changes, click the “Save” button:

After saving all the necessary module settings, you can load the supplier’s price corresponding to the format created earlier. To do this, click on the “Price Update” button:

As a result, the following form will open:

On this form you must fill in the following fields:

• Format. In this field you must select the format corresponding to the price list of the supplier that you want to download. For example, select “Provider 1”:
• File with prices. Using this field you can download the price list of the supplier. This file must have the extension .xls, .xlsx or .csv. To download the price list, click on the “Select file” button and in the dialog box select the desired price:
• Coefficient. If your site works with a certain monetary currency (for example, hryvnia), and the supplier’s price lists the product prices in another currency (for example, in dollars), then using this field you can specify the coefficient that converts the supplier’s currency into the currency of your store. If, in the price list, the currency corresponding to your store is indicated, in this field specify the value of the coefficient 1:

To update prices and products from the loaded price list, click on the “Process” button:

As a result, after processing the “price1.xls” file, a new product was added with the article number “101_100345” and the purchase price “1700”, which is recorded in the “UPC” field:

UPDATE 03/01/2019

Reworked the form for adding / editing a provider for the convenience of work.

All settings are divided into main parts: General settings, Products, Balances, Prices, Categories, Manufacturers.

Added the ability to download categories from a file. But for this, the rule must be observed: “the category in the row should be one, without unnecessary information in the other columns”

In order to update product categories from a file, go to the supplier editing on the “Categories” tab and enable the “Get categories from file” option.

After enabling the option, additional settings will become available:

• Category - the number of the column in which the name of the category is indicated. Numbered from 1.
• Create categories - turn off if you need only those categories that are already in the store

Update product categories - turn off if you do not need to bind the product to the categories from the file. In this case, new products will be created without reference to categories at all

To compare the categories on the site and in the file, go to the supplier’s editing on the “Categories” tab and fill in the correspondence in the form “Matching categories on the site and in the file”.