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 'SQL Scheduler and Reporting Tool'.


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:

"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'
  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 'SQL Scheduler and Reporting Tool' 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 'SQL Scheduler and Reporting Tool'.  Refer to:

Tags: -

Related entries:

You can comment this FAQ