Today I had a seemingly easy job of importing products into WordPress using a CSV.
The site I was working on had WooCommerce and a couple of premium plugins already installed, so I assumed the best.
Little did I know, I would run into trouble cross referencing import data with actual product data.
Here’s the story:
I was sent 2 CVS wth product data filled out. Product name, price, description, and image reference links were all included in the data. Everything looked good to go, so I merged data from both these sheets into one single import sheet using Excel.
I prepped the column headers using the documentation from WooCommerce, but noticed some of the headers in the import sheet weren’t listed in the WooDocumentation (Slug, MSRP, and Brand Name). So I just added custom attribute headers and saved the workbook as a CSV.
To import the data, I preferred to use the WooCoomerce add-on “Product Im/Ex” (I recently used the plugin on a large SEO overhaul). I used Im/Ex to MERGE already created product data.
Since I was now adding new products, the process was a little different, but the UI/UX looked the same.
I selected the sheet I wanted to import. I mapped the header columns for import and set any unmapped headers as custom attribute mappings.
I ran the import.
This import sheet only contained 26 products, but for some reason, the importer only found 4 and each has errors.
To sum it up, it did not work.
At this point I began scratching my head and started to move around columns in my CSV.
Maybe the title had to be first.
Maybe any unmapped headers had to be removed.
Maybe the plugin doesn’t work with new products.
I made various changes and tried 7 variations of the sheet before it all worked as planned.
THE ISSUE: Microsoft Excel Formatting.
Apparently the sheet I was sent had formatting issues. This could have happened directly from the person sending me the sheet, or via my Mac’s Excel app. Who knows?
I copy and pasted the data into a Google Spreadsheet and pulled the sheet back down as a CSV.
Once I imported this CSV, all of the column headers were picked up as usual and all 26 products were imported successfully as drafts.
Each of these products appeared as FREE products and had long ugly URL slugs. They also didn’t have the MSRP price that was on the Spreadsheet and I once again was left scratching my head.
Basically I had to remove all “$” symbols from the price signs and reimport the sheet in order for the process to appear correctly. But instead of importing as new products, I had to merge these products using the SKU (You can only merge products with a product/post ID or SKU number with the WP All Import plugin).
After merging this sheet again, all of the products looked great. Each was categorized correctly, had updated prices, product pictures, and descriptions. Now I just need to find a way to import the MSRP price and update the product slugs.
After 20 minutes of trial and error imports using the product Im/Ex plugin, I noticed it wasn’t getting the job done.
In order to merge custom fields data, I was going to need to use my favorite import plugin: WP All Import.
I fired this bad boy up, mapped the custom data, made sure to choose UPDATE ONLY THE SELECTED DATA options and reached the final step before discovering another problem.
In order to merge that data, I would need the product/post IDs.
*Note- I am using the free version of the plugin. There is a paid WooCommerce add-on that probably makes this process easier.
In order to find the product IDs, I needed to filter out the products I just imported from all 4000+ on the site.
So I opened up Store Export plugin (I have the premium version which allows me to export Brands, MSRP, and other Custom Field options, like SEO data)
Using the filtering options on Store Export, I was able to filter out:
1. all products from the specific brands I just imported
2. All products with a published status of DRAFT
This exported maybe 31 products, that included the product ID and all of the attributes that were linked to that product.
I was able to remove the extra products (I didn’t need) and save this export sheet for the next step in my product import chain.
Using the data from this product export, I created a new row on my original import sheet called “ID.”
I used a simple VLookup formula that searched for the product’s name, and generated the corresponding Product ID number.
Once everything was good to go, I saved this workbook as a CSV and went back to WP All Import.
In the import settings, I updated my selected sheet and kept the previous slug and (MSRP) custom field mappings I previously set up. Made sure that UPDATE THIS DATA ONLY option was selected and ran the import. All 26 products were successfully updated.
I took a look at the new drafts,
All the data from the original import sheet was now migrated over as products and my work was done.
This process took me about 1.5 hours to complete and was my first time running into the error.
I probably could’ve saved time by purchasing the WP Import WooCommerce Add-On Plugin, but the good ol’ fashion way never hurts.