Thursday 14 November 2013

Import csv to MySQL with php

I have small project at work where I need to import data from over 80 excel document and from more than one sheet from each into database. And I also needed to enter data from the web form to the database at the same time to identify each port with equipment and location. After converting sheet from .xls to .cvs I import the file by upload it trough the web form.
Not very hard task but I had some issues that I came a cross.
There is a simple method to import a file to database. From the command line I would do this.
mysql > LOAD DATA INFILE 'filename' INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (field1,field2,field3);
Ok, this was easy but. In my case it seams that the file neaded to be in /var/lib/mysql/databasename for this command to work and I did not figure out how to get the file there when I uploaded it with php, guess it's permission issue and i'm not going to change them. Adding LOCAL to the command is the way to import the file from my directory, but it considered an administrative task and it returned error.

In the connection parameters I had to add "false,128" to get it to work.
$connect = mysql_connect("localhost","user","pass",false,128);

mysql_select_db("database",$connect);

 mysql_query("LOAD DATA LOCAL INFILE 'upload/$filename' INTO TABLE database FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (field1,field2,field3) set field4='value1',field5='value2';") ;
Now it works like a charm.
--cheers

See also how to export MySQL data to csv file