I'm trying to export a mysql query to a .csv file, right now I'm running a successful query like:
us_id=`mysql -u $USER_NAME --password=$PASSWORD -D "databasename" \
-e "SELECT * \
FROM databasename.table \
WHERE somefield >0 AND otherfield ='$ctry' \
ORDER BY users \
DESC LIMIT 0,100";`
but I want to export it to a file, which I've tried by using:
us_id=`mysql -u $USER_NAME --password=$PASSWORD -D "databasename" \
-e "SELECT * \
FROM databasename.table \
WHERE somefield >0 AND otherfield ='$ctry' \
ORDER BY users \
DESC LIMIT 0,100 TO OUTFILE 'somefile.csv' ";`
but then I get a db access denied error. I've also tried piping the output to sed �s/\t/�,�/g;s/^/�/;s/$/�/;s/\n//g�, which also didn't work. If I could get TO OUTFILE to work I'd hopefully add this code to make the fields formatted:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY �\\� LINES TERMINATED BY '\n'
but I can't get the OUTFILE function to work. I could also use mysqldump I guess, but I don't know how to combine the query with a mysqldump. my output file exists and I chmod'ed it to 777 (for testing purposes).
---------- Post updated at 12:27 PM ---------- Previous update was at 10:10 AM ----------
got it, well really never got the TO OUTFILE working, but this worked:
echo "SELECT * \
FROM somedb.sometable \
WHERE field1 >0 AND field2 ='whatever' \
ORDER BY field3 \
DESC LIMIT 0,100 ;" > tmp
mysql -sN -u $USER_NAME --password=$PASSWORD -D "somedb" < tmp > temp_to_turn_into_csv_later.csv
now I have to parse the temp csv file into a csv using awk, there's probably an easier way though.