How to upload a large CSV file to MySQL using PHP without duplicate entries?

Create unique key on longitude,latitude

and then you may use something like below

LOAD DATA LOCAL INFILE 'c:\\temp\\filename.csv'
replace
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@col1,@col2,@col3)
set
column1 = @col1,
column2= @col2,
column3= @col3;

this will replace duplicate long,lat and insert new

Note: you're strongly advised to use PDO, as mysql_* functions are being phased out. You will be able to get rid of all those addslashes() too!

For the insert, you can proceed like this:

Keep in session some information about the CSV

$import = array(
    'current' => 0,
    'total'   => 0, // Estimated
    'begun'   => 0,
    'fpos'    => 0,
    'flen'    => 0, // Total CSV file size
    'errors'  => 0,
);

and create a UNIQUE INDEX covering the fields for which you want no duplicates (CREATE UNIQUE INDEX ...)

When you import from the CSV:

- open $csv file
- fseek() the file to the $session['fpos'] offset
- MySQL set AUTOCOMMIT to off;
- MySQL BEGIN WORK;
- Get current time plus 10 seconds into $ttl
- loop
    - read one record using, say, fgetcsv()
    - try
        - insert into the DB using INSERT IGNORE
    - catch PDO error
        - MySQL ROLLBACK, $session['errors']++ and immediately die().
    - is time() equal or above $ttl? If so, break
- update the session object, set its 'errors' to 0, put fpos() of $csv file into it
- MySQL COMMIT;
- Your ETA is $session['begun'] + (time()-$session['begun'])*($session['fpos']/$session['flen']).

With the above method, it will take somewhat more than 10 seconds for each iteration.

If the inserts go well, the new session will contain the next file offset from whence to start.

If some error ensues, the whole transaction block will rollback, and it will be as if it never started. You keep track of consecutive errors (they might mean something's wrong in the CSV).

You can do this and return the session object as JSON. Then you can import the CSV calling the above script through, say, jQuery $.get(), and use it to update a progress bar. You will be able to calculate the expected time of arrival for the whole procedure:

 importing [##############                   ] 48%, 32m 15s left

This means that the "UPLOAD" page must move the CSV file in a temporary directory and immediately display the HTML that will show the progress bar. Then this HTML (and Javascript) will be responsible for the upload to go on.

There are also upload libraries such as PLupload that upload the file in "chunks" and display a progress bar of their own. Since usually network upload is slower than MySQL upload, it is very convenient to upload the chunks one at a time. The last CSV record in each chunk will likely be truncated, so some kind of check is required, and the "fragment" of that chunk must be saved and attached to the next chunk to reconstruct the record.

The plupload method has the advantage of making MySQL import apparently take zero time - as soon as the upload is finished, even with very large file, the data is available. You put it into a table with a temporary name (not a MySQL temporary table or you risk data loss), and when the upload is finished you simply atomically rename the table.