Spreadsheet to sql asi sale - Revision history

Ted at 19:35, 9 November 2018

2018-11-09T19:35:13Z

Ted: /* ^ Gotchas In The Spreadsheet */

2018-07-21T15:22:31Z

^ Gotchas In The Spreadsheet

Ted: /* ^ Gotchas In The Spreadsheet */

2018-07-21T15:01:36Z

^ Gotchas In The Spreadsheet

Ted at 15:01, 21 July 2018

2018-07-21T15:01:15Z

Ted at 02:47, 21 July 2018

2018-07-21T02:47:25Z

Ted at 02:26, 21 July 2018

2018-07-21T02:26:05Z

Ted at 02:20, 21 July 2018

2018-07-21T02:20:50Z

Ted at 22:06, 20 July 2018

2018-07-20T22:06:24Z

Ted: 2018-07-20 - Friday, Ted starting notes on how to convert CSV file to SQL statements for plant sale data entry.

2018-07-20T20:49:32Z

2018-07-20 - Friday, Ted starting notes on how to convert CSV file to SQL statements for plant sale data entry.

New page

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:

<ul>
<pre>
price --> oc_product.price
name --> oc_product_description.name
description --> oc_product_description.description
image_filename --> oc_product.image
quantity --> oc_product.quantity
</pre>
</ul>

@@ Line 1: / Line 1: @@
 -07-20 Friday
 Notes on terms of this article:
@@ Line 8: / Line 12: @@
 ASI plant sale 2018 has plants listed in a spreadsheet with the following fields (columns):
@@ Line 23: / Line 30: @@
        quantity --> oc_product.quantity
 </pre>
-</ul>
+</ul><!-- comment -->
 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.
 == [[#top|^]] OC Category-Related Tables ==

@@ Line 90: / Line 90: @@
   Avoid double quotes in the middle of textual data, as double quotes are needed for purpose in (1)<br />
 </ul>
 <!-- comment -->

@@ Line 86: / Line 86: @@
 <ul>
-  Use double quotes only around spreadsheet cell data which contains commas
+  Use double quotes only around spreadsheet cell data which contains commas<br />
-  Avoid single quotes as these express syntax in MYSQL database context
+  Avoid single quotes as these express syntax in MYSQL database context<br />
-  Avoid double quotes in the middle of textual data, as double quotes are needed for purpose in (1)
+  Avoid double quotes in the middle of textual data, as double quotes are needed for purpose in (1)<br />
 </ul>
 <!-- comment -->

← Older revision		Revision as of 15:01, 21 July 2018
Line 78:		Line 78:
	</pre>		</pre>

		+
		+	<!-- comment -->
		+
		+	== [[#top\|^]] 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:
		+
		+	<ul>
		+	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)
		+	</ul>


	<!-- comment -->		<!-- comment -->

@@ Line 27: / Line 27: @@
 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.
 Here are two key tables needed to hold categories which OC users define:
@@ Line 61: / Line 63: @@
 +------------------+--------------+------+-----+---------+-------+
 rows in set (0.00 sec)
 </pre>

@@ Line 32: / Line 32: @@
 <pre>
 oc_test_cart> describe oc_category;
 +---------------+--------------+------+-----+---------+----------------+

← Older revision		Revision as of 02:20, 21 July 2018
Line 28:		Line 28:


		+	Here are two key tables needed to hold categories which OC users define:
		+
		+	<pre>
		+	oc_test_cart> describe oc_category;
		+	ERROR 2006 (HY000): MySQL server has gone away
		+	No connection. Trying to reconnect...
		+	Connection id: 3451080
		+	Current database: oc_test_cart
		+
		+	+---------------+--------------+------+-----+---------+----------------+
		+	\| 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)
		+	</pre>



	<!-- comment -->		<!-- comment -->