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):
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:
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 mysqldir=C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
@REM *** EXECUTION ***
@REM Clear the Output directory
DEL %OutputDir%\*.csv /F
@REM Change to 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-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:
b. From the list select Task Scheduler
c. From the main start-up window select Create Task
d. In the task creation wizard enter all required information. In the action tab select the batch file that was created in step 2.
e. Once you have select your batch file you can schedule the process to run base on schedule and time intervals.