Batch Insert Text Files as Content Items

[saratoga_]

I have 500+ text files I'm looking to batch insert into the Joomla MySQL database.  I'd like for them to appear in a specific category and section, with the file name serving as the title and the content of the text file as the fulltext.

It looks like there's no component to do this, so I'm looking for help to automate the process.  Has anyone ever had to write a PHP or other script to automate a process like this?  Unfortunately, I have no experience writing a MySQL query that would iterate over files in a directory.  Any help would be appreciated!

[radiant_tech]

I wrote something similar to this for a publishing company that needed to transfer several years worth of articles into a mySQL database for another CMS.  It helped a great deal that all of the articles were saved into year/month/ subdirectories on their server (the year and month designations were used in the category structure.

The interface allowed the publisher to view all files within each subdirectory and verify whether the files had been processed into the database.  If not, then the full batch of files in the currently viewed subdirectory was "processed" and stored into the database.  These were HTML files that needed a good bit of cleanup before storing (replacing deprecated font tags with H2, correcting image paths, etc.).

As this was to be a one-time operation, we did not bother to build it into the framework of the CMS.

I've attached a sample shot of the user interface.

Edit:  Forgot to mention that the drop-down boxes you'll see there were used to assign each article to a "Subject" -- in Joomla! I guess that would be a Section. (Still getting used to J!)

[saratoga_]

radiant_tech, could you share your mysql insert query for the content?  I have written a script to parse out the information I need from the folder of text files I have, but they're not properly inserting into my database (and not throwing back any errors!).

Thanks!

[saratoga_]

Well, I managed with the help of a good friend to figure out how to automate this.  Here's the script I used with a bit of explanation

Code: Select all

<?php
mysql_connect("yourhost", "youruser", "yourpassword") or die(mysql_error());
mysql_select_db("yourdb") or die(mysql_error());
/*All of the text files are in a directory named '2000-2006'  This next part loops over all those files*/
if ($handle = opendir('2000-2006')) {
 echo "Directory handle: $handle\n";
 /*loop over the directory, opening each text file and assigning its contents to $data and its file name to $file */
 while (false !== ($file = readdir($handle))) {
  $openfile = "2000-2006/$file";
  $fp = fopen($openfile, "r");
  $data = fread($fp, filesize($openfile));
  fclose($fp);
 /*escapes those nasty quotes in the text files*/
  $data = addslashes($data);
/*turns out the trick here is to assign the id to Null.  I've assigned $file to the title and $data to fulltext, you'll need to make changes to this INSERT statement as you need, probably changing things like catid, sectionid, created_by, etc.  Make sure to define yourdb in this INSERT statement, or assign it a variable!*/
  $sql = "INSERT INTO `yourdb`.`jos_content` (
`id` ,
`title` ,
`title_alias` ,
`introtext` ,
`fulltext` ,
`state` ,
`sectionid` ,
`mask` ,
`catid` ,
`created` ,
`created_by` ,
`created_by_alias` ,
`modified` ,
`modified_by` ,
`checked_out` ,
`checked_out_time` ,
`publish_up` ,
`publish_down` ,
`images` ,
`urls` ,
`attribs` ,
`version` ,
`parentid` ,
`ordering` ,
`metakey` ,
`metadesc` ,
`access` ,
`hits`
)
VALUES (
NULL , '$file', '', '', '$data', '1', '6', '0', '25', '0000-00-00 00:00:00', '62', '', '0000-00-00 00:00:00', '0', '0', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '', '', '', '1', '0', '0', '', '', '0', '0'
);";
  $result = mysql_query($sql);
/*spits out the contents of $file and $sql for error checking, and displays any errors*/
  echo "File: $file \n"; 
  echo "Sql: $sql\n";
  if (!$result)
  {
    echo ("Invalid mysql: ".mysql_error());  }
 }
 closedir($handle);
}
echo "Done!";
?>

[O3car]

Would this MySQL-query be applicable on the Joomla 1.5.1 table?

[radiant_tech]

saratoga, glad to see you found a friend to help with the sql statement.  I got preoccupied with other things last night and didn't get back to the forum.

O3car,
The fields listed in the query match those in the jos_content table for 1.5.1 with two exceptions:  alias and metadata have been left out.

BIG CAUTION however, please don't assume that the method being used here is the way one should add article data into the database.  This is just a quick and dirty method to serve saratoga's purposes.  Joomla! has a GREAT framework for adding data to tables in a much more secure manner.  If someone were building a component to handle batch file uploads of article data, I would definitely take the time to do that the "Joomla!" way.

Regards,

[saratoga_]

The installation I'm using is Joomla 1.0.13 Stable, because I haven't taken the time to upgrade.  That INSERT statement should work no matter what, as long as the ID is set to NULL.  I originally tried doing this without the ID set to NULL, only inserting the title, fulltext, catid, sectinid, and created_by but it didn't work.  Looks like setting ID to NULL was the trick, and each item I added was auto incremented.  I've been testing this morning and it looks like the import went as I wanted.  But like radiant_tech mentioned, I'd only use this if you were in a situation like mine (a massive number of files to quickly import into Joomla).

I literally spent all day yesterday revising that INSERT statement and my friend spent a few hours too.  We can't seem to understand why ID had to be set to NULL!  But we're just happy to have it done!