Jump to content


Photo

csv import question


  • Please log in to reply
2 replies to this topic

#1 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 08 April 2006 - 01:45 AM

I'm trying to update an existing table with csv data. I want to dump it into the table playerstats which is linked to the players table by player_id. So, I need to first find the player_id, that matches the first name and last name of each record in the file before I can import. Make sense? Anyone have any suggestions on how to go about this? It would be much appreciated.

Heres a sample record that I want to import:

Lance Niekro,SF,9,1,2,0,0,0,2,2,1,0,0,0.222,0.222,0.364,0.586

Here is my playerstats table:

CREATE TABLE `playerstats` (
  `stat_id` int(10) unsigned NOT NULL auto_increment,
  `player_id` smallint(5) unsigned default '0',
  `year` smallint(5) unsigned default NULL,
  `mlbteam_id` char(3) binary default NULL,
  `ab` int(11) default '0',
  `runs` int(11) default '0',
  `hits` int(11) default '0',
  `doubles` int(11) default '0',
  `triples` int(11) default '0',
  `hr` int(11) default '0',
  `rbi` int(11) default '0',
  `walks` int(11) default '0',
  `so` int(11) default '0',
  `sb` int(11) default '0',
  `cs` int(11) default '0',
  `avg` decimal(4,3) default '0.000',
  `slg` decimal(4,3) default '0.000',
  `obp` decimal(4,3) default '0.000',
  `ops` decimal(4,3) default '0.000',
  `wins` int(3) default '0',
  `losses` int(3) default '0',
  `ip` decimal(4,2) default '0.00',
  `ha` int(4) default '0',
  `bb` int(4) default '0',
  `ko` int(4) default '0',
  `era` decimal(3,2) default '0.00',
  `k9` decimal(3,2) default '0.00',
  `sobb` decimal(3,2) default '0.00',
  `saves` int(4) default '0',
  `er` int(4) default '0',
  `gs` int(4) default '0',
  `cg` int(4) default '0',
  `whip` decimal(3,2) default '0.00',
  PRIMARY KEY  (`stat_id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=2962;


PLAYERS Table
`player_id` smallint(5) unsigned NOT NULL auto_increment,
  `lname` varchar(50) default NULL,
  `fname` varchar(50) default NULL,
  `picture_loc` varchar(25) default NULL,
  `mlbteam_id` char(3) binary default NULL,
  `league_abbv` char(2) binary default NULL,
  `position_id` char(3) binary default NULL,
  `height` varchar(6) NOT NULL default '',
  `weight` char(3) NOT NULL default '',
  `birth` varchar(10) default NULL,
  `college` varchar(50) NOT NULL default '',
  `jersey` smallint(2) default NULL,
  PRIMARY KEY  (`player_id`)
) TYPE=MyISAM AUTO_INCREMENT=1350;



#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 08 April 2006 - 04:58 AM

Well, the easy way is to use file() and explode() to parse the csv file, and then for each row send an update query to the database.

If the CSV is a big one, you may want to do it in fewer queries. You can do that by creating and loading a temp table (one query), and updating the real table by joining with the temp table (one query). I wrote myself a function called massupdate() to do this automatically, but it's part of a package and doesn't stand alone very well, so I'm afraid I can't easily post it for you. You may want to try your hand at building one though, it comes in handy surprisingly often.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:30 PM

LOAD DATA LOCAL INFLE with appropriate field/line terminators is also an option.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users