CSV Upload with PHP/MySQL

Question

I have been trying to get a csv file uploaded into my database now for 1 week, i have read umpteen tutorials and have no idea what i am doing wrong, this is the simple code that I am too stupid to get right. Any help will be golden! :)

if(isset($_FILES['file'])){

    $csv_file = $_FILES['file']['name'];

  $sql = <<<eof
    LOAD DATA INFILE '$csv_file'
     INTO TABLE test_csv
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (name,house,po)
eof;



     $result = $dbh->query($sql); 



}

echo $csv_file .' has successfully been loaded';

?>
<!DOCTYPE html>
<html>
<head>
  <title>CSV to MySQL Via PHP</title>
</head>
<body>
  <form enctype="multipart/form-data" method="POST">
    <input name="file" type="file">
    <input type="submit" value="Upload">
  </form>
</body>
</html>

Have you tried running the same query through an SQL console rather than via PHP? Then you could determine whether it's an issue with your code or your query. — James Baker
– James Baker, Commented Jan 24, 2014 at 12:13
what is your csv structure looks like? and what does your db table look like? — Latheesan
– Latheesan, Commented Jan 24, 2014 at 12:14
@JamesBaker just told me access denied. is this my blummin read write access on the user causing me issue then? — Brobina
– Brobina, Commented Jan 24, 2014 at 12:18
Possibly, or perhaps the user permissions for the SQL user you are using. — James Baker
– James Baker, Commented Jan 24, 2014 at 12:21

Latheesan · Accepted Answer · 2014-01-24 12:28:39Z

When ever I had to import a CSV into database table, i've always written my own csv parser / importer. It's quite simple.

Here's an example.

test.csv

Firstname,Lastname,Age
"Latheesan","Kanes",26
"Adam","Smith",30

test.php

<?php

// Mini Config
$csv_file       = 'test.csv';
$delimiter      = ',';
$enclosure      = '"';
$skip_first_row = true;
$import_chunk   = 250;

// Parse CSV & Build Import Query
$import_queries = array();
$first_row_skipped = false;
if (($handle = fopen($csv_file, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, $delimiter, )) !== FALSE) {
        if ($skip_first_row && !$first_row_skipped) {
            $first_row_skipped = true;
            continue;
        }
        list($firstname, $lastname, $age) = $data;
        $import_queries[] = "INSERT INTO myTable (firstname, lastname, age) VALUES ('$firstname', '$lastname', $age);";
    }
    fclose($handle);
}

// Proceed if any data got parsed
if (sizeof($import_queries))
{
    foreach(array_chunk($import_queries, $import_chunk) as $queries)
    {
        $dbh->query(implode(' ', $queries));
    }
}

?>

The parsed queries will look like this (if you print_r it):

Array
(
    [0] => INSERT INTO myTable (firstname, lastname, age) VALUES ('Latheesan', 'Kanes', 26);
    [1] => INSERT INTO myTable (firstname, lastname, age) VALUES ('Adam', 'Smith', 30);
)

You have two option for the actual importing into the db:

Build a collection of import sql query and execute it in a batch (array_chunk) - this means less queries against your db. However as you can see, im not checking the values from the CSV - i.e. i trust my data source and not escaping anything - a bit dangerous...
You execute the query, as soon as you built it with escaping the values - small drawback is that it will execute one query per row in csv....

Hope this helps.

Collectives™ on Stack Overflow

CSV Upload with PHP/MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related