MySQL Insert Values that contain comma or apostrophe (single quote)

One of the biggest challenges in setting up a new online shop or e-commerce business on something like Virtuemart is setting up the product master data.

Download our Excel template for loading vm_product in Virtuemart

To help us upload bulk data, Website Freedom have developed an Excel spreadsheet template for loading simple product data for our customers. The spreadsheet takes column data and rewrites it as MySQL Insert value statements which can then be run against the Content Management System's database - in this case Joomla Virtuemart.

Excel template for dataloading (simple) Virtuemart products into vm_products, vm_product_category_xref and vm_product_price

The script created inserts into the three tables and deals with difficult characters in the text like apostrophe (single quote) and comma, both of which are typically used as delimiters. The product data loaded is simple and comprises:-

product_id, Product SKU, Product Name, Price, Currency and Short Description.

Of course you can use the Excel template as a starting point and add more fields to create a more complex dataload into Virtuemart.

Loading data using SQL Insert containing comma or apostrophe characters

The way we get around apostrophe (single quote) and comma characters being in the collected data for dataload, is to use the substitute function in Excel to do these changes....

How does this treat the text to upload?

The example below explains what happens. A comma or an apostrophe is treated as a field delimiter in a SQL Insert statement and so the text is converted from this...
Lovely, sparkly knittery brooches by Max's World
to this...
'Lovely\, sparkly knittery brooches by Max''s World'

With these changes to the delimiters in the data, the data will load normally into an SQL Insert Values statement, eg.

INSERT INTO `jos_vm_product` (product_id,vendor_id,product_sku,product_s_desc,product_publish, `product_weight_uom`, `product_lwh_uom`, `product_in_stock`,`product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, product_name) VALUES (190,1,'MABW','Lovely\, sparkly knittery brooches by Max''s World','Y', 'pounds', 'inches', 9999999, 1303430400, '48h.gif', 'N', 0, NULL, 1303199907, 1304955451,'Knittery brooch - white');