How to handle a 3 gigabyte CSV file

Jason C. Levine
Jason C. Levine used Ask the Experts™
on
Hi folks,

We've been given a 3.5 gig CSV file that I need to get into a database somehow.  We have access to MySQL and Access 2003 and 2007 but I'm stumped on how to convert a file of this size to a MySQL query and Access seems to not want to open it at all.  

What tools do I need to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2007
Commented:
For Mysql see http://dev.mysql.com/doc/refman/5.5/en/load-data.html No special tools needed.
Jason C. LevineDon't talk to me.

Author

Commented:
Thanks, hernst.   One follow-up, if I may:

Does that command require the table exist or will it create it based on the header row?
>>Does that command require the table exist or will it create it based on the header row? <<

Yes, the table needs to be created first. And no it won't recognize the first row as headers, so you should remove it if that's the case

>>Access seems to not want to open it at all.  <<
Access limit is 2gb, so if you have 3gb file, you are better off with mysql
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jason C. LevineDon't talk to me.

Author

Commented:
>> Access limit is 2gb, so if you have 3gb file, you are better off with mysql

Yep, that's exactly what we're discovering today.  Thanks for the additional info, ralmada.  
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
More technically, Access has a DB file size limit of 2GB.

However, Access can sometimes import csv files larger than 2gb, because of the way Access stores this data.

But yes, 3GB may be pushing this...
;-)

JeffCoachman
Jason C. LevineDon't talk to me.

Author

Commented:
Yeah, well.

3,207,489 rows with 255 columns.  Access is not rated at those sizes.
Jason C. LevineDon't talk to me.

Author

Commented:
So yeah.  It helps to RTFM.  But knowing which part of the FM to R was crucial.  Thanks to hernst for the fast answer and ralmada and boag2000 for filling in some details.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<3,207,489 rows with 255 columns.  Access is not rated at those sizes.>
There is no theoretical limit to the number of rows in Access.
(The Autonumber field (Long Integer) limit is set to 2 billion)

Access can handle up to, ...but not beyond 255 columns.
But here again, because of the datatypes and other properties, it is not practical to come near this limit as other "strangeness" will probably occur.
;-)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
jason1178,

Nice to see you taking a break from dominating the Web zones and actually posting a question...
;-)

Jeff
Jason C. LevineDon't talk to me.

Author

Commented:
Heh.

Hardly "dominating" anything (except for Dreamweaver) these days.  And this one stumped my in-house guys so the "old man" had to step up and show off a little.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial