No option to replace table data in phpmyadmin using CSV file import

I have versions 4.0.10 and 4.7.0 and I can see what you mean.  However, I have never checked that option when I have uploaded CSV files.  If you are unsure, you can make a copy of the original table, do the CSV upload to the copy, and then rename the copy back to the original name if the upload succeeds.

@Dave thanks for reply. I have done what you suggested but what ever I try no data is entered. I have tried with

'Update data when duplicate keys found on import' 

Select allOpen in new window

ticked and unticked. Can I then ask how you perform an update using CSV file? Thanks

I even tried the following and does nothing. Think it may be something on the server.

LOAD DATA INFILE '/var/www/csv/MORE-destruction.csv' REPLACE INTO TABLE `boxes` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Select allOpen in new window

Please see https://media.readthedocs.org/pdf/phpmyadmin/latest/phpmyadmin.pdf

I have never used this, but I found relevant-looking information on page 62, 94-96, 102, 115.  Maybe worth a quick look.

@Ray Thanks for that. Nothing there though that I wasn't aware of. Makes no mention of what replaced the replace data option of previous versions. I am now getting an error in phpmyadmin below which is strange because I have setup peter as a mysql user with password. Think I may need to ring 1and1 bloody 1.

#1045 - Access denied for user 'peter'@'%' (using password: YES)

Select allOpen in new window

When I open phpmyadmin there is no option to set priviliges. Guess it must pick it up from plesk. But it isn't. Guess something may have broken on update. Time to get digging lol

There are several things about uploading CSV files.  1.  The columns in the CSV file either must match exactly the columns in the table or 2. You must enter the list of the columns in the CSV file in the correct order at the bottom of the import page.  If you have a AUTOINCREMENT id in a column, you will have to enter the column names.  If your CSV file has a 'header' row, you must enter the number of rows to skip which is usually 1.  I usually copy the table to a new name and then TRUNCATE the table.  That keeps the structure but deletes all the previous data.  Then import the CSV data.

If you are on Shared Hosting, it is unlikely that you can add another user.  The hosting company normally sets the only privileges that you have.

@Dave What I normally do is export from MySql as CSV for Excel with column headers. Make my changes to data, remove the header and save as comma delimited CSV. But, on this occassion, I am dealing with unknown id, so that field is blank. So I need to find a way to import using some other clause such as in the where clause. Thanks

Thank you dave for your help

You're welcome!