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

[Les Dee]

Peter Brawley Wrote:
-------------------------------------------------------
> > 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.
>
> If by "to the remote MySQL file" you mean "to the
> remote MySQL table", the data doesn't need to be
> imported into MySQL via Load Data--it's already in
> MySQL, and your problem is just to pass multiple
> MySQL data rows from one server to another. That
> can be as simple as connecting to the remote
> server with PHP, sucking up the desired rows, then
> connecting to the local server and inserting them.


Not quite so. To clarify: The 'local' system has no web server and no PHP - just an Access database used to 'pre-process' the data that needs to be uploaded to the remote web site's My SQL database.

The remote Products file is defined in the local Access database as an ODBC linked table.

Currently the validated data is first loaded into the local Access database and then I execute an append query to extract records from the local database and 'send' then to the remote Products table.

My idea, when the dataset is large, is not to use the above append query but to create instead a csv file which I can then upload using LOAD DATA LOCAL INFILE