1

I'm a little mind struck at the second as I am looking to automate the following sequence:

  1. Extract Data from Microsoft Access
  2. Save files as CSV
  3. Connect toMySQL server
  4. 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.

1

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):

<?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); 

?>

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.

0

MySQL cannot reach outside itself for any kind of execution. This would be a serious security problem.

Instead, you write script or program that orchestrates what you need. It can reach into ACCESS and either extract the data or create a file. Then the script/program reachs into MySQL to insert the data or have it read the file.

0

MySQL Workbench allows to migrate data from various source RDBMSes to MySQL (e.g. MS SQL, Access, postgres). Start the migration wizard via the Database menu and select MS Access from the drop down in the source db settings:

Sourece DB selection in the Migration Wizard in MySQL Workbench

Continue after that and select your MySQL server you want the data end up. It will set up all meta data for you and gives you 2 (soon 3) ways to transfer your data to the target.

If you insist on having CSV data another option will soon be available in MySQL Workbench: Table Data Import/Export. This is a flexible data store/load solution for CSV and JSON files.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.