Spreadsheet to sql asi sale

2018-07-20 Friday

Overview

This nn wiki document contains first notes and development thoughts on writing a script to convert a single sheet of a spreadsheet file or comma separated value file to corresponding SQL statements, SQL which may be used to populate products in the context of Opencart 2.3.2 database. This specific themed document will be linked to by an ASI works wiki document. Ted noting this plan on 2018-11-09 Friday.

Notes on terms of this article:

• spreadsheet columns are analogous to relational database fields,
• product or item attributes map to (get stored in) database fields,

^ ASI Plant Sale Spreadsheet Description

ASI plant sale 2018 has plants listed in a spreadsheet with the following fields (columns):

  price, name, description, image_filename, quantity

These item attributes map to Opencart table columns (fields) in the table oc_product and oc_product_description:

         price --> oc_product.price
          name --> oc_product_description.name
   description --> oc_product_description.description
image_filename --> oc_product.image
      quantity --> oc_product.quantity

So the info we've been given maps to two OC tables. There are two more tables we need update resepctively, to make given plant items appear in a given store and to make items searchable by one or more product (item) categories. These tables are oc_product_to_store and oc_product_to_category. These tables can be updated with the insertion of records which refer to the product ids of the items newly added to OC's table oc_products.

^ OC Category-Related Tables

Here are two key tables needed to hold categories which OC users define:

oc_test_cart> describe oc_category;

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| category_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| image         | varchar(255) | YES  |     | NULL    |                |
| parent_id     | int(11)      | NO   | MUL | 0       |                |
| top           | tinyint(1)   | NO   |     | NULL    |                |
| column        | int(3)       | NO   |     | NULL    |                |
| sort_order    | int(3)       | NO   |     | 0       |                |
| status        | tinyint(1)   | NO   |     | NULL    |                |
| date_added    | datetime     | NO   |     | NULL    |                |
| date_modified | datetime     | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

oc_test_cart> describe oc_category_description;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| category_id      | int(11)      | NO   | PRI | NULL    |       |
| language_id      | int(11)      | NO   | PRI | NULL    |       |
| name             | varchar(255) | NO   | MUL | NULL    |       |
| description      | text         | NO   |     | NULL    |       |
| meta_title       | varchar(255) | NO   |     | NULL    |       |
| meta_description | varchar(255) | NO   |     | NULL    |       |
| meta_keyword     | varchar(255) | NO   |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

And a third table which holds data to associate OC categories with OC stores:

oc_test_cart> describe oc_category_to_store;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| category_id | int(11) | NO   | PRI | NULL    |       |
| store_id    | int(11) | NO   | PRI | NULL    |       |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

^ Gotchas In The Spreadsheet

Converting from Excel type spreadsheet to comma separated value file requires some attention to field (column) delimiting characters, and to characters which have syntactic meaning in MYSQL and other database contexts. Some stuff to attend to:

1 Use double quotes only around spreadsheet cell data which contains commas
2 Avoid single quotes as these express syntax in MYSQL database context
3 Avoid double quotes in the middle of textual data, as double quotes are needed for purpose in (1)

Spreadsheet filenames must respect case of the actual filenames. When there's a mix of .JPG and .jpg files in the uploaded images, and the spreadsheet refers to all files as ending in .JPG, a file renaming action needs be taken. One shell based solution is:

  $ for file in *.jpg; do newname=`basename ${file} .jpg`; newname=${newname}.JPG; echo ${newname}; mv $file $newname; done