How to Export MySQL Table Data to CSV File Base on Scheduler

MySQL is a free open source database; therefore, lots tools available in other database like Microsoft SQL exporting features are not available in MySQL. Of course you can search in Google and you will find hundreds of MySQL data exporting tools, some of them may be free and some will cost you money.

Personally I like the free stuff, therefore, I have created a batch file that will go into MySQL database table and execute a select statement and dump the results into a CSV type format file. To automate the process I have scheduled the process with Windows Task Scheduler.

The whole idea is to be able to load that output CSV file into other system or just to loan to Excel. I know there is also a way to load data directly from MySQL table to an Excel Sheet via an ODBC driver, which is a topic for different day.

The project is divided in 4 simple steps and it contains 3 files (ExportDWData.BAT, SELECT_DocPoolFC.sql, dwdocdata.csv):

3 Files

3 Files

You can download the complete package here:  MySQLExportPackage.zip

1. File SELECT_DocPoolFC.sql

a. This is a MySQL script file and it will be executed against the database once the connection takes place. The “INTO OUTFILE” parameter needs to be change to where you want the output of the database. Here is an example of the MySQL script:

USE DWDATA;

SELECT

COMPANY,

CONTACT,

DEPARTMENT,

SUBJECT,

DOCTYPE

FROM dwdata.document_pool

INTO OUTFILE ‘D:/DSC/RESULTS/dwdocdata.csv’

FIELDS TERMINATED BY ‘,’

ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\n’

2. File ExportDWData.BAT

a. This is batch file that will connect to the database and execute the MySQL script again the database. In this file you have to change the PARAMETERS (area) to the proper path and location of the file. There is also an Output directory, you need this path because it needs to be deleted the old file first before generating an new one. Here is an example:

@REM *** PARAMETERS/VARIABLES ***

SET OutputDir=D:\DSC\RESULTS

SET mysqldir=C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin

SET mysqlpassword=admin

SET mysqluser=admin

SET selectScript=D:\DSC\SQLScripts\SELECT_DocPoolFC.sql

@REM *** EXECUTION ***

@REM Clear the Output directory

DEL %OutputDir%\*.csv /F

@REM Change to mysqldir

CD %mysqldir%

@REM Run the create table sql script – The column count must be exactly as the column in csv file

mysql -u %mysqluser% -p%mysqlpassword% < %selectScript%

@REM – YOU ARE DONE!

3. File dwdocdata.csv

a. This is the Output of the batch file. Here is an example of the file:

“Peters Engineering”,\N,”Organization”,”PE-Stationary”,”Form”

“Peters Engineering”,\N,”Organization”,”PE-Letter template”,”Form”

“US-Steel”,”Juan Tenemaza”,”Production”,\N,”Invoice in”

“Phone Wizard”,\N,”Accounting”,\N,”Invoice in”

“Rapid Transport”,\N,”Logistics”,\N,”Invoice in”

“Robinson’s Refuge”,\N,”Technical Services”,\N,”Invoice in”

“Storage Experts Inc.”,\N,”Logistics”,\N,”Invoice in”

4. Automating the process (in Windows 7).

a. In windows start typing Task in the search box:

Start --> Search

Start –> Search

b. From the list select Task Scheduler

Lunch Task Scheduler

Lunch Task Scheduler

c. From the main start-up window select Create Task

Create Task Link

Create Task Link

d. In the task creation wizard enter all required information. In the action tab select the batch file that was created in step 2.

Create Task Select Application

Create Task Select Application

e. Once you have select your batch file you can schedule the process to run base on schedule and time intervals.

Create Task - Scheduling

Create Task – Scheduling

DONE!

About onlinejt

Blogger

,

3 Responses to How to Export MySQL Table Data to CSV File Base on Scheduler

  1. Someone 02/16/2011 at 7:17 pm #

    The file ExportDWData.BAT may not work if the folder name where you want to export the data or the path to MySQL directory has spaces. If it has spaces you have place inside of double quotes (E.g.: “C:Program Files (x86)MySQLMySQL Server 5.1bin”)

  2. Ghostboy_07 09/25/2012 at 8:35 pm #

    Does anyone know instead of having static stated in this line:INTO OUTFILE ‘D:/DSC/RESULTS/dwdocdata.csv’
    Is it possbile to have this in dynamic way?

  3. Juan Tenemaza 09/27/2012 at 10:00 am #

    There is a command from DOS command can be combined with MySQL language.

    So it will be something like:

    SET FolderPath=C:MySQLData

    INTO OUTFILE % FolderPath%Out _%random%.csv

    I have not tested so I am sure if it will work. The value is %random%.

Leave a Reply

*