Importing a partial table - "Empty table before"?

**knows MySQLDumper** Joined: 04 Jul 2011 Posts: 6

I have a fairly large table (26,000 entries) that I've done some bulk updating to about 50% of it, prices changes, and I need to know if I can import just the changed portion or do I need to import the entire thing at once?

I ask this because I tried to import just the data that was changed and it told me that there were duplicate entries, I understand why this is, but I'm hesitant to use the 'Empty table before' option because I don't know if it will erase EVERYTHING in the table or if it will just erase the portion of old data that I'm replacing and then add the updated data to the table.

Hopefully this is clear.

Thanks,
Matt

Posted: 2011-07-04, 22:06 (No subject)

How did you do the backup?
MySQLDumper doesn't offer to do partial exports of a table.

Btw: don't use the "empty tables" option. It drops all tables of the target database. It is meant to be used when you restore backups that don't have a "DROP TABLE"-statement.

MySQLDumper always adds a DROP TABLE-statement in its backups, so that's why I am asking how you created the backup you try to import.

**knows MySQLDumper** Joined: 04 Jul 2011 Posts: 6

I used the export tool from inside the SQLBrowser.

Posted: 2011-07-05, 00:05 (No subject)

This also doesn't offer a partial export of the data of a table. So I wonder what you did.

But to answer this question:

Posted: 2011-07-05, 00:06 (No subject)

Why did you do that?

If you want to alter 50% of the data in a table, make a dump of that specific table (choose "select table" when creating a backup), download that file, unpack it, open it with a text-editor (MS Word isn't one!), make the changes you want to make, save it, upload it into work/backup folder and restore it.

That will completely delete your old table with all data in it and restore the data from the file you just edited.

**knows MySQLDumper** Joined: 04 Jul 2011 Posts: 6

Sorry if I wasn't clear, I exported the whole table using the export tool, then edited it in my spreadsheet program. I didn't realize that the backup tool would let me do just a table by itself. I thought it was an all or nothing sort of thing. I've gone back and looked again and I see the 'select table' option now.

I've gone and used the backup tool to export the proper template, i will redo my edits. Once thats done and ready do i upload it back to my server in the folder with the rest of the backups and restore from there?

Matt

Posted: 2011-07-05, 00:14 (No subject)

So this issue is solved.
Have fun using MySQLDumper. Wink

Posted: 2011-07-05, 00:16 (No subject)

Posted: 2011-07-05, 00:22 (No subject)

But before you import your edited file, make a new backup of the table in question.
This way you can restore this file in case you broke the file with your edits and restoring doesn't work.

When editing the sql-file, make sure you won't change the encoding of the file!
I suggest you use notepad++ to edit the file.

**knows MySQLDumper** Joined: 04 Jul 2011 Posts: 6

Yea, i noticed the encoding issue, notepad++ will work, just makes it a bit longer. I have 26,000 prices to alter. but since I'm doing in bulk i can use find/replace, just will take longer than using excel. :-) but I will get it done. I'm too stubborn to let it go. :-)

Posted: 2011-07-05, 00:33 (No subject)

You can also do bulk replaces using SQL in the SQLBrowser.
E.g.:

**knows MySQLDumper** Joined: 04 Jul 2011 Posts: 6

It ended up being much faster to export it using the export tool with " and ; as delimiters and making sure that Openoffice Calc was set to UTF8, sorting the columns, editing the prices, then importing it in a few batches using the import tool. There was no reasonable way to edit 26,000 lines the way i needed to in notepad ++ since it can't sort and there is huge range of prices. :-)

Posted: 2011-07-05, 01:18 (No subject)

Ok, this time this way won. Wink

But if the number of records grows too big, so that the csv file can't be imported because it exceeds the max execution time of PHP you will remember my words. So I hope you wont have to repeat that again.

**knows MySQLDumper** Joined: 04 Jul 2011 Posts: 6

I just copy and pasted into the text box. :-) I hope I don't have to change those prices in bulk again either. :-) even with a spreadsheet it still took me 15 min to change all 26,000 records. :-)