Re: Automating LOAD DATA INFILE with data collected regularly on an other system (Windows)

[Les Dee]

Access is the front end application that validates the data.

Data is submitted by users in batches using CSV files.

The Access application validates the data and then adds the records in the batch to the remote MySQL file via ODBC. In this case ODBC is the bottleneck.

My solution is to continue with this method where the uploaded data is small but for large volumes I want to create a new CSV file, formatted ready for insertion into the remote MySQL table.

The only way seems to be to use LOAD DATA INFILE.

Or rather the LOAD DATA LOCAL INFILE version.

Using MySQL Workbench I can run an sql script like

LOAD DATA LOCAL INFILE 'D:/folder/Export.txt'
INTO TABLE products
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(field1,field2,...)


and it works like lightning - uploading the txt file and then running the LOAD DATA INFILE command on the remote system where the database resides.

What I am struggling with is the automation of this on a Windows system so I can bypass Workbench.

An added complication is that the remote site requires connection via SSH before authenticating with MySQL - though I think I have a solution for that by using Plink.