Import multiple text files to database table cells using PHP

Question

I'm trying to transfer the contents of multiple text files into a MySQL. These files are sequentially named 1.txt, 2.txt, 3.txt ... 13381.txt. Basically, each file is an HTML document without all the headers, meta, etc. that's been stripped away and only the content is left.

I've also made an table with a columns ID and CONTENT. ID is auto-incrementing. I'd like the contents of the files to go into the CONTENT column's cells like this:

1.txt -> row 1
2.txt -> row 2
3.txt -> row 3
etc.

Searching around only leads me to importing data from CSVs and other tabular forms of data. I could always just open each file and copy-paste from there into something like phpmyadmin but that seems like a tedious thing to do given that there's thousands of data files to go through.

I was wondering if there was an easy way to do this using PHP and some kind of recursive loop that gets file contents and then UPDATEs the database. I've experimented a bit but I've seem to come against a wall beyond my mediocre programming skills. Anyone care to help? Thanks in advance :)

Kuya · Accepted Answer · 2015-09-03 07:31:57Z

2

Something like this using PDO...

<?php
$host = 'localhost'; $db = 'database-name'; $user = 'database-user-name'; $pw = 'database-password';
$conn = new PDO('mysql:host='.$host.';dbname='.$db.';charset=utf8', $user, $pw);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$i = 0;
do {
    $i = $i+1;
    $txt = file_get_contents("$i.txt");

try {
    $sql = "INSERT INTO table (CONTENT) VALUES (:txt)";
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(':txt', $txt, PDO::PARAM_STR);
    $stmt->execute();
} catch (PDOException $e) {
    die("Could not complete the insert for ".$i.".txt " . $e->getMessage()); // this will tell you which of your text files caused the error
}

} while ($i <= 13381); // close for loop

echo('All Done!');
?>

Hope this helps.

edited Sep 3, 2015 at 7:31

answered Sep 3, 2015 at 6:46

Kuya

7,3204 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

doraemond Over a year ago

Hi! I tried this out and get the error "Fatal error: Call to a member function prepare() on a non-object" on the line $stmt = $conn->prepare($sql);. Did I forget to replace something? Thanks

Kuya Over a year ago

Probably your connection file or database connection code. This should be placed above your insert code. See my edited answer

doraemond Over a year ago

Got it, thanks! It'll probably take a while to finish but are there any pitfalls to this method? Like memory usage or execution time limits? Additionally, I didn't even know you could connect to SQL using your method. I just normally used mysql_connect().

Kuya Over a year ago

mysql_connect() has been deprecated as of PHP version 5.5 and will be totally removed in version 7. It's best to use PDO. More information can be found here

manjeet · Accepted Answer · 2015-09-03 06:44:29Z

0

    <?php
    try {
        $instance = new PDO("mysql:host=127.0.0.1;dbname=nameofdatabase;charset=utf8", $user, $pass, array(PDO::ATTR_PERSISTENT => true));
        $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


    $stmt = $instance->prepare('INSERT INTO tablename SET CONTENT=:con');

    for ($i = 1; $i <= 13381; $i++)
    {
    $con = file_get_contents("/path/to/files_dir/$i.txt");
    $stmt->bindValue(':con', $con, PDO::PARAM_STR);
    $stmt->execute();
    }
}
    catch(PDOException $ecx) {}

OR you can use mysql LOAD DATA INFILE statement in php loop

answered Sep 3, 2015 at 6:44

manjeet

1,51710 silver badges15 bronze badges

1 Comment

Kuya Over a year ago

Why are you recommending the use of deprecated code? mysql has been deprecated since PHPv5.5

Collectives™ on Stack Overflow

Import multiple text files to database table cells using PHP

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related