How to Input Missing Data and Modify Existing On-The-Fly by Means of Expressions in the Process of Magento 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 on 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 to modify data without having to manually input changes.
More details on the essence of the notion “expression” you can find here:
What are typical expressions and when should one use them?
Lets have a closer look at cases when you might need to input definite expression during bulk items update and what exactly expression should be:
1) No column or values in it that are responsible for product visibility at front-end
You may say that is 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 Magento product update of 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 quantity automatically, it can be done, but there should be certain logics of quantity change. For example - in case products belong to the certain category, set for them 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 update. 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:
2) You need to merge some columns
In case you have multi-leveled category tree, the usual is the situation when you get the import file where main category and sub-categories are placed in separate columns. For 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:
where INDEX1, INDEX2, INDEX3 are the numbers of .csv columns where your categories and subcategories are placed and pipe symbol (|) serves are separator.
3) Set mark-up or increase the value indicated in the file/database
Just imagine that your supplier provided you “fresh” product spreadsheet with price indicated there. You would like to import that file, but sell your products for the higher price to make some profit. Using expression it is possible to make bulk price modifications and increase price from that indicated in the file by some percent.
To do that you should use the formula:
where INDEX is the number of your .csv column where price is indicated and 1.15 means that the price should be increased by 15%.
There can be another situation. All we know that before the holidays, in order to attract more buyers, the owners can do Magento bulk price update, decreasing the price of the existing products. So to lower the price, you can export your products and then importing them back indicate the following expression -
Sometimes merchants have specific requirements to their pricing. For instance, you may need to change or not change the price depending on some fact. Lets say, during initial upload you need to change product price in case in your file the bigger price value is indicated, but if the value for price in the file is lower than 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)])
4) Replace or remove some values
Suppose that you have a file with 10 000 of products and in each product description you have company name “ABC-company”.
If you would like to have company name removed, use the following formula in the process of Magento import -
REPLACE([CSV_COL(INDEX)], "ABC-company", "")
If you would like company name to be replated on some other text or value, you can input -
REPLACE([CSV_COL(39)], "ABC-company", " New name")
5) Complex changes on the basis of definite logics or data placement regularity
There might be the need to place values to correct places depending on some other values range during upload process. For example, you need to place products to the definite categories on the basis of product price - if the price of the product is less then $50, place it to Category 1, if product price is from $50 to $100, list it in Category 2 etc. In this case, you will need this expression:
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'
Moreover, really often owners or developers have categories named differently in their stores, than category names their suppliers provide. How to avoid manual adjusting of categories names in the file? Easily, with the following expression rule -
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'
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 new category. It is possible to create 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.