Solved

PHP MySQL import large CSV file...

Posted on 2004-11-01
2,166 Views
Last Modified: 2008-01-09
Hello,

I need to import a large amount of data into MySQL from a CSV file.  The data in question is a group of 50,000 or more line items, which need to be written to various tables in my database for proper normalization.

I have class strucutures in the form of parents that house collections of line items that are designed well and offer what I need when the user is editing a few of the items, or wants to otherwise manuipulate the data structure(s).

The problem is, that during an import process, the performance is quite piss-poor, if I use my collections in php to write to the database:
i.e.

foreach ($oProject->lineItemCollection as $oLineItem)
   $oLineItem->writeDB();

Without stored procedures yet in MySQL, is there a 'proper' way I could compile an .SQL script in PHP, and then submit it to the MySQL server, which would then execute the 12,000 queries on ONE database access, rather than looping in php 10,000 times with
mysql_query($query[$i]);

Thanks,
Scott


0
Question by:kbach
    2 Comments
     
    LVL 3

    Accepted Solution

    by:
    hmm, ive never tried this before, so use at your own risk, but it seems it would solve your problem... what you could do is make a file... dump in all your 50000 INSERT statements... name it something.sql

    then use php shell_exec() to run command: mysql databaseName -uuser -ppassword < something.sql
    0
     
    LVL 29

    Expert Comment

    by:fibo
    Alskdj80 is pointing you to a good direction: avoid using a php "front" to do that, consider importing DIRECTLY the csvb file, either thru phpmyadmin (should fail miserrably 'cause of CPU time) or into mysql itself. For a similar problem but with quite smaller file (although they were in UTF-8, which "compensates"), direct load i,nto mysql was the only option.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now