Sema Data Co-Op


CaseStudyImage

Technologies:

Wordpress, MySQL Server, WooCommerce, PHP

Overview:

We were approached by “Client A” to create a project to create an automated csv and zip file import process for product data. This import process would integrate as a WordPress Plugin into an existing WooCommerce store. This import procedure could be initiated manually by uploading a csv file or by scanning a specific folder for csv files. The product data would be imported via CSV files which would be automatically uploaded to the clients local server via FTP transfer.

Upon successful FTP transfer our web application would scan the FTP folder for csv and/or zip files and perform the import procedure. The import procedure would insert the product data into an existing woocommerce database structure which would create published product listing pages for successful imports.

Manual Process:
Step 1: Upload CSV File with Headers for Import Data
Description: User selects a CSV file for import in order to establish mapping of columns to WooCommerce fields
Response: Plugin displays a list of WooCommerce fields in one column. In a second column drop down lists are displayed for all headers found in the CSV File.

Step 2: Map column headers to WooCommerce Fields
Description: User selects the column they wish to map to a WooCommerce field. i.e. CSV_File_Advertised_Price mapped to WooCommerce regular_price

Step 3: Upload Zip file with Images associated with the previous CSV File
Description: User selects the zip file containing images for the products contained in the CSV file selected in step 1. Products are matched by SKU numbers in the image filename and CSV File row

Step 4: Product Import Complete
Description: The product import process occurs in around 5-30 seconds and the user is presented with a success or error message

Automated Process:
Step 1: Setup FTP Settings
Description: User goes to the plugin settings page and enter the ftp server, login username, and password.

Step 2: Setup automated FTP uploads
Description: User goes to the data delivery portal they use and sets up automated uploads of product data to the server.

Step 3: Scan and Import
Description: The plugin performs scans every 5 minutes. Upon finding a valid CSV file it imports the product data and scans the image folder for matching images to associate to the products. Upon finding a valid zip file it extracts the images to the image directory and associates the images to products based on the SKU number in the filename.

Challenges:

In the process of implementing the solution we quickly realized that the import procedure would be severely bottlenecked by processing the files using PHP to read each row in the CSV file. Some files would have over 20,000 rows which could take 4-5 minutes to perform reading and importing often with even slower response times. In order to address this issue we utilized a large database import of the csv data into a custom MySQL database structure and performed our data merge by utilized stored procedures to insert the product data in large batches. This decreaed import times to below 1 minute even in the case of very large files.

In some cases csv and zip files would fail during FTP transfer leaving a partial corrupted file in the FTP folder. In order to address this we implemented a try catch style of scanning the directory. We would attempt to import the csv or extract the zip file. If the import was successful we would temporarily store the successful imports in a last import directory. If the import or extraction met with issues we placed the files in questions into an failed to import file folder and emailed a message to the site administrator so they could review the files themselves or reattempt the ftp transfer from their data provider.

Final Completion: Upon final delivery of our solution we had a wordpress plugin that successfully import tens of thousands of products into a WooCommerce store allowing the site owner to save hours of time creating products manually. In addition updates to pricing, product data, and images became an automated process so the site owner could focus on store management and sales rather than data management.

Back