Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

General Discussion

nicholas maddren
nicholas maddren
12,793 Points

MySQL Database & Scheduled CSV Imports?

Hello community, I can't find an answer anywhere on this. I've searched Treehouse and can't find a tutorial that shows me how to import a CSV file to a MySQL database daily. I'd need it to replace all the current data. Does anybody know of any solutions for this I've found plenty of tutorials on importing the file manually once but not automated?

Thanks :)

3 Answers

Hey! What's the context behind your automatic upload? i.e. where's the file coming from etc..

nicholas maddren
nicholas maddren
12,793 Points

It is uploaded onto my server in the public_html daily :)

Ah okay, well I'm not pro, I've only worked with the manual uploads you mentioned.

So if you have a specific folder the upload is sent to, maybe you could create a function that will search for new files inside that folder? Make sure you add a lot of validation too, i.e. if the file name increments by 1 for each upload, you could write the function to only work with the next file/increment. The trouble is.. php would only kick on when a page is requested. You could run the 'check folder and upload' function on every page request - that seems a little messy, but it would work.

Maybe this might help http://au2.php.net/manual/en/function.file-exists.php

Another point of validation - you could receive the file into a temporary folder, check it - and only if it's acceptable, move it into the validated folder and continue with the overwrite: http://www.php.net/manual/en/function.move-uploaded-file.php

You said you've looked into it already, but I feel I should suggest anyway: Make sure you use PDO and a try-catch block because it protects you against SQL injections.. Other php mysql functions (i think) are now considered pretty dated and insecure.

If it helps, here's the block I started with where the $files param is the $_FILES array.

As I said, I'm not 100%, just throwing information at you :-p

Database file

<?php

try {
    $db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";port=" . DB_PORT,DB_USER,DB_PASS, 
                                       array(
                                        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
                                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                                       )
                                  );
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES 'utf8'");
} catch (Exception $e) {
    echo "Could not connect to the database.";
    exit;
}

Function

<?php
function add_csv_file_to_db($files) {

    $valid = validate_csv_upload($_FILES);

    if ($valid) {
        $name = $files['file']['name'];
        $file = ROOT_PATH . 'admin/uploads/' . $name;   
        $fieldseparator = ","; 
        $enclosedseparator = '"';
        $escapedseparator = '\\';
        $lineseparator = "\n";
        $databasetable = "price_import";


        require(ROOT_PATH . "inc/database.php") ; //attempt connection if valid

        try {
            $affectedRows = $db->exec("
            LOAD DATA LOCAL INFILE ".$db->quote($file)."REPLACE INTO TABLE `" . $databasetable . "`
            FIELDS TERMINATED BY ".$db->quote($fieldseparator)."
            ENCLOSED BY ".$db->quote($enclosedseparator)."
            ESCAPED BY ".$db->quote($escapedseparator)."
            LINES TERMINATED BY ".$db->quote($lineseparator));
        } catch (Exception $e) {
            var_dump($db->errorInfo()); //debugging only, make sure you remove this if live.
//          echo 'there was an error';
            exit;   
        }
        $success_message[] = "Loaded a total of $affectedRows records from this csv file.\n";
        setSuccessMessage($success_message); //sets session var
        unlink($file); //deletes file
        return true;
    } else {
        return false;
    }
}
?>
nicholas maddren
nicholas maddren
12,793 Points

Thanks for that! Some of it is Chinese to me but I'll have a good look and see what I can do. It's a starting point ha thanks for your time :)

Haha, sorry man. The try section of the

add_csv_file_to_db($files)

Is just the same as if you're adding the csv through phpMyAdmin (again my apologies if you already know all this!).

In phpMyAdmin, click on any table in any database. Then click import at the top. Add a dummy file - it doesn't matter what - just not a csv file because you want it to fail!! Scroll down and select CSV (USING LOAD DATA). The page should re-render to show boxes for 'enclosed by' and 'escaped by' etc. Leave them as they are for this test and hit 'go'. You should get a nice big error message, but also the SQL code. This code might help you decrypt the try block.

Good luck.. It sounds pretty tricky ;)