Save CSV files into mysql database

Question

I have a lot of csv files in a directory. With these files, I have to write a script that put their content in the right fields and tables of my database. I am almost beginner in php language : I have found some bits of code on the Internet. It seems to work, but I am stuck at a stage. Some topics are related on this website, but I did not found the ecat problem.

I have written a php script that permits to get the path and the name of these files. I managed too to create a table whose name depends of each csv (e.g : file ‘data_1.csv’ gives the table data_1.csv in my-sql). All the tables have the three same fields, id, url, value. The last thing to do is to populate these tables, by reading each file and put the values separated by ‘|’ character in the right tables. For example, if a line of ‘data_1.csv’ is

8756|htttp://example.com|something written

I would like to get a record in data_1.csv table where 8756 is in id, htttp://example.com in url field, and something written in value field. I have found the way to read and print these csv with fcsvget function. But I do not know how to make these lines go into the SQL database. Could anyone help me on this point?

Here is my script below

<?php 
ini_set('max_execution_time', 300); 
$dbhost = 'localhost'; 
$dbuser = 'root'; 
$dbpass = ''; 
$conn = mysql_connect($dbhost, $dbuser, $dbpass, true) or die                      ('Error connecting to mysql'); 
$dbname = 'test_database'; 

mysql_select_db($dbname); 


$bdd = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); 
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); 
if (mysqli_connect_errno()) { 
printf("Connect failed: %s\n", mysqli_connect_error()); 
exit(); 
} 
else { 
echo "hello <br>"; 
} 

$dir = 'C:\wamp\www\test';
$imgs = array();

if ($dh = opendir($dir)) {
while (($file = readdir($dh)) !== false) {
    if (!is_dir($file) && preg_match("/\.(csv)$/", $file)) {
        array_push($imgs, $file);
        }
    }

    closedir($dh);
} else {
    die('cannot open ' . $dir);
}
foreach ($imgs as $idx=>$img) {
    $class = ($idx == count($imgs) - 1 ? ' class="last"' : '');
    $filePath=$dir . '\\'. $img;
    $file = fopen($filePath, "r") or exit("Unable to open file!"); 
    $nom = 'FILE: ' . $dir . '\\'. $img;
    echo $nom;
    settype($nom, "string");
    echo '<br>';
    $chemin = '<img src="' . $dir . $img . '" alt="' . 
        $img . '"' . $class . ' />' . "\n";
    echo $chemin;
    $file_name =  basename($filePath);
    $sql = 'CREATE TABLE `' . $file_name . '` (id int(20000), url varchar(15), value TEXT)';
    mysql_query($sql,$conn);
    $handle = fopen($filePath, 'r');
    while (($row = fgetcsv($handle)) !== false) {
    foreach ($row as $field) {
    echo $field . '<br />';
}
}
fclose($handle);

}

echo ("VERIFY"); 
for ($i = 1; $i <= 1682; $i++) { 
    echo ("<br>A : " . $i); 
    $checkRequest= "select * from movies where movieID='". $i."'"; 
    echo ("<br>". $checkRequest); 
    if ($result = $mysqli->query($checkRequest)) { 
    printf("<br> Select ". $i ."returned %d rows.\n", $result->num_rows); 
    if ($result->num_rows == 0) { 

                    echo ("I : " . $i); 
            } 



$result->close(); 
    } 
} 

$mysqli->close(); 

?>

Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is good PDO tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Jul 11 '12 at 12:20

score 12 · Answer 1 · answered Jul 11 '12 at 12:34

12

MySQL provides a wonderful feature that allows you to import a CSV file directly, in a single query.

The SQL command you're looking for is LOAD DATA INFILE

Manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Quick example:

LOAD DATA INFILE 'fileName'
 INTO TABLE tableName
 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
(
field1,
field2,
field3,
@variable1,
@variable2,
etc
)
set
(
field4 = concat(@variable1,@variable2)
);

That's a fairly basic example, but it covers most of what you'd want. The manual page gives full details of how to do some very complex stuff with it.

Hope that helps.

answered Jul 11 '12 at 12:34

SDC

14,192
2
35
48

LOAD DATA INFILE in itself works, but I have a lot of file and I have to embed it in my php script. I have tried to add the code but the rows are not added in my database. But maybe should I open a new topic ? : $sqltwo = "LOAD DATA LOCAL INFILE '$filePath' INTO TABLE $file_name FIELDS TERMINATED BY '|' ESCAPED BY '\\\' LINES STARTING BY '' TERMINATED BY '\\n'(id, url, value)" or die(mysql_error()); – user1474018 Jul 11 '12 at 14:34
`LOAD DATA LOCAL INFILE` ==> reads `$filePath` relative to the SQL server, using SQL's user permissions. `LOAD DATA INFILE` (without `LOCAL`) ==> reads `$filePath` relative to the PHP program, using PHP's user permissions. It works well: I have a PHP program using `LOAD DATA INFILE` to regularly import a 300,000 line data file. Much *much* quicker than reading it through PHP. But yes, if you're still having specific problems with `LOAD DATA INFILE`, a new question might be easier than twenty more comments on this answer. :-) – SDC Jul 12 '12 at 09:00

score 0 · Answer 2 · edited Aug 11 '17 at 11:09

0

after

foreach ($row as $field) {
echo $field . '<br />';

You need to parse the result after ; like:

$pieces = explode(";", $field);

and then insert every piece into the database

$sql = ' INSERT INTO X VALUES ("'.$pieces[0].'","'.$pieces[1].'","'.$pieces[2].'","'.$pieces[3].'","'.$pieces[4].'")';
mysql_query($sql, $conn);

edited Aug 11 '17 at 11:09

jps

20,041
15
75
79

answered Aug 11 '17 at 07:33

Adrian

1

score 0 · Answer 3 · answered May 07 '19 at 16:46

Also mysqlimport can be used

private function runImport($host, $username, $password, $schema, $csvFilePath) {
    $output = array();
    $cmd = "/usr/bin/mysqlimport --host=$host";
    $cmd .= ' --fields-terminated-by=\',\'  --fields-optionally-enclosed-by=\'"\' ';
    $cmd .= ' --user=' . $username . ' --password='. $password;
    $cmd .= " $schema $csvFilePath";

    exec($cmd, $output, $retVal);

    foreach ($output as $line) {
       echo "$line\n";
    }

    echo "\n\nReturn code : $retVal";
}

Save CSV files into mysql database

3 Answers3

Linked