0

Hi I have written a query that creates multiple (four to be exact) temp tables (these tables are dependent on each other i.e., product of one table would affect the next table) and writes them to a CSV using OUTFILE. Like below

SELECT field1, field2
FROM table1
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
FIELDS ESCAPED BY '\'
LINES TERMINATED BY '\n';

I want to deploy this same query in an new environment where I am accessing the DB server remotely, and I want the CSV files to generated on the local machine (not the DB server). I was UNABLE to use the SED command like this:

mysql -umysqlusername -pmysqlpass databasename -B -e "select * from \`tablename\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv

Because I needed each temp table as a separate CSV and those temp tables are all interdependent, being populated in a single query. Other than setting up an network drive or FTPing the files over to the client, are there any other options?

This would need to be a permanent solution as this query will be running automatically nightly.

Thank you in advance for your help!

Barmar
  • 596,455
  • 48
  • 393
  • 495
Dan
  • 1
  • 1
  • [this question](http://stackoverflow.com/questions/2867607/mysql-select-into-outfile-local) mentions a Python script that will convert a `mysqldump` file to CSV. – Barmar Sep 19 '16 at 16:37
  • Thank you - how would I use this in the case of everything being executed within a single query? Curently I have four OUTFILE statements in the single query - the script seems to be called similar to the sed function in that it cannot be called within the query itself – Dan Sep 19 '16 at 17:24
  • There's no way to do it in the query itself, it will be similar to what you're doing. – Barmar Sep 19 '16 at 18:53

0 Answers0