Difference between revisions of "Spreadsheet to sql asi sale"

Revision as of 15:22, 21 July 2018

2018-07-20 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 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