Migrating data from one platform to another can feel overwhelming, especially when moving from a specialized tool like Part-DB to WooCommerce. However, with careful planning, a solid grasp of SQL, and the right tools, the process is straightforward. My recent experience migrating my inventory taught me that what might seem complex at first is entirely achievable with the right approach.
Part-DB is excellent for internal inventory management but lacks the tools to make your data publicly accessible. WooCommerce, on the other hand, is a robust e-commerce platform designed to display products online, manage stock, and facilitate sales. By moving my inventory to WooCommerce, I was able to make my data accessible on my website, CollierComputers.com. Here’s how I accomplished the migration.
Why Migrate from Part-DB to WooCommerce
While Part-DB excels at organizing inventory for internal use, it doesn’t offer a public-facing interface. As my website, CollierComputers.com, began to take shape, it became clear that WooCommerce was a better choice for showcasing my inventory to the world. Its flexibility, ease of use, and e-commerce features made it the obvious next step.
The migration wasn’t just about moving data; it was about making my inventory available to visitors, giving them a way to explore and engage with the collection I’ve been building over the years.
Preparing for the Migration
To start the process, I had to bring my Part-DB server back online. After months of being powered down, it needed a little attention. Once it was running, I transferred the SQLite database containing all my inventory data to my local machine.
With the database file in hand, I installed DB Browser for SQLite. This tool provided an easy way to analyze the database and identify the fields I needed for WooCommerce. This preparation stage was essential to ensure the migration would go smoothly.
Mapping Data for WooCommerce
To successfully migrate the data, I needed to understand WooCommerce’s product structure. I began by exporting two products I had already added to WooCommerce to study the required fields. These exports served as a template, helping me identify critical fields like product name, price, and stock quantity.
Using the exported data as a guide, I mapped the fields in Part-DB to those required by WooCommerce. This step was crucial to ensure the imported data would display correctly on my site.
Writing the SQL Query
The next step was creating an SQL query to extract the data from the Part-DB database. Since Part-DB’s structure didn’t directly align with WooCommerce’s import format, this required some trial and error. My focus was on pulling key information like product names, prices, and stock quantities while ensuring consistency across all 176 products.
Crafting the query was time-intensive but rewarding. By the end, I had a properly formatted dataset ready for import into WooCommerce. See the code block below for an example of the SQL code I used for my needs:
WITH RankedAttributes AS ( SELECT DISTINCT p.name AS "Name", p.description AS "Short Description", CASE WHEN c.name = 'Computers' THEN c.name ELSE 'Computers > ' || c.name END AS "Categories", pa.name AS "Attribute Name", pa.value_text AS "Attribute Value", p.manufacturer_product_number AS "Manufacturer Product Number", ROW_NUMBER() OVER (PARTITION BY p.name ORDER BY pa.name) AS row_num, CASE WHEN a.path LIKE '%/part/%' THEN REPLACE(a.path, '%MEDIA%', 'https://example.com/media') ELSE NULL END AS "Full Image URL" FROM parts p INNER JOIN categories c ON c.ID = p.id_category LEFT JOIN attachments a ON a.id = p.id_preview_attachment LEFT JOIN parameters pa ON a.element_id = pa.element_id
),
AllImages AS ( SELECT DISTINCT Name, "Full Image URL" FROM RankedAttributes WHERE "Full Image URL" IS NOT NULL
),
ConcatenatedImages AS ( SELECT Name, GROUP_CONCAT("Full Image URL", ', ') AS "Images" FROM AllImages GROUP BY Name
),
UniqueSKUs AS ( SELECT DISTINCT Name, LOWER(REPLACE(Name, ' ', '-')) || '-' || CAST(DENSE_RANK() OVER (ORDER BY Name) AS VARCHAR) AS SKU FROM parts
),
PartStock AS ( SELECT p.name AS "Name", CAST(SUM(pl.amount) AS INT) AS "Stock" FROM parts p LEFT JOIN part_lots pl ON p.id = pl.id_part GROUP BY p.name
),
PurchaseNotes AS ( SELECT p.name AS "Name", GROUP_CONCAT( CASE WHEN pd.price IS NOT NULL THEN 'Purchase Price: $' || CAST(pd.price AS TEXT) || COALESCE(', Supplier Part Number: ' || od.supplierpartnr, '') || COALESCE(', Supplier URL: ' || od.supplier_product_url, '') ELSE NULL END, '; ' ) AS "Purchase Note" FROM parts p LEFT JOIN pricedetails pd ON p.id = pd.orderdetails_id LEFT JOIN orderdetails od ON p.id = od.part_id GROUP BY p.name
)
SELECT DISTINCT ra.Name, ra."Short Description", ra.Categories, GROUP_CONCAT(ra.Tags, ', ') AS "Tags", ra."Manufacturer Product Number", us.SKU, ci."Images", ps."Stock", pn."Purchase Note", MAX(CASE WHEN row_num = 1 THEN "Attribute Name" END) AS "Attribute 1 name", MAX(CASE WHEN row_num = 1 THEN "Attribute Value" END) AS "Attribute 1 value(s)", MAX(CASE WHEN row_num = 1 THEN 1 ELSE NULL END) AS "Attribute 1 visible", MAX(CASE WHEN row_num = 1 THEN 0 ELSE NULL END) AS "Attribute 1 global" -- Add additional attributes as needed
FROM RankedAttributes ra
LEFT JOIN ConcatenatedImages ci ON ra.Name = ci.Name
LEFT JOIN UniqueSKUs us ON ra.Name = us.Name
LEFT JOIN PartStock ps ON ra.Name = ps.Name
LEFT JOIN PurchaseNotes pn ON ra.Name = pn.Name
GROUP BY ra.Name, ra."Short Description", ra.Categories, ra."Manufacturer Product Number", us.SKU, ci."Images", ps."Stock", pn."Purchase Note";Importing Data to WooCommerce
With the data prepared, I used WooCommerce’s built-in import tool to upload the products. This step was surprisingly smooth, though the upload process took longer than expected due to the number of products being imported. Watching the progress bar move slowly was a test of patience, but it was worth it when I saw the products appear on CollierComputers.com.
After the upload, I spent time verifying the data on the site. Ensuring the accuracy of each product listing was a meticulous but necessary step to ensure the migration’s success.
Lessons Learned
This migration reaffirmed the importance of planning, preparation, and understanding the tools at hand. Breaking the process into manageable steps made it much easier to navigate. SQL knowledge was indispensable for extracting and preparing the data efficiently.
By moving to WooCommerce, I not only improved the way I manage inventory but also made my collection accessible to visitors. The ability to display my inventory online opens new possibilities for sharing and engaging with my audience.
Final Thoughts
Migrating from Part-DB to WooCommerce was a game-changer for my workflow and my website. The process required effort and patience, but the result—a fully accessible, public-facing inventory—is well worth it. For anyone considering a similar migration, I encourage you to take the leap. With the right preparation and tools, it’s entirely within reach.