Import large csv file to mysql database using php

Question

I have a very large CSV file (150 MB). What is the best way to import it to MySQL? I have to do some manipulation in PHP before inserting it into the MySQL table.

Institutionalize

check my solution at https://mcmap.net/q/501299/-read-large-data-from-csv-file-in-php-duplicate which includes some comparisons also – Puppis

Answer 1

You could take a look at LOAD DATA INFILE in MySQL.

You might be able to do the manipulations once the data is loaded into MySQL, rather than first reading it into PHP. First store the raw data in a temporary table using LOAD DATA INFILE, then transform the data to the target table using a statement like the following:

INSERT INTO targettable (x, y, z)
SELECT foo(x), bar(y), z
FROM temptable

Katzman

+ 1 for LOAD DATA INFILE. If you use phpmyadmin (obviously PHP :) you can easily compare several methods, most popular: SELECT INTO and LOAD DATA INFILE with all their different args and parameters. You will what´s best for you. Most likely it's LOAD DATA INFILE – Fastigium

Depending on the amount of manipulation he has to do to the data it might be more feasible to read it into php first and insert one row at a time. If the amount of manipulation is significant it is more efficient to do it in a script anyway and then he has two operations. 1. with LOAD DATA INFILE and then 2. with UPDATE TABLE queries. – Woehick

Answer 2

I would just open it with fopen and use fgetcsv to read each line into an array. pseudo-php follows:

mysql_connect( //connect to db);

$filehandle = fopen("/path/to/file.csv", "r");
while (($data = fgetcsv($filehandle, 1000, ",")) !== FALSE) {
    // $data is an array
    // do your parsing here and insert into table
}

fclose($filehandle)

Woehick