MySQL Forums
Forum List  »  PHP

Re: Automating LOAD DATA INFILE with data collected regularly on an other system (Windows)
Posted by: Les Dee
Date: October 03, 2015 11:19AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.