Import a 150mb csv text file into mysql

Forum Moderators: open

Message Too Old, No Replies

Import a 150mb csv text file into mysql

Any ideas?

youfoundjake

7:17 am on Apr 19, 2008 (gmt 0)

I've been working on this for awhile now.
I need to import 150mb text file into mysql.
I would rather not break it up since I have 18 files each at 150mb, so if i chunk them at 75mb each, thats 36 files I have to load, ugh
anyone got a work around, like using a temp directory that I've been reading about?
Running php5, mysql and apache on my home machine, 2 gig memory so that's managable.

centime

11:44 am on Apr 19, 2008 (gmt 0)

i really look forward to you getting a workable answer, i've got similar issues

so far the answer has been a faster upstream connection

centime

11:51 am on Apr 19, 2008 (gmt 0)

assuming you're talking about uploading to a web hosts servers, there are obvious contrains like you machine might take it but some dedicated hosting packeges would choke on that immediately

I thought the max for csv was actually 50MB in most setups, i guess it depends on the hosting package

youfoundjake

8:43 pm on Apr 19, 2008 (gmt 0)

Well, the kicker is that I'm doing this all local. So thats no problem. I think I just may have to do it directyly through the mysql console, and bypass any php and apache time constraints.

youfoundjake

10:26 pm on Apr 19, 2008 (gmt 0)

ok, got it

mysql> load data local infile 'c:/path/file.txt' into table table_name
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
-> (column1, column2, column3, column4)
-> ;

I processed about 96 million records on a xp pro machine with 2 gigs in about 45 minutes. not bad at all.

The one thing to keep in mind is using / as the directory path instead of \. Thats what hung me up, heeh.
Now I get to work with a database that is 4.3 gigs, and somehow pull out relevant data so that I can win a million bucks!