Editing MySQL databases

[Maljonic]

I've been using Excel to edit a MySQL database then saving it as a tab delimeted text file, then having to go through the file and removing unwanted speech marks before SQLing it back into the database, which is a pain but loads quicker than editing entries one at a time.

Anyway I'm about to do the same again with a dictionary and wondered if there is better software to use than excel where I can edit stuff in columns exactly the same but where it saves/exports the data in a text file that can be run as an SQL thingy right away. Or if there's a better way of saving stuff in Excel, or exporting and importing through PHPMyadmin?

I'm wanting to convert the static dream dictionary here: http://www.maljonicsdreams.com/Dream_Dictionary/ into a database driven job, something I would have done in the first place if I'd known what I was doing three years ago.

 

[Kryten]

Dont know any off hand - it's a while since I faffed with any SQL.

However, in Excels options (saving options iirc) you can set the separators you want in the tab delimited files - you can certainly get rid of the speech marks.

 

[phlash]

A couple of options come to mind, both connecting to the DB rather than using intermediate files:

[1] Use ODBC to connect your MySQL database directly into Excel. This should let you view and edit tables as datasheets.

[2] Use a GUI database client (like SQuirreL or TOAD) which also displays and allows you to edit database tables in a tabular format.

 

[Maljonic]

I can't find where to edit the save settings in Excel. Squirrel looks sort of too uber nerdy for me to understand, can't even figure out what I'm supposed to do with the files once I download them. I'm installing TOAD which needs Microsoft.NET to work so I have to install that first, then I'll see what it's like.

The ODBC thing sounds like a good idea but I don't know how to do that.

 

[SheepCow]

You don't need to remove the speech marks. If you import the TSV as a TSV it will split on tabs anyway.

 

[Maljonic]

I got it to work exporting csv then importing csv again, like you said, but I had to change "Fields terminated by" to , and "Lines terminated by" to ; and it worked.

I had a look at the TOAD thing but I can't get it to display the data in a way that's any use for adding new stuff in there, i.e in columns and rows where I can drag data down as many rows as I need. I probably just don't understand the program, it looks very complicated.

 

[phlash]

.Squirrel looks sort of too uber nerdy for me to understand...
The ODBC thing sounds like a good idea but I don't know how to do that.

LOL - I know what you mean about SQuirreL
For ODBC have a look at:
http://www.mysql.com/products/connector/odbc/
...they also give you examples for Excel and more usefully Access here:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples-tools.html

HTH...

 

[Maljonic]

Oh yeah, I keep forgetting about Access. Thanks.