cobby Tutorial Import Prices Magento

How To Easily and Quickly Update Prices in Magento

You would like to update multiple product prices in a Magento store? To do this manually for each product is very time consuming and cumbersome. Mass processing would be the faster way here.
In Magento 1 or Magento 2, bulk editing usually works by importing and exporting csv files. Especially if your store has a very large product catalog, it is much more time-saving and clearer to export the data in a spreadsheet program like Excel, update the prices and then import them back into the store. However, importing and exporting data to and from Magento requires a deeper understanding of the store data structure and the CSV file format.

Sounds complicated? It is indeed. That’s why cobby offers you a simpler and faster solution for updating product prices in Magento. Watch the video or the step-by-step tutorial below to learn more.

Video: How to bulk update prices with cobby in Magento

Scenario

You sell different items from different suppliers through a Magento online store. Your supplier for bags sends you a price list with new prices. You would like to transfer these purchase prices to your Magento shop with a certain price surcharge (sales prices).

Step by step

You need Excel and the supplier list with the new prices (in digital form).

  1. Open files and load data

    First open your supplier list with Excel.
    Then click on cobby (1) → Load products (2).
    The product data from your Magento store will now be loaded into new spreadsheets. The supplier list remains in a separate sheet in Excel.

    Supplier List with cobby

  2. Add new columns for the price matching

    First you create another column.
    To do this, first select the correct product category via the spreadsheets: In this case “Bag”, because only the prices for bags should be updated.
    Click with the right mouse button to the right of the price column (1).
    Add a new column via “Insert” (2).
    The new column “Column1” is created (3).

    Insert column

    The column serves only as a temporary addition and is not taken into account when saving in the Magento backend.
    Please also note that Excel uses existing filters from the previous column. This can lead to errors when creating formulas. You can find more information about this and detailed instructions on how to disable it here: Deaktivieren der Datenüberprüfung für Excel Zellen

  3. Initiate formula for price matching (INDEX)

    In the newly created column, enter the beginning of the formula for the INDEX =INDEX( .
    Insert Excel formula INDEX

    The Excel formula INDEX is composed of four parts:
    1. What is to be matched?
    In our example New price from the supplier list: NewPrices → Column C
    2. What should be matched in the “old” list?
    Here it is the SKU, because this is the same in both lists and is unique, i.e. from the sheet Bag → Column SKU
    3. Which section is it in the “new” list?
    Here it is the SKU in the supplier list: NewPrices → Column A
    4. At the end the “0” still follows for the exact price
    Formula: =INDEX(NewPrices!C:C;MATCH([@SKU];NewPrices!A:A; 0))

  4. First formula part: select section which should be adjusted

    While you are still in the formula input, click into the spreadsheet with the supplier list NewPrices (1), select the column header “C” (2) and close this first part of the formula with a semicolon ; (3).

    Insert Excel formula Index

  5. Second formula part: select matching value in existing list

    Now switch to the spreadsheet Bag, write “MATCH” after the semicolon (click on a cell in the SKU column and close this part again with a semicolon ( ; ).
    The SKU must be in the same line as the newly created formula (in this example line 3).

    Insert Excel formula INDEX

  6. Third formula part: select matching value in the new list

    In the next step, you add the column SKU from the supplier list. In order to do this, switch back to the supplier list NewPrices, select the header column with the SKU (in this example A) and close this part with a semicolon ( ; ).

    Insert Excel formula INDEX

  7. INDEX Finalize formula and apply to the whole column

    With another semicolon ( ; ) you then indicate that an exact match must be found. The constant for this is 0.
    With two closed brackets )) you end the input of the INDEX formula. To apply the formula to the whole column and thus to each article, you can now copy and paste it or double-click on the small square at the bottom right of the cell to copy it for all cells.

    Excel copy Row

  8. Calculate new sales prices with a formula

    Since these are the purchase prices, you increase the new prices by your margin, in this case by 10% as an example.
    To do this, you must store the following function in the first cell of the original price column (1) (3). Formula: =ROUND([@Column1]*110%;0)
    With this the new price is automatically rounded.
    By dragging down via the small square at the bottom right, you copy the formula again for all cells. Your new sales price is fixed.

    cobby in Excel

  9. Transfer (Import) data to Magento

    At the end you click on “Save products” in the cobby section (no. 5 of the upper image).
    Your new prices will now be updated in real-time in your Magento store and are immediately online.

Conclusion

In order to easily and quickly update many prices in Magento, you require neither Magento knowledge nor a complicated csv import-export solution. All you need is cobby and Excel.

cobby Extension in Magento 2

How to install and set up the cobby extension in Magento 2

1. How to download the cobby extension

  • Log in to the cobby customer portal. If you haven’t registered yet, please click on the tab Registration.
  • Create your online store there. The wizard guides you through each step. Select Magento 2 as the online store system.
  • During the store setup, you can download the cobby extension for Magento 2. You can download this extension at any time after your setup under “My stores” in the customer portal.

2. Installation

Step 1 – Unzipping

The cobby extension was downloaded as a ZIP file. Unpack it locally on your computer.

Step 2 – Upload

  • Connect to the source directory of your website via SSH/SFTP/FTP client
  • Upload all files and folders from the cobby extension package to the corresponding root folder of the Magento installation (Magento root directory).
  • Use the transfer mode “Merge”. Do not replace the whole folder, but merge the data.
    This way your SSH/SFTP/FTP client will only add new files. This mode is used by most SSH/SFTP/FTP clients by default.
  • For MacOS we recommend Transmit or Cyberduck.

Step 3 – Activation

  • Connect to your Magento directory via SSH.
  • Run the following two demands:
    php bin/magento module:enable Mash2_Cobby
    php bin/magento einrichten:aktualisieren

3. Creation and Configuration

cobby authenticates itself via a user account in your Magento 2 installation. The best way to run cobby is to create a dedicated user in Magento. To do this, proceed as follows.

Step 1 – Create a Dedicated User Role for cobby

Go to “System” in the Magento backend ➞ User Roles

Magento2 Backend User Roles
System ➞ User Roles
  • Click on Add New Role.
  • Change the role name to cobby-role.
  • Enter your password under Current User Identity Verification.
  • Switch to Role Resources.
  • Select “cobby” under Resources.
  • Click on Save Role.
Magento 2 cobby Roles Resources
Role Resources ➞ cobby auswählen ➞ Save Role

Step 2 – Create a User for cobby and Assign Roles

You can access the Magento 2 user administration via “System” ➞ All Users

System ➞ All Users

Here’s how to create a user for cobby and assign it the role you created in step 1:

  • Click on “Add new user” (Neuen Benutzer hinzufügen)
  • Fill in all required fields under User Info and enter your password under Current User Identity Check. Note or memorize the user name and password, because we will need them later.
  • Switch to User Role.
  • Mark the newly created role cobby-role
  • Click on Save User.
Magento2 cobby user and role
User Role ➞ cobby-role markieren ➞ Save User

Step 3 – Configure the cobby Settings

Go to “Stores” ➞ Configuration

Magento2 Menu Stores
Stores ➞ Configuration
Magento2 Configuration Services cobby
Klicke anschließend unter Services ➞ cobby

Now fill in the general settings:

  • Licence key is already filled in with your license key for this cobby installation.
  • Contact email is filled in with the email address you registered with in the customer portal. cobby will inform you about news, updates and bugs via the entered email address.
  • User name: Enter here the user name of the user that you created in Step 2.
  • API key: the password of the user created in step 2
Magento2 cobby Settings
Allgemeinen Einstellungen ausfüllen ➞ Save Config

Step 4 – Password Settings

API keys or passwords can expire by default in Magento 2. Make sure in the settings that the password cannot expire. To manage the lifetime of passwords, do the following:

  • Go to “Stores” ➞ Configuration ➞ Extended (Admin)
  • Change the value for password duration (days) to blank in the section Security.
  • Under Password Change you have the opportunity to put the password on Recommended.
Magento2 Configuration Password-lifetime

Step 5 – Save

The last step: Click on the top right on Save Config.

cobby will now be set up and the product data of your store will be synchronized. This may take some time depending on the amount of data. You will receive an email as soon as cobby is set up.

Afterwards you will find a download link for the cobby Excel plugin in the customer portal.


Installation completed!

via GIPHY

Configure cobby extension in Magento 1

Configure cobby in Magento 1

First, install the cobby module like you’re used to, just like you install other modules in your Magento store as well.

Step 1 Open settings in the Magento backend

After you have successfully installed the cobby module, the following message “Click here to configure cobby” (1) appears in the admin area. By clicking on the message “Configure cobby“, you can directly access the cobby settings under System.

View in Magento 1 after installing the cobby module

You can also find the settings via the menu bar: “System” (1) → “Configuration” (2) → “Services” (3) → “cobby” (4)

Aufruf der cobby Einstellungen in Magento 1
You can also open the cobby settings from the menu in the Magento backend.


Step 2 Select user

In the general settings at cobby the license key and the contact email address is already stored automatically. The license key is assigned by us and the contact email address is the same one you used to register in the cobby customer portal.

cobby Allgemeine Einstellungen in Magento 1

Under “Select web service user” you have the option to create a new web service user or to select an existing one.

Neuen Webservice Nutzer erstellen
If you would like to create a new user, enter a user name, the new API key to use and a user email.
Vorhandenen Webservice Nutzer auswählen
If you would like to use an existing user, select the user name from the list and enter the API key of the selected user.

Step 3 Save configuration

With “Save Config” you save the configuration. Our service will now automatically connect to your store and synchronize the product data.
Once the synchronization is complete, we will send you an email including the next steps and the download link to the plugin for Excel.

Konfiguration von cobby in Magento 1 erfolgreich
The configuration of cobby in Magento 1 was successful.
Product
Store Systems