Quantcast

Maximum PC

It is currently Wed Nov 26, 2014 6:10 am

All times are UTC - 8 hours




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: yet another question about MySQL & SQL language....
PostPosted: Sun Oct 17, 2010 6:44 pm 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1489
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.


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Mon Oct 18, 2010 11:02 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
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.


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Tue Oct 19, 2010 4:33 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
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/


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Tue Oct 19, 2010 9:49 am 
Million Club - 5 Plus*
Million Club - 5 Plus*
User avatar

Joined: Sun Sep 12, 2004 6:37 pm
Posts: 4745
Location: In the monkey's litterbox
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.


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Sat Oct 23, 2010 2:30 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
I have always used excel to build insert statements when I had a ton of inserts to do.

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]


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Mon Oct 25, 2010 5:58 pm 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
Gadget wrote:
CrashTECH wrote:
I have always used excel to build insert statements when I had a ton of inserts to do.

What's the advantage over using a macro facility in WordPad 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?)


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?


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Wed Oct 27, 2010 10:52 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
No, I haven't used the data connections with excel to do any updating.

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

CrashTECH wrote:
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.

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

CrashTECH wrote:
Why would I switch to textpad (ew?) or emacs (double ew) when I am already in Excel?

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.]


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Thu Oct 28, 2010 4:46 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
WordPad has macro recording? Interesting... Although in my experience those that would be heavily using such a feature, probably shouldn't. :)


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Thu Oct 28, 2010 8:06 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
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!


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Fri Oct 29, 2010 4:39 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
Gadget wrote:
CrashTECH wrote:
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 :)


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Mon Nov 01, 2010 1:53 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
Gadget wrote:
CrashTECH wrote:
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!


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Thu Nov 04, 2010 5:48 am 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
I still think Vi/vim/gVim >>>>>>>>> emacs and Notepad++ >>> gVim.


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Fri Nov 05, 2010 2:09 am 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
CrashTECH wrote:
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... =)


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Sun Nov 07, 2010 2:30 pm 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
::facepalm::


Top
  Profile  
 
 Post subject: Re: yet another question about MySQL & SQL language....
PostPosted: Sun Nov 07, 2010 5:28 pm 
Bitchin' Fast 3D Z8000*
Bitchin' Fast 3D Z8000*
User avatar

Joined: Tue Jun 29, 2004 11:32 pm
Posts: 2555
Location: Somewhere between compilation and linking
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'... =)


Top
  Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC - 8 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group

© 2014 Future US, Inc. All rights reserved.