By cruze72 on
Hi,
Have looked around for a while for a solution to be able to export product data via csv. The task is not made easy due to the fact that product info is stored in at least 3 different tables.
Here's my solution to export data from multiple database tables to a single csv which is then renamed with a datestamp and can then be sent to another folder / server via cron. In this example i have used uc_products, node & content_type_product so that i can get SKU, Product name and a number of fields all into one file.
Put the following into a .sh file and run it through cron:
#!/bin/bash
MYSQLHOST="localhost" MYSQLDB="database_name" MYSQLDUMP="/path/to/file.csv" MYSQLUSER="database_user" MYSQLPASS="user_password"
MYSQLOPTS="--user=$MYSQLUSER --password=$MYSQLPASS --host=$MYSQLHOST $MYSQLDB" FDATE=`date '+%m-%d-%y'`
echo "SELECT * FROM uc_products LEFT JOIN node ON uc_products.nid = node.nid LEFT JOIN content_type_product ON uc_products.nid = content_type_product.nid" | mysql $MYSQLOPTS | perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' > $MYSQLDUMP
cp -f /path/to/file.csv /path/to/newfilename_$FDATE.csv
Hope this helps someone else :)
