A client recently requested that I write a custom program to enable staff to bulk import products into WooCommerce from a spreadsheet that they use internally.
Having written several custom spreadsheet-to-WooCommerce import programs over the years, I knew immediately that I would use the WP All Import plugin (WPAI) as the base because I am familiar with its flexibility and ability to import the most complex of set ups.
I created a staging area at a different hosting company, then migrated in an exact copy of the LIVE website. I added password protection to the public_html folder on the staging website in order to keep it hidden from uninvited eyes and the search engines.
I worked in the staging area until everything was working 100%. Once the product import from spreadsheet process was working as requested, I installed the WPAI plugin, functions, additional filters, modified functions.php file, and, CSS on the client’s LIVE website. (Pretty standard development.)
Oh, and the number of times the product import was run in the staging area was 53. Yes, 53 times! In between each run, I used another plugin to bulk delete the products.
On each run, 152 products were imported, some with no featured images, some with only a featured image, and some with a featured image and a photo gallery of 1 – 6 images. Each product was added to the WooCommerce catalog as a simple product.
Why 53 tests of the product import template?
The actual import of all products worked fine from the first test. The parts that I was testing and retesting were:
- Outcomes of different stages of the three custom functions I was programming in order to manipulate and display the incoming data
- Properly matching the products to the 55 categories and subcategories (Not as easy as you would think…See below.)
- A rewrite of some code after the LIVE website had been reorganized in the middle of this project
- Using the same import file with the addition of only 5 new products. Effectively, testing two scenarios. Testing what happens when the same products are imported multiple times and no changes have been made to their info, and testing what happens when the same products are imported multiple times and some of their data has been changed.
- What happens when expected data is missing, and tweaking the code for those occasions
- Whether the incoming categories, subcategories, manufacturers, and manufacturers’ brands where already on file, and adding them appropriately, if not
Matching product images with line items in the spreadsheet
Instead of listing the primary and supporting (product gallery) images in the spreadsheet, the client chose to apply a naming scheme to the product images, then asked me to write some PHP code to automatically match those images to the product line items in the spreadsheet.
Testing Solution:
In the staging environment, test phase 1
- I uploaded the images to the WPAI /files/ folder.
- I wrote a PHP function and saved it in the functions.php file. In the function, I loaded an array with a number of the product Titles from the client’s product spreadsheet.
- I defined a shortcode in functions.php to call the function.
- I created a test page and added the shortcode into that new page
- Then, I viewed the page (and the function would run)
- I kept tweaking the PHP function until the appropriate images displayed on screen (not formatted screen, just enough to prove a relationship between a product Title in the array and the images that displayed below it.
- In order to match all the images to any given product, the code matched the image filenames to the product Title using “starts with” code.
- When there were no image matches, the product was associated with the ‘”default” image placeholder.
In the staging environment, test phase 2
Once the rough connections between images and product Titles were made on that test page,
- I saved the full list of products in the client’s spreadsheet as a CSV file.
- I created the first draft of the WPAI Product Import from Spreadsheet to WooCommerce template. The template prompts you for the target CSV file, uploads the CSV, and stores it in the /files/ folder with the images. [WPAI offers other ways to import files and images. I use the CSV with FTP approach. You can check out the plugin for more options.] In this case, I entered the products’ CSV file I had just saved as the content to be uploaded.
- I cut the PHP function out of the functions.php file and pasted it into the Functions Editor section of the template.
- I replaced the get a product Title “from the array” programming code with get a product Title “from the field in the CSV file” being fed to the template as WPAI processes each product line item in the CSV file
- I added a call to the images function into the IMAGE field in the product import template (following the WPAI examples)
- I cut the shortcode definition out of the functions.php file since I no longer needed the Test page (cleaned up test code )
- I ran the WPAI template to learn about the results.
Once the product-to-images challenge was solved, my attention turned to manipulating the product categories. The product categories are separate and different from the post categories, and in this case, included categorizations other than the traditional product types.
Mapping Product Categories and Subcategories
Typically, mapping categories and subcategories is easy with the WPAI drag and drop interface, and category “rules” toggles provided in the plugin.
This client also adds manufacturers (MFR) and manufacturers’ brands to the categories. Still feels like primary category to subcategory relationship, so it should not require custom coding. However, due to how the categories were set up years ago, there are middle-level categories (that, apparently, helped the original website developer display the products) of which the client is unaware. (This was our first project together as we connected in February 2023.)
So, I made the client aware that that set up needs to be revisited, and for this project, I wrote another custom function for WPAI.
Data Situation:
- All products have a category, subcategory, and MFR
- Some products have a category, subcategory, MFR, and MFR’s Brand
- The MFR needs to be added as a subcategory to a “parent category” already on file (One of middle-level categories I mentioned above)
- Some MFR Brands are added at the same level as the MFR and some are added as a parent category
- The incoming data (category, subcategory, MFR, and MFR’s Brand) needs to match exactly what is already on file (as categories) so that products display within their “related” categories as navigation (menu structure) items are selected.
Testing Solution:
- I used the same process as above, adding a function and shortcode to the functions.php and a new page to the staging website for testing.
- I loaded the test array with categories, subcategories, MFRs, and brands, including some values that did not already exist in the categories list and some blanks.
- This testing took longer due to the exact-match requirement. It took several rounds of updates of the spreadsheet by the client to create exact matches. This is nitpicky stuff that the client would not normally need to know. IF they were adding the products one at a time using the WooCommerce interface, they would simply select a category from the list or add a new category to the list.
Since they are doing bulk product imports from a spreadsheet, they need to know the valid categories and subcategories. And since they are adding the MFR and MFRs’ Brands to the categories list, this means that four of the columns in the spreadsheet have to have exact matches in the categories list on the website. - I provided the client with the “official” list of categories and subcategories based on what already existed and the business requirements they had explained to me.
- Due to some changes made to the website in the live environment while I was working on the custom PHP functions, the client asked me to make manual adjustments to the categories and subcategories in both the live and staging environments in order to bring them back into balance.
- Using WP All Export (sister program of WPAI), I exported the list of categories and subcategories from the staging and live environments, then compared the lists using a desktop file comparison program that I have.
- I made manual updates to the environments until this comparison process showed no differences.
Production Solution:
- In the end, there were 55 categories and subcategories.
- The four columns in the spreadsheet included text that matched exactly.
- I copied the custom function into the WPAI template
After the product categories were ready, it was time to import and format the product description. Again, sounds easy. Just drag-n-drop the product description from the CSV file field to the product import template.
The challenge in this project is that in addition to the product description, there were multiple sections the client wanted displayed below the description, and the data associated with those sections was spread over twelve fields in the incoming CSV file.
Import Product Features and Descriptions
Next, it was time to import and format the product description. Displaying the product description for these products (on the public-facing website) required special formatting, and that meant writing another custom PHP function for the WPAI product import template.
The data to be displayed in the area was comprised of twelve columns from the spreadsheet. Some of the columns, like Product Features, fit together. Others, like Country of Origin, were their own little subsection.
Testing Solution:
- Since all of the incoming fields were simple text and WPAI has a preview mode on the description (body content) field, I started writing the custom PHP function inside the WPAI function editor section.
- I created the function in the WPAI Function Editor, first only passing in one field.
- I added the call to the function inside the body content field of the template. After previewing and tweaking for that field, I added another field. I previewed and tweaked for that field, then continued in a similar fashion until I all twelve fields had been added, and the preview displayed the product description area as the client wanted.
- The trickiest part was determining whether to print a section or not. If certain spreadsheet fields had no values, the section would not display on the public-facing website.
- Prior to the client review, I used CSS to modify the look for some headers, manipulate spacing, and format the Request a Quote button.
Production Solution:
- I copied the function into the WPAI Function Editor.
- I added a call to the function in the body of the content field at the top of the import template.
- I copied the CSS and pasted it into the Additional CSS area in WordPress
- I added a filter in the functions.php file to make the “Additional Information” product tab not display since the client wanted all product information available to visitors right on the product description page.
- ( add_filter( ‘woocommerce_product_tabs’, ‘woo_remove_product_tabs’, 98 ) )
- Testing Clean Up: None
And, there you have it. The process I used to import a product spreadsheet to WooCommerce.
Hit me up with a DM if you have questions. I’m /in/KarenCallahanMarlboro on LinkedIn and @KarenCallahanMA on Instagram.