Maximum PC

I know that the bulk of the community on MaxPC aren't all programmers, but I've got another MySQL/SQL question...

I have a large list of rows of data to add to a mysql table.

The data is laid-out like this:
file_id, filename, file_alias, tags, permissions

In the list, the only real values that differ from row to row is the file_id (which is incremental values from 1 to 300) and the filename, whch simply has a number
attached to it, for example: switzerland-1.jpg, switzerland-2.jpg, etc and so on). The permissions field and tags are the same for all 300 entries.

How can I do a bulk INSERT on a MySQL table, or do I need to dump this list into a text file and then import it via MySQL. Al I have to work with on the webserver is PHPMyAdmin. I don't have access to a command line or prompt or anything else. How do I import this data into mysql. The version on the server (I think) is 5.1.48.

I'm a bit confused. Are you saying that there isn't a real programming environment on the machine, so you'd have trouble inserting the records? I'm a bit lost. Three hundred files shouldn't take long to insert, if that's what you're asking.

I have always used excel to build insert statements when I had a ton of inserts to do.

This isn't the exact method I use, but it should get you started (and is probably better than what I do anyway).

http://tgrignon.wordpress.com/2007/03/0 ... ql-server/

phpMyAdmin has an import feature.

Put the data into a CSV file and then navigate to the table page in phpMyAdmin. Select import at the top and upload the CSV file containing your data.

What's the advantage over using a macro facility in TextPad or Emacs?

Are you using Excel to actually insert the data into the database? (presumably you get the raw data from somewhere, or generate it, tinker with it using Excel, then hit a button to push it into the database?)

[Edit: Changed WordPad to TextPad]

No, I haven't used the data connections with excel to do any updating. Typically I end up with a sheet of data and I just insert columns inbetween the values and enter [ ', ' ] (sans brackets) into the blank cells between the data and then just drag them to the end of my data. If I already have an Excel sheet it is usually the fastest way to do it. Why would I switch to wordpad (ew?) or emacs (double ew) when I am already in Excel?

Too bad... saving a step would actually be pretty cool!


I like that idea CrashTech! Hopefully, I'll be able to recall that one some day when it could come in handy!


Well, if you're already in Excel and doing a relatively simple data massage like adding a comma, then you wouldn't want to open/use another program. Use what works... especially if you're already up and running with it.

Alright, I expected a remark about Emacs, but why did you give TextPad an "ew"? Now you've really crossed the line! =)
[Note: this statement is out of context now because, originally, I had mistakenly written Wordpad, not TextPad. Of course, now I feel somewhat entitled to give CrashTech a hard-time, because on the "ew" scale, Emacs was ranked twice as badly as WordPad!]

Actually, TextPad is a nice, fast editor, with the best mouse-event recording and playback system that I've used*. This type of macro-recording can really save a non-programming friend/co-worker oodles of time (Well, assuming they don't ask you to do it instead, which may not be such a bad thing depending on the co-worker).

A couple of possible examples where Emacs might make massaging data easier come to mind:
(Of course, I'm far from competent in Excel, so a few of these things might actually be easier than I realize)

1) You're given a file to load into the database. One of the column's is a string datatype. Of course, the data hasn't been "cleaned", and in addition to double-quotes at the beginning and end of each strings, some of these strings include double-quotes in the interior. Let's assume the "policy" where you work is to change these interior double-quotes to single-quotes, can you do it (relatively easily) using Excel?

2) Same drill as above... this time you've been tasked with importing data from an old database that didn't meet 1NF. There is a name column with both the first name and last name that needs to be separated into two columns for your database.

3) A column contains floating point data in scientific notation (perhaps, generated from another program) that isn't compatible with your database. Now I'm certain that Excel can convert scientific notation to a decimal format, but can you do something like changing a capital E to a lowercase e and other formating changes within a cell?

*TextPad may not have the best mouse-event recorder in the world, but I don't recall ever having a problem using it. Of course, when I need to record a macro to massage some data, I use Emacs nearly 100% of the time (ie it's open and works well). Also, using keyboard shortcuts seems much less error prone. Plus, Emacs has a large library of "functions", which can save some hassle. Plus, things like search/find/replace are all standard functions in Emacs, and you can easily add "functions" to your Emacs... hence, the "programmable editor" moniker. Although I haven't done it, I would imagine that it is probably pretty easy to add a user-created function to Excel as well.

[Edit: Changed the WordPad errors to TextPad.]

WordPad has macro recording? Interesting... Although in my experience those that would be heavily using such a feature, probably shouldn't.

No, I can't believe that I've been using WordPad and TextPad interchangeably throughout the last couple of posts... Argh!

And you thought I was crazy for saying ew to WordPad

And yet, you double "ewed" Emacs, which makes you even crazier!

I still think Vi/vim/gVim >>>>>>>>> emacs and Notepad++ >>> gVim.

Not to be overly nerdish with the mathematical notation, but you could have reduced the above to...
I still think Notepad++ >>> Vi/vim/gVim >>>>>>>>> emacs.

Saving a couple dozen bits of ascii data, while still expressing your bad taste in text editors... =)

::facepalm::

Of course, I made the assumption that a/b/c indicated an atypical notation for set membership.

FYI, a common notation would be {a,b,c}.

Just sayin'... =)