alternative to LOAD DATA INFILE?

[satre]

Hi Everyone,

 

I have some code that works great on our private server, but now that I'm migrating the site over to a public hosting company, I found that they do not allow the use of the command "LOAD DATA LOCAL INFILE" for security reasons. The file I have has over 3000 lines in it (attached). Can anyone suggest a different way to automatically get this data into a MySQL table? (I can't have our secretary signing on to phpMyAdmin and doing everything by hand b/c I need to reduce her labor as much as possible).

 

Here's the code I'm using, and the file is attached.

//create table in database
$sql1 = "
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL auto_increment,
  `type` text NOT NULL,
  `command` text NOT NULL,
  `value` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1";

$maketable = mysql_query($sql1);
if (!$maketable) {
die('Could not create table: ' .mysql_error());
}
//load data into table
$sql2 = "LOAD DATA LOCAL INFILE 'temp.txt' INTO TABLE `test` FIELDS TERMINATED BY ' ' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n' (`type` , `command` , `value`)";

$loaddata = mysql_query($sql2);
if (!$loaddata) {
die('Could not load data from file into table: ' .mysql_error());
}

 

[attachment deleted by admin]

[straylight]

You would need to create or find a script which can parse your file into mysql queries and run the queries. If I were doing this, I'd create the script using php, as you get easy access to the database that way. There's probably a third party script out there that can do this.

[satre]

I'm writing some clunky code based on a loop after

fgetcsv

or I might try

str_getcsv

Will post here when I finish, or if someone has a better idea, could they please post?...

 

Thanks!

Satre

[satre]

This is clunky, and very very slow compared to LOAD DATA LOCAL INFILE, but it works. With my particular file I had to remove 4 spaces in front of some lines so I had to create the temporary text file that was prepped to create the arrays properly.

 

//set and get file names
$tempfilename = 'temp.txt';

//delete the previously used temporary file if it exists. Note that the '@' symbol will negate any errors if they exist
@unlink($tempfilename);

//remove spaces from beginning of GE command-line file and resave
$file = $filePath; //uploaded file
@chmod($file, 0777);
$file_contents = file_get_contents($file);
$fh = fopen($file, 'r');
$file_contents = str_replace('    ','',$file_contents);

$tempfile = fopen($tempfilename, 'w');
if (!$tempfile) {
exit("<p>Unable to open temporary file ($tempfilename) for " .
	"writing.</p>");
}
fwrite($tempfile, $file_contents);
fclose($tempfile);
chmod($tempfilename, 0777);

//erase table in database
$sql0 = "DROP TABLE  `test`";

$erasetable = mysql_query($sql0);
if (!$erasetable) {
die('Could not erase table: ' .mysql_error());
}

//create table in database
$sql1 = "
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL auto_increment,
  `type` text NOT NULL,
  `command` text NOT NULL,
  `value` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1";

$maketable = mysql_query($sql1);
if (!$maketable) {
die('Could not make table: ' .mysql_error());
}

$row = 1;
$handle = fopen($tempfilename, "r");
while (($data = fgetcsv($handle, 1000, " ", "\"")) !== FALSE) {
   $num = count($data);
   //load data into table
	$sql2 = "INSERT INTO `test` (`type` , `command` , `value`) VALUES ('$data[0]', '$data[1]', '$data[2]')";

	$loaddata = mysql_query($sql2);
	if (!$loaddata) {
		die('Could not load data from file into table: ' .mysql_error());
}

}
fclose($handle);
?>