peter-cooper
asked on
No option to replace table data in phpmyadmin using CSV file import
Hello
Probably a very simple answer to my question but here goes.
Plesk has updated phpmyadmin to V4.5.1 and when I try to import a CSV file there is no option to 'Replace table data with file'. I have searched the docs but cannot find any reference to why this is missing or what has replaced it.
How do I update using CSV and replace the table data with new data via CSV.
I just want confirmation from experts before I start the process to avoid mistakes.
Thanks
Probably a very simple answer to my question but here goes.
Plesk has updated phpmyadmin to V4.5.1 and when I try to import a CSV file there is no option to 'Replace table data with file'. I have searched the docs but cannot find any reference to why this is missing or what has replaced it.
How do I update using CSV and replace the table data with new data via CSV.
I just want confirmation from experts before I start the process to avoid mistakes.
Thanks
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.
ASKER
@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'
ticked and unticked. Can I then ask how you perform an update using CSV file? Thanks
ASKER
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';
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.
I have never used this, but I found relevant-looking information on page 62, 94-96, 102, 115. Maybe worth a quick look.
ASKER
@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)
ASKER
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.
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.
ASKER
@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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you dave for your help
You're welcome!