[UPHPU] FW: Importing large csv file into MySQL

[Matthew Frederico]

On Thu, 2004-03-18 at 13:31, Ashley Oviatt wrote:
> I sent this accidentally to just Mac, and now here it is again for all of
> you:
> 
> > Hello all,
> > 
> > I have a large csv file (1 million records) that I need to 
> > import into a mysql database. I have tried phpMyAdmin's 
> > import utility but it always errors out after a couple 
> > minutes of processing. It works if I take 10,000 records out 
> > of the csv as a test import, so I know my syntax is correct. 
> > 
> > I'm guessing the csv is corrupted down the line somewhere, so 
> > I tried to import it into MS Access. Low and behold it 
> > worked. So now I have the csv DB in Access and can connect to 
> > the MySQL DB via an odbc connection.
> > 
> > So I just need to know how to copy the csv table into the 
> > mysql table. It will only let me copy about 30,000 records at 
> > once without saying memory is all used up.
> 
> Mac suggested that I import it directly from the command line mysql. Do any
> of you have the syntax for that?
> 
> Ashley
> 

First of all, MYSQL 3x will choke on records of that magnitude.  

Mysql 4x does a better job, but your seek time will be slow on a million
records.  I recently built some list management software for a company
and had to scrap mysql because it was too slow ..  I even maxed out
memory, and everything.  

However, there is command line import for CSV and other arbitrary data:
http://www.mysql.com/doc/en/LOAD_DATA.html

That should get you on the right track.