How to Use Magmi for Easy Magento Catalog Management & Imports

Magento is a powerful eCommerce platform, however sometimes the default admin interface leaves much to be desired. Magmi, or Magento Mass Importer, is a tool that fills this gap. Read on to learn how it can be leveraged to make complex magento catalog imports simple.

We often get approached to create and manage catalogs of a thousand plus items with complex bundled and configurable products (sometimes even bundles consisting of configurables.) Manually creating these items by clicking the “Add New” button in the manage screen is not something we would wish on our biggest competitor. Sure, there’s Dataflow, but what about when that’s not enough? In this article we are going to explore a tool that will make your life much easier as a Magento Developer; Magmi.

Introduction to Magmi (Magento Mass Importer)

If you’ve read anything on using Magmi then you may know what a powerful tool it is to be able to quickly import complex products based on a spreadsheet. However we at Morroni Technologies have found the surrounding documentation to be spotty, and some aspects of it cumbersome. The best resource so far is UnderstandingE with its great articles. The Magmi Wiki is good for reference, but not for a complete picture of how Magmi can be utilized.

In case you haven’t done it already, install Magmi. We highly recommend that you set up a htpasswdfor this directory.

I always have trouble finding a template for using Magmi. What columns are needed at the minimum? Here’s a good starting point:

sku, name, price, qty, categories, url_key, type, grouped_skus, cs_skus, us_skus, short_description, description, image, small_image, thumbnail

And we will go over these columns in detail:

  1. sku The SKU of the product. This is the only required field that must always be present in every spreadsheet. This is how Magmi references products. If the SKU does not already exist, it will be created. Even though this is the only required field, make sure you fill out more information that just this column, else you’ll end up with empty products.
  2. name The name of the product as it will appear on the frontend
  3. price The price of the product without a dollar sign. E.g. 24.95 for $24.95
  4. qty The amount of inventory you have of the product. Leave blank for unlimited. Using a spreadsheet with just the sku and qty columns is a good way to update the inventory of your Magento store.
  5. categories This column is the first one so far that is a little tricky, since an item can be in multiple categories. Say you have two categories such as Electronics > Phones and Deals > Lightning Deals and want to put a product in both categories, you would enter this into the categories column: Electronics/Phones;;Deals/Lightning Deals. Notice the two semi-colons separating the multiple categories. If you had only one category for this item, the semi-colons should not be used.
  6. url_key The URL of this item. This should not contain spaces. Be sure to fill this column or you may end up messing up your homepage!
  7. type This can be ‘simple’, ‘grouped’, ‘bundle’, or ‘downloadable’. If your Magento store has other types defined, you can use those too.
  8. grouped_skus If you chose ‘grouped’ in the type column then this column should be filled out. It will contain a comma separated list of SKUS to put in the group/bundle. Note that in your spreadsheet, you should have the simple products defined before the parent one with this column so they will be created by the time Magmi gets to the group/bundle. If your child products all start with the same SKU as the parent (e.g. SKU001 for the parent and SKU001-single and SKU001-double) then Magmi is actually smart enough to figure this out and this column can be omitted.
  9. cs_skus Cross Sell SKUS as a comma separated list of products.
  10. us_skus Up Sell SKUS as a comma separated list of products.
  11. short_description The short description of the product.
  12. description The full description of the product. This can be full of HTML codes, so it can be VERY unwieldy to type this in to a cell of a spreadsheet. You will see how we avoid this further down in the article.
  13. image The path of the image relative to httpdocs/media/import directory. I would suggest placing your image at httpdocs/media/import/SKU/image.png
  14. small_image The path of the image relative to the same directory as above, but for the small image on category pages.
  15. thumbnail The path of the image relative to the same directory as above, but for the thumbnail image.

These columns will give you a good start for defining your entire catalog in a spreadsheet. Of course you may have custom attributes that you also need defined. You’ll see how to do that a little later.

Configuring Magmi

Before we can import, we need to configure Magmi a little. There are many plugins available, and all of the ones that we will use come bundled with Magmi already. Open the Magmi Web interface by going to http://example.com/magmi/web/magmi.php.

magmi-2magmi

Database connection

Under Configure Global Parameters * enter your database credentials and click *Save global parameters.

Auto-indexing

Scroll down to the Configure Current Profile (Default) section. Scroll down until you see Magmi Magento Reindexer. Check the box, hit the configure button and check all the index option boxes. Click Save Profile (Default)

On The Fly Category Creation

Check the On the fly category creator/importer box and click configure. The default options will work for us, but if you want to, you can change Assign product to to “last category of each branch” if you don’t want products added in all the intermediary categories. Click Save Profile (Default)

Image attributes processor

Make sure to check this box to allow image importing. We will keep the existing configuration, but the Magmi Wiki has a good page explaining the various options here. Click Save Profile (Default)

Grouped Item processor

Check this box to allow importing of grouped product types. We do not need to configure anything here, but if you wanted to, you could disable the auto-matching of simple SKUs discussed previously in the grouped_skus column. Click Save Profile (Default)

Cross/Upsell Importer

Check this box to allow cross/upsell relations. There are no configurable parameters. Click Save Profile (Default)

Doing the actual import

First, download the example spreadsheet and corresponding images. Here’s what the CSV file looks like:

sku,name,price,qty,categories,url_key,type,grouped_skus,cs_skus,us_skus,short_description,description,image,small_image,thumbnail
SKU001-H,"Learning Magento Hardcopy book",20,500,"Books/Hardcopy Books;;Books/eCommerce",learning_magento_hardcopy,simple,,"SKU002,SKU003",,"This hardcopy book teaches you the fundamentals of building an eCommerce store with Magento","Chapters include<hr/><ol><li>Installing Magento</li><li>Configuring Magento</li>...</ol>",SKU001-H/image.png,SKU001-H/image.png,SKU001-H/image.png
SKU001-D,"Learning Magento ebook",15,,"Books/Digital Books;;Books/eCommerce",learning_magento_digital,simple,,"SKU002,SKU003",SKU001-H,"This digital book teaches you the fundamentals of building an eCommerce store with Magento","Chapters include<hr/><ol><li>Installing Magento</li><li>Configuring Magento</li>...</ol>",SKU001-D/image.png,SKU001-D/image.png,SKU001-D/image.png
SKU001,"Learning Magento Book",,,Books/eCommerce,learning_magento_book,grouped,,"SKU002,SKU003",,"This book teaches you the fundamentals of building an eCommerce store with Magento","Chapters include<hr/><ol><li>Installing Magento</li><li>Configuring Magento</li>...</ol>",SKU001/image.png,SKU001/image.png,SKU001/image.png
SKU002,"Magento Training DVDs",30,200,"Digital Media/DVDs;;Digital Media/eCommerce",magento_training_dvds,simple,,"SKU001-H,SKU003",,"This DVD set is great for training clients on using Magento","Videos include<hr/><ol><li>Navigating the admin interface</li><li>Completing orders</li>...</ol>",SKU002/image.png,SKU002/image.png,SKU002/image.png
SKU003,"The Importance of eCommerce Audio Book",25,,"Digital Media/Audio Books;;Digital Media/eCommerce",ecommerce_audio_books,simple,,"SKU001,SKU002",,"This audio book is great for listening on why eCommerce can help your business grow","Chapters include<hr/><ol><li>Why eCommerce</li><li>Comparison of eCommerce Platforms</li>...</ol>",SKU003/image.png,SKU003/image.png,SKU003/image.png
SKU004,"All Magento Training Material",,,Deals,all_magento,grouped,"SKU001-H,SKU001-D,SKU002,SKU003",,,"All Magento products in one screen","Buy Learning Magento, Magento Training DVDs and The Importance of eCommerce Audio Book in one place!",SKU004/image.png,SKU004/image.png,SKU004/image.png

Let’s go through the import process for these sample products. Place the csv file in httpdocs/var/import/ and the images in httpdocs/media/import/. Navigate back to the Magmi web interface. Make sure to select create new items & update existing ones from the using mode: drop down, then click Run Import.

import-magmi

When you run this, you’ll see some warnings:

SKU SKU001-H - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU002
SKU SKU001-H - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU003
SKU SKU001-D - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU002
SKU SKU001-D - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU003
SKU SKU001 - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU002
SKU SKU001 - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU003
SKU SKU002 - Cross/Upsell Importer v1.0.3 - Unknown related sku SKU003

This is because those SKUs didn’t exist yet. At the bottom of the screen, click Back to Configuration Page and run the process again. This time you will not see the warnings. Now, go to your store’s front-end and you will see the new product!

product

More Columns

Advanced Columns

Here are other ones that we often use, but aren’t always necessary

out_of_stock, manage_stock, links, tax_class_id, visibility,  meta_keywords, meta_description
  1. out_of_stock This should be 0 or 1 depending on if this item is in stock currently or not. Note that Magento will mark an item as out of stock if the qty is 0, so this column may be redundant for your purpose. Out of stock products will still show on the front-end, but will not be orderable.
  2. manage_stock This should be 0 or 1 depending on if you want Magento to manage stock for this item. For example with digital products, you do not want Magento to keep track of stock.
  3. links This column contains serialized data for downloadable products. See this page for more info on it. This column is a another prime example of a column we can automatically generate.
  4. tax_class_id Enter ‘Taxable’ or ‘Non-Taxable’ (without the single quotes) depending on if you want tax applied to this column.
  5. visibility Enter 1 for ‘Not Visible Individually’, 2 for ‘Catalog’, 3 for ‘Search’ and 4 for ‘Catalog, Search’. How are these values determined? You’ll find out in the next section.
  6. meta_keyword A comma separated list of meta keywords that will appear in the HTML meta tag. You can also configure this to be used as search terms.
  7. meta_description A short description that will appear in the HTML meta tag.

Custom Columns

We haven’t touched on defining your own custom attributes here. If you guessed that you use the attribute identifier as the column name, you’ve guessed right. But what if it’s a custom attribute created by a module, or any other one native Magento one that we have not covered? We can easily figure out what to use for any attribute that is found in the Manage Products screen. First, load up any product. You should be familiar with the inspect element feature of your browser.

How did we figure out that visibility needed to be a number and what those numbers corresponded to? Right click on the drop-down select element and hit inspect element. You should see the following. Notice the value 1 corresponds to ‘Not Visible Individually.

visibility

Secondly, let’s look at a custom attribute that either you or a module may have defined. In this case, the gauge of wire-stock. Again, right click on the form element (this time a text box) and you should see something like this:

guage

The form element’s name attribute will help us figure out what to name the column header. In this case it’s ‘product[gauge]’ so the column header will be ‘gauge’. Since this is a text box and not a select, there are no pre-defined options and the column may contain any value.

Making Magmi even easier

Splitting up spreadsheets

You may be thinking already that this spreadsheet has too many columns and is unmanageable. At Morroni, we tend to split these things up in to multiple spreadsheets. This is no problem, just be sure to include the sku column in each sheet. This is especially helpful when you want to have clients be able to modify only certain aspects of the data.

Using the filesystem

This next trick is going to make your life easier and open the possibilities. When we started using the filesystem with Magmi, we knew we were on to something. With some simple code, we can auto generate the columns that don’t make sense to manage in Excel

Images

One example is that we do not like to have to enter the filename for image, small_image, thumbnail. Instead, we can use this PHP script to loop through the httpdocs/media/import folder and generate the spreadsheet for ourselves. Let’s use the structure of httpdocs/media/import/SKU/image.png

<?php
$httpdocs = '/srv/www/httpdocs';
$fp = fopen("$httpdocs/var/import/images.csv", 'w');

fputcsv($fp, array('sku', 'image', 'small_image', 'thumbnail'));

foreach ($files = glob($httpdocs.'/media/import/*/') as $dir) {
  if(!is_dir($dir)) continue;

  // This line will pull the directory name which should be our SKU
  $sku = array_reverse(explode('/',$dir))[1];
            
  $cover = $dir.'image.png';
  fputcsv($fp, array($sku, $cover, $cover, $cover));

}

Place this code anywhere on your server and run it. You should then see a file named ‘images.csv’ available for import with Magmi.

Downloadable Products

Remember the links columns? It can be a pain to manually create. In this snippet we will auto detect the downloadable file. This example is simple, but we could expand it to read in values for the is_sharable and number_of_downloads variables. In the filesystem, create a folder named ‘downloads’ for each SKU that will have downloadable files. For example, httpdocs/media/import/SKU001-D/downloads/learning-magento.pdf

<?php
$httpdocs = '/srv/www/httpdocs';
$fp = fopen("$httpdocs/var/import/downloads.csv", 'w');

fputcsv($fp, array('sku', 'type', 'links'));

foreach ($files = glob($httpdocs.'/media/import/*/') as $dir) {
  if(!is_dir($dir) || !is_dir($dir.'downloads')) continue;

  $sku = array_reverse(explode('/',$dir))[1];

  $links = array();

  foreach ($files = glob($dir.'downloads/*') as $file) {
    // get the filename and stylize it
    $title = ucwords(str_replace(array('-','_'), ' ', pathinfo($file)['filename']));
    $links[] = "file:$file,sort_order:0,title:$title,sample:,is_shareable:0,number_of_downloads:2";
  }
  $links = implode(';',$links);
    
  fputcsv($fp, array($sku, 'downloadable', $links));

}

Descriptions

Putting the description column in a spreadsheet is a huge pain because it’s not a good way to edit HTML. Let’s allow the use of description.html, short_description.txt, meta_description.txt and meta_keywords.txt in httpdocs/media/import/SKU/.

<?php
$httpdocs = '/srv/www/httpdocs';
$fp = fopen("$httpdocs/var/import/descriptions.csv", 'w');

fputcsv($fp, array('sku', 'description', 'short_description', 'meta_keyword', 'meta_description'));

foreach ($files = glob($httpdocs.'/media/import/*/') as $dir) {
  if(!is_dir($dir)) continue;

  $sku = array_reverse(explode('/',$dir))[1];
            
  $desc = $short_desc = $meta_desc = $meta_keywords = '';

  if(file_exists($dir.'description.html'))
    $desc = file_get_contents($dir.'description.html');

  if(file_exists($dir.'short_description.txt'))
    $short_desc = file_get_contents($dir.'short_description.txt');

  if(file_exists($dir.'meta_description.txt'))
    $meta_desc = file_get_contents($dir.'meta_description.txt');

  if(file_exists($dir.'meta_keywords.txt'))
    $meta_keywords = file_get_contents($dir.'meta_keywords.txt');

  
  fputcsv($fp, array($sku, $desc, $short_desc, $meta_keywords, $meta_desc));

}

Auto Importing

Now that we have multiple spreadsheets, it may be a hassle to import them with Magmi through the web interface. This last bit of code will use Magmi’s CLI to import all files in httpdocs/var/import/

<?php
$httpdocs = '/srv/www/httpdocs';
foreach ($files = glob($httpdocs.'/var/import/*.csv') as $filename) {
  $magmi_cli = "php $httpdocs/magmi/cli/magmi.cli.php -mode=create -CSV:filename=\"$filename\"";
  $return_var = 0;
  system($magmi_cli, $return_var);
  echo $filename . (!$return_var?'[OK]':'[ERROR]')."\n";
}

Conclusion

These are some ways that we use Magmi to make our life easier. The code you have seen was the start of what we call the Auto-Importer, which is a custom Magento module that allows our clients to easily import products, with the guarantee that everything will work properly. We hope that you have found some useful information here, and that it inspires you to think outside the box.

Leave a Reply

Skip to toolbar