It is rather inconvenient to manually add a large number of products
at once to a Magento installation. Inserting products one by one will
take a long time especially when you have hundreds or thousands of
products.In such cases you need an automatic way to add all those
products to your Magento online store. We will address all steps you
need to take in order to achieve a successful import.
First, access your Magento administrator backend and go to Catalog -> Manage Categories. Create all product categories you will need. You can do so by filling the form displayed below: When you fill the form with all the information you like click the Save Category button. Bear
in mind that at this point you should make note of the newly created
category ids. It would be best to save them in a simple text file as you
will need them for the import. The category ID will be displayed upon
saving the category. It is recommended to make notes as shown below If you plan to have additional attributes for the products you are importing you will need to create those via Catalog ->Attributes ->Manage Attributes -> Add new Attribute.
You can use this functionality to add all custom attributes that are
not present by default in a standard Magento installation. Note also
that you can add additional attributes later at the moment you are
creating a sample product. It is up to you whether to create the
attributes before that or at the point you are creating the first
product.
The next step is to manually add a product to your
Magento installation. You will later export this product and use it as a
template for importing the large batch. Make sure you include all
attributes you will use for the products you are going to import in the
sample product. You might want to delete all default products that might
be present as you will not need them and then create the new product
you will use as a template for the import.
Once you create the new product and save it it will appear in the products list for your Magento store. You are now ready to make the sample export that you will use as a template. In the Magento administrator area go to System -> Import/Export -> Dataflow - Profiles -> Export All Products. Under Profile Information -> Store
choose the desired store where you will be importing the products. This
should also match the store where you have previously created a sample
product. Under Data Transfer drop down menu choose Local/Remote Server. Under Data Format make sure CSV / Tab Separated is selected for type and click Save Profile. Then click export all products again and click Run Profile in Popup. This
will save a file named “export_all_products.csv” under the var/export/
directory for your Magento installation. The export success screen will
look like this and will specify the file name where the products were
exported. Using an FTP client
download this file to your local computer. The file will include
columns for each of the attributes you have defined for your products.
Open it in a spreadsheet program (MS Excel, Open Office Spreadsheet) and
add the products you would like to import. Make sure you are
copy/pasting the corresponding attributes in the correct columns. Also
here is when you will have to add the category IDs. Use the IDs from the
text file you saved earlier and put the corresponding category ID for
the products you are adding.
Once you have accomplished the above go back to the Magento administrator area and choose System -> Import/Export -> Dataflow - Profiles -> Import All Products.
Then choose Upload file and browse for the .csv file that you have
updated with the products that need to be imported. Once you have
uploaded it click Import All Products again, then Run Profile, select
the .csv file you have just uploaded from the drop down menu and click
Run Profile in Popup. A status screen will open and the products will
start importing When the import completes you will get an export success message. You
can now go to the products section of the Magento administrator backend
and check the imported products. They will be present there and
assigned to the corresponding categories with the attributes you have
added for them.
I recently had to transfer the inventory of an old Shop
Factory site into Magento 1.7. Magento offers a powerful facility to do
this via CSV using its DataFlow profiles but this is not without its
quirks and pitfalls so I thought I’d share my experiences.
Importing product data
First, you will need to know the data format required by Magento for
import. The simplest way to do this is to create a sample product in
Magento and export all products. To do this:
Go to System > Import/Export > DataFlow – Profiles
Edit the Export All Products profile and Run Profile
The exported CSV file will be placed in the /var/export folder. You
will need to FTP into your Magento hosting to retrieve the file.
Open this file in a spreadsheet application. I used OpenOffice Calc
as it allows the CSV to be saved as UTF-8 which is required by Magento.
The first row shows the product field names required by Magento – for
your first import, you will need all these fields to be present, even if
the values are blank. Copy and paste the columns of data from the CSV
exported from your old inventory into the Magento CSV. You don’t need
all the columns, if in doubt just leave them with the same values as
your sample product. The values you must have are:
sku – this is the unique product ID and is used by
Magento to allocate the data to the correct product in its database. Any
subsequent imports with the same sku will update the data. I didn’t
have unique skus from my previous inventory so I created them using the
product names converted to lowercase and with spaces replaced by
hyphens.
type – this must be set. I used “simple”.
category_ids – these will need to be mapped from
your old categories to the new categories in Magento. You can look up
the Magento category IDs in Catalog > Manage Categories by clicking
on the category – the ID will be shown at the top. If you have a lot of
categories like I did, you can export them from phpMyAdmin using the
following SQL query:
SELECT ccev.entity_id AS categoryID, cce.path, ccev.value FROM catalog_category_entity cce JOIN catalog_category_entity_varchar ccev ON ( cce.entity_id = ccev.entity_id ) GROUP BY ccev.entity_id ORDER BY path
I then placed these values in a separate spreadsheet, and manually
entered the corresponding old category ID in the next column. Now I
could use Calc’s LOOKUP function to map each old category ID to the new
one in the Magento CSV. This saved a lot of time when making changes to
category allocations, and also prevented me having to manually enter the
new category IDs for each product.
image, small_image, thumbnail – leave these blank.
It’s better to import the images separately (see below) because Magento
will not import the product if it can’t find the referenced image (for
instance if the image filename is wrong).
Once you have all your product data in the correct columns, save the
spreadsheet as a CSV, making sure to use quotes around text fields and
to save in UTF-8.
Now you can import the product data by running the Import All
Products DataFlow profile. Upload your edited CSV and run the profile.
It’s pretty slow, taking about a second a product, so now may be a good
time for a cup of tea.
Importing product images
Now that your products exist in the Magento database, you can perform a separate import to attach your images to the products.
First, FTP your product images to the /media/import folder of your
Magento installation. Magento will resize your images to the correct
dimensions on import, so just upload the best quality image you have for
each product.
Create a new spreadsheet with the following columns: sku, image.
Copy the sku column from your product data CSV.
Copy the image filenames from your old inventory export to the image column. The filenames should have the format:
/.jpg
(note the leading slash).
Save the CSV as import-product-images.csv in UTF-8.
Create a new DataFlow profile called Import Product Images with the following settings:
Entity Type: Products
Direction: Import
Data Transfer: Interactive
Type: CSV/Tab Separated
Field Mapping: sku > sku, image > image
Upload your import-product-images.csv and Run Profile.
Voila! Your products should now have images.
Notes
Magento DataFlow does not have very helpful error messages, for
example it will tell you “Image not found” but not tell you which image.
The mysterious “Product type not set” message is probably because a
text field is not enclosed in quotes.
Removing duplicate images
Every time you run a product image import, Magento copies the image
from /media/import into a subdirectory of the /media/catalog/product
folder. Annoyingly, it does not replace the image but rather adds it to
the product and creates a copy _2.jpg etc. So after
several imports, you will have several copies of each image in your
media folder and duplicate images on each product. You can remove the duplicate images from the product data with this script, and delete unused duplicate images with this extension. However, when importing the complete catalog several times, I found it simpler to:
Delete the /media/catalog/product folder.
Delete the product image references in the Magento database using the following SQL:
DELETE FROM catalog_product_entity_media_gallery
DELETE FROM catalog_product_entity_media_gallery_value
Be warned! Only do this to clear all product images and start again.
Once you have successfully imported all images, you can safely delete any images in /media/import to free up server space.
