Upload a csv and import it into the mysql database

Question

I am writing the following script up import a csv file into a mysql database. The user has to log in first and their security rights to upload are checked by another file, if this evaluates to true, the file can then be uploaded by the file up-loader which will only allow csv's to be uploaded and then the following part imports the file.

Am I using the correct code for this below ?, also if the csv layout is wrong is it possible to refuse the import ?, im just concerned that this could go badly wrong if the csv is formatted correctly. This feature has been requested as a requirement for this project so I am just trying to make it as idiot proof as possible for them.

<?php
$uploadedcsv = './uploads/'.$filename.'';
$sql = 'LOAD DATA LOCAL INFILE "'.$uploadedcsv.'" INTO TABLE '.$table.' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" IGNORE 1 LINES' or die(mysql_error());
?>

Answer 1

Unless you are 100% rely on the user and it seems that you are not, it will be good not to blindly import uploaded file, but first to check if it's correct.

To do it, you'll need to open and to read the file, e.g. with fgetcsv and to check the data consistency line-by-line.

---

You can find a lot of examples on the web.

Here are just some:

• http://www.damnsemicolon.com/php/php-upload-csv-mysql
• http://www.programmingfacts.com/import-csvexcel-data-mysql-database/

Answer 2

It can be done via MySQL, but by default LOAD DATA INFILE expects a different format than CSV. From the documentation:

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

The documentation notes, for when dealing with CSV files:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Which is an example of dealing with comma separated values enclosed by "" and line-separated by \r\n

To use this to import a file, make sure your statement matches the CSV format of your upload files and you can import via this manner.