[SOLVED] Importing content of multiple text files

[stargate03]

Hi all

 

Hope some one can help a newbie at mysql

 

i need a way of importing the contents of multiple text files into a table

 

it will basically be importing the same info apart from the content of the text file

 

for example

 

id, category id, description

 

would be

 

NULL, 1, content of text file

 

and the sql needs to run through each text file in a directory and load into a table

 

any known php scripts that do this?  or anyone can put together something that does, if its easy

 

thanks

[Wildbug]

It's a fairly easy thing to do.

 

Read a directory (opendir, readdir) to find the files, read the file(s) (file_get_contents), and run INSERT queries on the escaped data (mysql_real_escape_string, mysql_query).

 

This can also be done at the command line with various system tools, at least in *nix.  Perl's also a good solution, either to write an SQL "dump" file or by using the DBD/DBI package.

[stargate03]

is there any chance someone could write the code, im a total new person at mysql and have no idea how to do this

 

And if you could add descriptions as to what does what that would help a lot

[Wildbug]

If you can't do this or learn to do this on your own, you're not going to be able to do much else, as this is very basic.  It's been done many times, and you can search this site or the web to find examples.  I'll give you some help along the way if you want, but I won't write your code for you.

 

Are you going to be doing this just once, constantly, or occasionally?  Describe your project.  What operating system is your MySQL on?

[stargate03]

ok,  i have now impressed myself lol

 

i can now connect to database

 

read data from a file into a variable

 

pass that variable data into a field in the table

 

 

problems i now have

 

1.  in the text file, the first line is the title and the rest is the article, how can i pass the first line into title field and rest into description field

 

2. no formatting remains when importing , how can i keep new lines

 

3. how do i get it to loop through all the files in the directory - adding data to new records

 

 

many thanks

 

[stargate03]

2. no formatting remains when importing , how can i keep new lines  -OK SOLVED this part using nl2br function

 

still need help on 1 and 3 though

[Wildbug]

1.  There are probably as many ways to acheive this as there are PHP functions, which may make it harder than easier to find one.  You may currently be using file_get_contents() to read a file into a variable, which is fine, but one way to separate the first line from the rest of the file would be to read each line of the file into an array, shift the first element off, and implode the rest of it as the "description" field.  See file(), array_shift(), and implode().

 

3.  It depends.  Try readdir() and opendir() in the directory functions.  Or try glob().  If you need to look for newer files only, the filemtime() function might be useful.

 

 

If you didn't find it already, you'll need to use mysql_real_escape_string() on the data you're putting in your database.  This will escape special characters including quotes, which will break the SQL string you use to insert the data.

[stargate03]

Ok this is going way over my head now

 

i can read a list of files from a directory

 

but how do i put together  a loop

 

so that the content i want is imported into the database for each text file, while there are more files

 

so it loops through each file, importing the data into a new record

 

 

[Wildbug]

Show me the code you're using to read the list of directory files.

[stargate03]

    $list = array();

 

    $directory = opendir($directory);

 

    while ($file = readdir($directory)) {

 

      if ($file != '.' && $file != '..')

      $list[] = $file;

    }

 

 

    closedir($directory);

 

    return $list;

 

[Wildbug]

Pretty good.  You could do two things.  You could read the files while you're finding them in the loop that you already have, or you could use a foreach() loop with the array of files that you return from this code.

 

 

(See also the glob() function.)

$list = glob('directory/*.txt'); // or *.*

(Also note that you should be using the !== FALSE comparison in your while loop; see the PHP manual page for readdir() -- there's a warning and example.)

[stargate03]

COOL  this is getting good

 

ok 95% of the way there

 

i now can read the files from a directory into an array

 

loops through the array of files

 

use fgets(); to get the first line of the text in the file to be used as a title

 

use nl2br(file_get_contents()); to add text from file to be used as the article body and keep the line breaks

 

pass the information from these into the database correctly

 

ok final thing  - how can i get the second, third and fourth lines of the article to use as a summary ?

 

 

 

 

 

[Wildbug]

file_get_contents() is a good way to put a file into a single variable, but since you want to access several of the lines individually, the file() function, which reads each line into an array, might be a better choice, since you could then use rtrim($xx[0]) for the first line/title and nl2br(implode(array_slice($xx,1,3))) for the summary.

[stargate03]

Hi again

 

Definitely can not get this last bit working

 

This is what i have so far that works in getting title and description

 

but can not get summary, anything i have tried has ended up with the summary variable having no content

 

I just want it to pull in any data in lines 3 and 4 of the text file

 

--------------------

 

<?php

 

mysql_connect("localhost", "username", "password") or die(mysql_error());

echo "Connected to MySQL<br />";

mysql_select_db("database name") or die(mysql_error());

echo "Connected to Database";

 

 

function ListFiles($dir) {

 

    if($dh = opendir($dir)) {

 

        $files = Array();

        $inner_files = Array();

 

        while($file = readdir($dh)) {

            if($file != "." && $file != ".." && $file[0] != '.') {

                if(is_dir($dir . "/" . $file)) {

                    $inner_files = ListFiles($dir . "/" . $file);

                    if(is_array($inner_files)) $files = array_merge($files, $inner_files);

                } else {

                    array_push($files, $dir . "/" . $file);

                }

            }

        }

 

        closedir($dh);

        return $files;

    }

}

 

 

foreach (ListFiles('/path to text files directory') as $key=>$file)

{

 

$filename = "$file";

$f = fopen($filename, 'r');

$line = fgets($f, 1024);

 

$smeg = nl2br(file_get_contents("$file"));

 

 

 

echo "Data Inserted!";

 

 

} 

 

?>

[stargate03]

oops updated with full script

 

Hi again

 

Definitely can not get this last bit working

 

This is what i have so far that works in getting title and description

 

but can not get summary, anything i have tried has ended up with the summary variable having no content

 

I just want it to pull in any data in lines 3 and 4 of the text file

 

--------------------

 

<?php

 

mysql_connect("localhost", "username", "password") or die(mysql_error());

echo "Connected to MySQL

";

mysql_select_db("database name") or die(mysql_error());

echo "Connected to Database";

 

 

function ListFiles($dir) {

 

    if($dh = opendir($dir)) {

 

        $files = Array();

        $inner_files = Array();

 

        while($file = readdir($dh)) {

            if($file != "." && $file != ".." && $file[0] != '.') {

                if(is_dir($dir . "/" . $file)) {

                    $inner_files = ListFiles($dir . "/" . $file);

                    if(is_array($inner_files)) $files = array_merge($files, $inner_files);

                } else {

                    array_push($files, $dir . "/" . $file);

                }

            }

        }

 

        closedir($dh);

        return $files;

    }

}

 

 

foreach (ListFiles('/path to text files directory') as $key=>$file)

{

 

$filename = "$file";

$f = fopen($filename, 'r');

$line = fgets($f, 1024);

 

$smeg = nl2br(file_get_contents("$file"));

 

mysql_query("INSERT INTO article(article_id, category_id, time, title, author, summary, text) VALUES(NULL, 7, UNIX_TIMESTAMP(), '$line', 'unknown', '$summ', '$smeg') ")

or die(mysql_error());

 

echo "Data Inserted!";

 

 

}

 

?>

[Illusion]

try this

 

to get summary

 

$summ=nl2br(implode(preg_split('\n',$smeg,3)));

 

and the query should be

 

INSERT INTO article(article_id, category_id, time, title, author, summary, text) VALUES(NULL, 7, UNIX_TIMESTAMP(), '%s', 'unknown', '%s', '%s', mysql_real_escape_string($line),

            mysql_real_escape_string($summ), mysql_real_escape_string($mesg));

         

 

 

[Wildbug]

Also note that, instead of reproducing the leading text of the article twice, once in the text and once in the summary, you could just write a query that pulls out

the first three lines of the content when you want a summary.

[stargate03]

I get the following when trying the solution from Illusion

 

Warning: preg_split() [function.preg-split]: Delimiter must not be alphanumeric or backslash in /home/master01/public_html/articleimporter.php on line 40

 

Warning: implode() [function.implode]: Argument to implode must be an array. in /home/master01/public_html/articleimporter.php on line 40

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Planning for Atkins ), mysql_real_escape_string(), mysql_real_esc' at line 1

[Wildbug]

Change '\n' to '/\n/' in the preg_split function.

 

(Or change the preg_split to:

explode("\n", $smeg, 3)

  )

[stargate03]

ok one problem solved ty

 

still left with

 

Connected to MySQL

Connected to Database

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Planning for Atkins ), mysql_real_escape_string(Planning for Atki' at line 1

[Wildbug]

Illusion's query string wasn't quite complete; see the sprintf() function for details if you're not familiar with it.  This should work:

 

<?php

$query = sprintf("INSERT INTO article(article_id, category_id, time, title, author, summary, text) VALUES(NULL, 7, UNIX_TIMESTAMP(), '%s', 'unknown', '%s', '%s')",
mysql_real_escape_string($line),
mysql_real_escape_string($summ),
mysql_real_escape_string($mesg)
);

mysql_query($query) or die(mysql_error());

?>

[stargate03]

Getting closer

 

Just let me check with you that the code is correct, because it puts the whole of the content into the summary field and not just a few lines

 

foreach (ListFiles('/home/master01/public_html/art') as $key=>$file)

{

 

$filename = "$file";

$f = fopen($filename, 'r');

$line = fgets($f, 1024);

$smeg = nl2br(file_get_contents("$file"));

$summ=nl2br(implode(preg_split('/\n/',$smeg,3)));

 

$query = sprintf("INSERT INTO article(article_id, category_id, time, title, author, summary, text) VALUES(NULL, 7, UNIX_TIMESTAMP(), '%s', 'unknown', '%s', '%s')",

mysql_real_escape_string($line),

mysql_real_escape_string($summ),

mysql_real_escape_string($smeg)

);

 

mysql_query($query) or die(mysql_error());

 

echo "Data Inserted!";

 

 

} 

 

[Illusion]

so you need to examine the contents of $mseg first and then get $summ by writing the code according to that , did u examined the $mseg did it contain any new lines.

[Wildbug]

Actually, it isn't correct, all the preg_split line does is split the content and then stitch it back together again, so that's why you're getting all the data.

 

Try using the file() function to get your file content.  It'll allow you to control the data line-by-line with less fuss.

 

<?php

// Replace the code between the foreach and $query with this.

$contents = file($file);
$line = rtrim(array_shift($contents));
$summ = nl2br(implode('',array_slice($contents,0,3)));  // First three lines
$smeg = nl2br(implode('',$contents));

?>

 

Let me reiterate my suggestion regarding your database structure.  If your summary is only the first few lines of your article's content, then don't repeat the same information in two columns.  Get rid of the summary, and just create a summary on the fly when you need it by only selecting a few lines of the content.

 

SELECT SUBSTRING_INDEX(content, "\n", 3) AS summary FROM article;

[stargate03]

Hi there

 

yes  $smeg contains the full text from the file

 

but $summ also contains the full text from the code

 

so i guess i need some way of limiting the amount of data being put in $summ