Database lines still appear after deleted in excel CSV import back into phpmyadmin

Wildboys86

Hi Everyone,
Forgive me for not using proper lingo, I'm brand new at this and learning every day.
I have a database that is about 2000 lines long, with about 15 fields in each line. Rather than go line by line in browse and edit, I've exported the database as a CSV file, and I edit in in Excel 2007.

My problem is this:

In updating the file, i've deleted about 300 lines that were incorrect, or items that have been discontinued from my website. Almost every other line has been edited with current pricing or new info. All I really did was delete the entire line in excel, and all the data then moved up a line to close the space. The database is organized by the first field/column, which is ID.

When I then import it back into myphpmyadmin, all the lines that I edited now show up on the website correctly, showing the edits I have made. However, the lines that I have deleted, and no longer appear in the CSV file, still show up online in the database. It appears that it overwrites the still existing lines, but nothing is telling it to delete the ID lines that no longer exist. Perhaps its something i'm not checking off, or perhaps it just doesnt work this way. Is there a simple solution to this? I called my webhost godaddy who couldnt really talk me through this. All they told me was that I have an error in a column query.

I'm choosing the following options when I import. Format: CSV using LOAD DATA, and I check off "Replace table data with file". the "ignore duplicate rows" isnt making a difference for me whether it is on or off.

Any help would great!
Thanks so much!

sneakyimp

If you have phpmyadmin, you should try looking at the table which you are ostensibly replaceing. checking "replace table data with file" as I understand it should empty the table completely before inserting the new records. If you look at the table and these deleted records are still in the table, try emptying out the table before you import the spreadsheet -- don't forget to make a backup first.

bradgrafelman

The rows aren't getting deleted because there's nothing telling the DB that you deleted them. Instead, it sounds like you're wanting to TRUNCATE the table before re-populating it with the updated CSV's content. This, of course, is assuming that you have some sort of locking mechanism that prevents any INSERTs or UPDATEs from occurring between the time you export and subsequently import the data.

If that assumption is not true, then you'll need a more involved approach that determines which rows were there but are intentionally missing from the CSV while distinguishing rows that are missing from the CSV because they are new (i.e. they were created after the CSV was created).

Weedpacket

Wildboys86 wrote:
or perhaps it just doesnt work this way.

No, it doesn't. If you don't tell the database to delete any records it won't delete any records (at least it certainly shouldn't!)
If you're exporting the whole table, then loading it back in again (after edits), then just before you load it you can issue a "TRUNCATE TABLE tablename" SQL command to empty it out ready to be reloaded. Note that this could be affected by foreign key constraints (that you would be told about when you make the attempt).

Wildboys86

Thanks guys! That seemed to work. I created a few backups, and then all I had to do was click Empty the table, and then import the csv file again, and it worked! I'm sure there's an easier way to do it, but for the once a year or so that I do this, it'll be fine.
THANK YOU!

Wildboys86

I can't seem to figure out how to edit the thread title to say resolved, but I guess this is Resolved.

bradgrafelman

Under the "Thread Tools" menu at the top, there is a link to "Mark Thread Resolved."