Migrating Magento Data: Exporting Products, Categories & Orders from Database Dump
As you’re preparing to migrate your Magento store to a custom webshop engine, you’ve encountered the challenge of working with only an MySQL dump file and no access to the live site itself. This is where our expertise at Laravel Company comes in handy.
Understanding Your Migration Scenario
You’re dealing with Magento version 1.6.0.0, which means the core_resource table has an admin_setup value of ‘default’. The key here is to extract all necessary data - products (with images and attributes), categories, and orders if possible.
Why You Want a CSV/XML Export
Exporting this data into a CSV or XML format makes it easy to manage during migration. These formats are universally readable by most systems, including your new custom webshop engine.
The Fastest Working Way: SQL Queries & PHP Scripts
The most efficient approach involves writing SQL queries directly against the MySQL dump file and then using PHP to process and export this data into CSV/XML format. Let’s break down each step:
1. Exporting Products (Including Images)
To extract product information, you can use a query like this one:
SELECT e.entity_id,
e.sku,
e.name,
e.description,
e.short_description,
e.price,
m.value as image_path
FROM `catalog_product_entity` AS e
LEFT JOIN `catalog_product_entity_media_gallery` AS g ON (g.entity_id = e.entity_id)
LEFT JOIN `catalog_product_entity_media_gallery_value` AS m ON (m.value_id = g.value_id)
WHERE e.type_id='simple'
This query joins the main product table with the media gallery tables to fetch both the product information and its image paths.
2. Exporting Categories
For categories, a simple query like this should suffice:
SELECT *
FROM `catalog_category_flat_store_1`
Note: The store_id (in this case, ‘1’) may vary depending on your specific setup.
3. Exporting Orders
Orders can be complex, but you can start with a basic query to fetch order IDs and then write PHP scripts to process each one:
SELECT *
FROM `sales_flat_order`
WHERE status!='canceled'
Processing Data in PHP
Once you have the SQL queries, use PHP to read the output, parse it, and export it into CSV/XML format. You can utilize built-in PHP functions like fopen, fgetcsv for reading the MySQL dump file, and str_getcsv or built-in XML writers for creating the final output files.
At Laravel Company, we’ve handled similar migrations where we had to extract data from various sources and transform it to fit new systems. The key is to understand your current data structure and map it to what you need in your new environment.
Conclusion
Migrating Magento data from a dump file requires careful planning, but with the right SQL queries and PHP processing scripts, it’s entirely feasible. Remember to test your scripts thoroughly on small datasets before running them against your entire production data.
The goal is to create clean, well-structured CSV or XML files that you can easily read into your new webshop engine during the migration process. If you find yourself needing professional assistance with migrations like this one, don’t hesitate to reach out - we’re here to help!