Automate CSV files to MySQL Database? I'm a little mind struck at the second as I am looking to automate the following sequence:


Extract Data from Microsoft Access 
Save files as CSV
Connect toMySQL server 
Run one SQL file that exports all the CSV's using LOAD DATA LOCAL INFILE


I've written the entire SQL file that DROPs each table then re-uploads it and this runs like a dream in MySQL WorkBench. I just don't know how to get this to do the task without me physically carrying out the task...

I am now stumped how to automate part 4... There's no visual basic style backend to MySQL WorkBench and I thought requesting SSH from my provider would fix this, turns out my provider states SSH access is for web files not databases.

Is this the case with all MySQL or do some providers allow SSH directly to the database? If not how do I go about automating this sequence?

Many thanks.
Having researched and tested out a few ideas I have now found a solution. 

1) Access or Excel and utilise VBA to Save the CSV files to the FTP of where your website is.
2) After the SaveAs VBA statement I then had another line of code that fires open a web browser which loads a PHP page and executes a INSERT with LOAD DATA LOCAL INFILE... 

Even with a 50mb CSV file the saving process over BT Infinity only takes a minute or so and the PHP runs in seconds.

I'm no natural born coder, I find solutions to what I need to achieve and here's what I have working...

Here's the PHP (ensure MyTable.csv is in the same folder as the PHP file else modify below):

&lt;?php

$sql1 = "TRUNCATE TABLE MyTable";

$sql2 = "LOAD DATA LOCAL INFILE 'MyTable.csv'
INTO TABLE MyTable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
(@date_time_variable, field1, field2, field3, field4)
SET datestamp = STR_TO_DATE(@date_time_variable, '%m/%d/%Y %H:%i');";

$con=mysqli_connect("mysql_server_address","mysql_username","mysql_password","mysql_table");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result1 = mysqli_query($con, $sql1);
$result2 = mysqli_query($con, $sql2);

if (mysqli_affected_rows($con) == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con); 
};

echo $message;

mysqli_close($con); 

?&gt;


The VBA I used to save to FTP:

ThisWorkbook.SaveAs "ftp://ftp_username:ftp_password@ftp_site_address/MyTable.csv"


Thanks for the responses to the original question to those that tried to help but as you can see I needed a solution that required no extra input from the user, one button does all.

The only thing you will need to ensure is that the MySQL server you are scripting to allows the use of LOAD DATA LOCAL INFILE as some restrict this on shared servers. Also take note that you'll have to constantly have FTP access enabled or enabled from your IP Address as many hosts like this locked down these days.

Hope this helps someone given the fact it wasn't easily answered.