22. Input Missing Data using Expressions During Import

Managing your online shop, the usual way of getting information about the products is via cooperation with one or multiple suppliers. Providers supply you with the file that contains the information about new products and up-to-date data already listed at your store entities. Usually, the suppliers don’t have any idea which exactly product data and columns you need to have in order for data upload to be performed successfully. Thus, in such and similar situations you need to do certain file adjustments by means of expressions.

Expressions are specific formulae that allow modifying data without having to manually input changes.

More details on the essence of the notion of “expression” you can find here


What are typical expressions and when should one use them?

Let's have a closer look at cases when you might need to input a definite expression during bulk items update and what exactly the expression should be:


  • No column or values in it that are responsible for product visibility at the front-end

You may say that is not a very big deal if some of the product info is not in the file. You are right, but not when it comes to the data which are responsible for product visibility at the front-end. For example, if you do not have in your file values indicated for status, visibility, quantity, or stock availability of a product, then most likely your product will not be available for view at the front-end.


Imagine that you do not have in your file indicated values for either status or stock availability. By means of expression, you can add the values for this column on the fly and make your products available in stock and their status enabled. For making products set to in stock and enabled by status, put 1 enclosed in (‘) marks an on the screen-shot below -

If you want to make your products disabled or out of stock, put ‘0’.


Product visibility you can set to options: nowhere, catalog, search, and catalog&search. In order for your product to be visible and searchable at the front-end, in your file indicate value 4 (it corresponds to catalog, search option) or during import, in front of visibility column in the expression field put ‘4’.


In order to make a Magento product update of the quantity field, you can set the value for desired quantity, for example, ‘100’, but, please, keep in mind that in this way you will set the same quantity for all the products you import. If you need to set different quantities automatically, it can be done, but there should be a certain logic of quantity change. For example - in case your products belong to a certain category, set for all quantity 10, the expression will be the following:


IF([CSV_COL(10)]=’Name of category’, ‘10’, ‘0’);


Where 10 is the number of categories’ .csv column from your file. You need to input this expression in the quantity database field in the left pane.


There might also be some specific owners’ or developers' needs concerning inventory updates. For example, you may need to add the quantity offered by your supplier to the product quantity that is already listed in your database. To arrange that, the following formula is needed:

[FIELD_VALUE]+[CSV_COL(INDEX)]


  • You need to merge some columns

In case you have a multi-leveled category tree, the usual is the situation when you get the import file where the main category and sub-categories are placed in separate columns. For the efficient procedure of Magento import via Store Manager for Magento, it should be indicated category path with main categories and subcategories in one column.

The following expression will help you to merge .csv columns:


CONCAT([CSV_COL(INDEX1)],'|',[CSV_COL(INDEX2)],'|',[CSV_COL(INDEX3)])


where

INDEX1, INDEX2, INDEX3 are the numbers of .csv columns where your categories and subcategories are placed and the pipe symbol (|) serves as a separator.


  • Set mark-up or increase the value indicated in the file/database

Just imagine that your supplier provided you “fresh” product spreadsheet with the price indicated there. You would like to import that file, but sell your products for a higher price to make some profit. Using expression it is possible to make bulk price modifications and increase the price from that indicated in the file by some percent.

To do that you should use the formula:


[CSV_COL(INDEX)]*1.15


where INDEX is the number of your .csv column where the price is indicated and 1.15 means that the price should be increased by 15%.

There can be another situation. All we know, before the holidays, in order to attract more buyers, the owners can do a Magento bulk price update, decreasing the price of the existing products. So to lower the price, you can export your products and then import them back indicate the following expression:


[CSV_COL(INDEX)]*0.85


Sometimes merchants have specific requirements for their pricing. For instance, you may need to change or not change the price depending on some fact. Lets say, during the initial upload you need to change the product price in the case in your file the bigger price value is indicated, but if the value for price in the file is lower than the existing product price, leave it “as is”. The following expression will be useful for you:


IF([FIELD_VALUE]>[CSV_COL(INDEX)], [FIELD_VALUE], [CSV_COL(INDEX)])


  • Replace or remove some values

Suppose that you have a file with 10 000 products and in each product description you have the company name “ABC-company”.

If you would like to have the company name removed, use the following formula in the process of Magento import:


REPLACE([CSV_COL(INDEX)], "ABC-company", "")


If you would like the company name to be replated on some other text or value, you can input:


REPLACE([CSV_COL(39)], "ABC-company", " New name")


  • Complex changes on the basis of definite logic or data placement regularity

There might be the need to place values to correct places depending on some other value range during the upload process. For example, you need to place products in the definite categories on the basis of product price - if the price of the product is less than $50, place it in Category 1, if the product price is from $50 to $100, list it in Category 2, etc. In this case, you will need this expression:


CASE

WHEN [CSV_COL(5)]<50 THEN 'Category tree 1'

WHEN [CSV_COL(5)]>=50 AND [CSV_COL(5)]<100 THEN 'Category 2'

WHEN [CSV_COL(5)]>=100 and [CSV_COL(5)]<200 THEN 'Category 3'

WHEN [CSV_COL(5)]>=200 THEN 'Category tree 4'

ELSE 'Some other Category'

END


Moreover, really often owners or developers have categories named differently in their stores, thуn category names their suppliers provide. How to avoid manual adjusting of categories names in the file? Easily, with the following expression rule -


CASE

WHEN [CSV_COL(5)]='Category Name 1' THEN 'Your Category Name 1'

WHEN [CSV_COL(5)]='Category Name 2' THEN 'Your Category Name 2'

WHEN [CSV_COL(5)]='Category Name 3' THEN 'Your Category Name 3'

ELSE 'Some other Category'

END


Where Category Name 1, 2, 3 are categories from your .csv fileб and Your Category Name 1, 2, 3 are category names (or path) at your store. The last condition for some other Category is used to move all products that for some reason don't match any of the above rules. For example, if your supplier adds a new category. It is possible to create a new category like "What's new" - actually, it is a category for you to watch for new products that are not mapped correctly and serve as a signal to update expression.


To conclude, using expression rules you can particularly change all the available in the list fields and values. Basically, you can forget about having to make changes manually, since if you have at your disposal the necessary formula, Magento product update and data modification will make your eCommerce life much more relaxed.