MySQL Forums
Forum List  »  PHP

Automating LOAD DATA INFILE with data collected regularly on an other system (Windows)
Posted by: Les Dee
Date: October 02, 2015 05:56AM

I have a Linux based LAMP site using MySQL.

Records are inserted regularly by a remote Access application using ODBC.

That seems to be fine for a small numbers of records but when a batch of data becomes large it is much too slow. 1,500 records takes well over an hour to upload. There could be instances in the future where 100K records or more would need uploading.

So I need a method to upload large datasets in some other way - primarily by uploading a csv file and then using LOAD DATA INFILE locally.

Some parts of the solution I am comfortable with (at the Windows end) but I need some help putting it together - in particular at the web site end.

I can do the following at the Windows end.

1. Create the csv file
2. Physically upload to a folder on the site using SFTP

I am posting here as I have done extensive mods to existing PHP files so I am reasonably good as modifying existing code or code suggestions but do not have enough knowledge to take this much further on my own.

I'm sure this could also be achieved by scripting (and I am open to suggestions in that area) but I have no real knowledge in that area.

I need some method to trigger the processing of the uploaded file. A cron job does not sound ideal as I then need to deal with the possibility of several files having been uploaded between cron runs.

Ideally, as each file is uploaded it should be processed. (Could lead to multiple files being processed at once unless the Windows end is in total control of the process).

I think an ideal solution would be to try and simulate what the Workbench does - it can take a local file (on the Windows system, upload it to the target site and then trigger the execution of the LOAD DATA INFILE statement and report back on success or failure.

I'm not sure if the following might be useful: I think I can send a URL to the web site from the Access application to trigger some processing and then receive back a response reporting back the result.

I hope this gives enough to get the ball rolling towards a solution.

Many thanks.

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.