Notification Services

ID #1156

Can I schedule CSV export and import?

The CSV export and import functionality of the SQLyog GUI are not directly implemented in the SJA. But both are posible with relative simple SQL statements from 'Notification Services'.

 

1) export:

You can schedule and execute a "SELECT INTO OUTFILE .." statement.  That will store the outfile on the machine where MySQL is running (as SELECT INTO OUTFILE has no LOCAL option in MySQL).


From MySQL docs:
http://dev.mysql.com/doc/refman/5.0/en/select.html

"Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

SELECT a,b ... INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;"


Note that the outfile must be renamed or deleted before next run. This can be done by a batch job that also calls SJA. or you may use prepared statements with 'Notification Services' to generate the file name dynamically using a timestamp for instance.


One detail to observe is that with MySQL on a Windows machine "\" characters in the file path must be escaped (as normally within strings).

This is correct: SELECT a,b INTO OUTFILE 'c:\\tmp\\result.txt' ...

and this will fail: SELECT a,b INTO OUTFILE 'c:\tmp\result.txt' ...

 


2) import

In quite a similar way you may schedule a LOAD DATA (LOCAL) INFILE statement with 'Notification Services'.  Refer to:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Tags: -

Related entries:

Last update: 2008-09-09 15:57
Author: Peter Laursen
Revision: 1.0

Digg it! Print this record Send FAQ to a friend Show this as PDF file
Propose a translation for Propose a translation for
Please rate this FAQ:

Average rating: 0 out of 5 (0 Votes )

completely useless 1 2 3 4 5 most valuable

You can comment this FAQ