Welcome to WebmasterWorld Guest from 3.85.245.126

Forum Moderators: open

Message Too Old, No Replies

Export, revise and re-import a table best practices

newbie question on how to export and re-import tables in mySQL

     
7:40 pm on Dec 4, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Dec 2, 2003
posts: 45
votes: 0


Hello --

Newbie as far as dealing with mySQL export/import, so sorry....

I need to export a table (about 1000 lines), open and alter and re-sort records in Excel (preferably), and then return it to my database, overwriting the old content.

What's the best way to do this? A standard csv export from phpmyadmin is using ; to terminate fields, but excel is not liking that - a CSV for excel export looks good in excel, but I think it will not re-import correctly, right?

People must do this all the time, right? What am I missing

Working on a Mac
MySQL client version: 5.0.84
phpmyadmin Version: 3.2.2.1

Thanks,
Don

8:14 pm on Dec 5, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


The first question is about the need to re-sort; if you re sort them in Excel, whatever select statements are on your site are likely to sort them exactly as they are doing now. If they aren't sorting the way you want, you can add order by clauses to your select statements (or modify the existing ones) to sort them as you wish.

As for importing/exporting, when you bring your files into Excel you should be able to select a predefined delimiter or indicate a custom one when you import. The same is true when exporting from phpMyAdmin. Whether or not you have the fields quote qualified must be addressed too.

The delimiter really shouldn't matter. Tab, a character, quote qualified, doesn't matter, whatever is easiest for you, as long as it's the same on both ends - when you export, then import into Excel - it should work out fine.

Where some error is in exporting the modified file from Excel. You have to open the exported file to check and make sure it exported right, check the delimiters and any quote qualifying that may be present. Then when you re-import it to phpMyAdmin, make sure the settings for the import are properly set.