• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

WooCommerce ODBC connector - numbers not recognized in Excel

jappie

New Member
Hello all,

I've succesfully created an ODBC connection with a website's WooCommerce (Wordpress) database. So far so good!
The strange this is that if I use the query-string that I've created for the ODBC connector in phpMyAdmin (so on the database directly), the quantity column behaves exactly as you would expect (i.e. the SUM of rows yields a total). So "SELECT SUM(`quantity`) FROM `qryOrderItems_Detail`" yields (example data 108)

Unfortunately the same data, after the query "download" into Excel via the ODBC connection is recognised as a "string" meaning I can't do any calculations of the field. Has anyone got any idea what may be the cause of this (undesireable) behaviour and know of a way to make sure the ODBC query shows the column contents correctly (as numbers) in Excel?

Your feedback much appreciated.

In the image below, notice how cell F2 has been "opened"
(just pressing F2-on keyboard) and closing for it to be recognised as "number/integer" 13?
ODBD received values in table
Same would happen for all other cells in column F if repeated.

Now, the data-source "as it is received" (so before I opened CELL F2) is useless when we need to use it for instance as a SUM in pivot table.
72144

ODBC details
ODBC Connection string: DRIVER={MySQL ODBC 8.0 Unicode Driver};UID=username;PWD=password;SERVER=server_name;DATABASE=database_name;PORT=3306
Query string: SELECT
p.order_id,
p.order_item_id,
p.order_item_name,
p.order_item_type,
max( CASE WHEN pm.meta_key = '_product_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as productID,
max( CASE WHEN pm.meta_key = '_qty' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Qty,
max( CASE WHEN pm.meta_key = '_variation_id' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as variationID,
max( CASE WHEN pm.meta_key = '_line_total' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as lineTotal,
max( CASE WHEN pm.meta_key = '_line_subtotal_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subTotalTax,
max( CASE WHEN pm.meta_key = '_line_tax' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as Tax,
max( CASE WHEN pm.meta_key = '_tax_class' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as taxClass,
max( CASE WHEN pm.meta_key = '_customer_user ' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as customer_user,
max( CASE WHEN pm.meta_key = '_line_subtotal' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) as subtotal
FROM
base_18_woocommerce_order_items as p,
base_18_woocommerce_order_itemmeta as pm
where p.order_item_type = 'line_item' and
p.order_item_id = pm.order_item_id
group by
p.order_item_id

THanks in advance for your ideas, pointers and suggestions, Jappie
 

Attachments

  • Screenshot 2020-11-19 170407.jpg
    Screenshot 2020-11-19 170407.jpg
    10.8 KB · Views: 0
  • Screenshot 2020-11-19 170037.jpg
    Screenshot 2020-11-19 170037.jpg
    14.3 KB · Views: 0
Back
Top