Automate transfer of csv file to MySQL

I have a csv file that I will be regularly updating through a batch script that calls cygwin+ bash script. I would like to automate the upload of the csv file into a MySQL database such that a table in my database would be updated with the csv file at regular intervals. The database is currently running on a Windows Server 2003 machine and administered with phpMyAdmin.

I have looked online and found some ways that I could achieve part of that, but I am confused as to where the code presented in those sources should be placed and how they would be called. For instance, Import CSV file directly into MySQL seems to show how to upload a csv file to a MySQL database from the SQL command line once, but not repeatedly, the latter being what I need.

I wo开发者_开发百科uld prefer the solution to involved bash scripting (as opposed to batch and php) if possible (i.e. I would prefer a solution that I could integrate with the bash scripts that update the csv file).

Thank you

You can execute a MySQL script from the command line by doing something like:

mysql -uUsername -pPassword database_name < infile.sql

You could invoke that from the command line and in the infile.sql you could have code like:

LOAD DATA INFILE 'filename.csv' TO table_name 
FIELDS TERMINATED BY ','

You can use a here document:

# some bash script stuff
mysql ... <<EOF
SQL COMMANDS
GO HERE
EOF
# more bash script stuff

You can use Quartz to create a cronjob - for periodically updating your database. with the help of cronmaker (http://www.cronmaker.com/), you get to choose when and how often your database gets updated.

This is a sample SQL Script to import data into your MySQL database:

LOAD DATA INFILE 'c:/.../filename.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

run the above script in your cronjob using your preferred language.