Importing local text file to mysql database

Hi!

I have program that logs usage of certain programs to text file.
I would need to import these files to mysql database, and it should be done without user interaction..
What code should i use? I assume some php solution would be best, but i think php cannot read
files from local machine, so it needs to upload the file to the server first?

The program that makes the log file is just .bat file, so i can format the data anyway i like, so that is
not a problem.. lets assume it would be something like this:
Program name, date, time

thank you!

edit: The automation thing i think i can figure out somehow, but if you have good ideas on this(winodws scheduler or something), please tell me. I would not like to install any additional programs if possible.
LVL 3
bfr-dwnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fouriceCommented:
I think something like this should do the trick. Of course you will need to edit some variables, like filenames, db connection info etc.
<?php
if($_FILES['userfile']['tmp_name'] != "")
{
	// The directory you wish to upload to needs to be writable!
	$uploaddir = './';
	$uploadfile = $uploaddir . basename($_FILES['userfile']['name']);
 
	if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile))
	{
		//echo "File is valid, and was successfully uploaded.<br>";
 
		// Make db connection
		$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
		if (!$link)
		{
			die('Could not connect: ' . mysql_error());
		}
 
		$db_selected = mysql_select_db('your_db_name', $link);
		if (!$db_selected)
		{
			die ('Can\'t use your_db_name : ' . mysql_error());
		}
 
		// Read entire file into array
		$lines = file($uploadfile);
 
		// Loop through all lines
		foreach ($lines as $line)
		{
			// FORMAT OF EACH LINE SHOULD BE: Program name;date;time
			$explodedLine = explode(";", $line);
			$sqlInsert = "INSERT INTO table_name (id, program_name, program_date, program_time) VALUES(0, '" . $explodedLine[0] . "', '".$explodedLine[1]."', '".$explodedLine[2]."')";
			// Insert new record (id is an autoincrement field in my example)
			mysql_query($sqlInsert, $link);
		}
 
		// Close db connection
		mysql_close($link);
 
		// Remove the file from the server
		unlink($uploadfile);
	}
	else
	{
		echo "Upload failed<br>";
	}
}
?>
<form action="yourfile.php" method="post" enctype="multipart/form-data">
	<table border="0" align="center" cellpadding="0" cellspacing="0">
		<tr>
			<td>
				<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
					<tr>
						<td>Select file
						<input name="userfile" type="file" id="userfile" /></td>
					</tr>
					<tr>
						<td align="center"><input type="submit" name="Submit" value="Upload" /></td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</form>

Open in new window

0
spoxoxCommented:
I didn't notice any web requirement.

You (or windows scheduler) can execute a PHP script from the command line this way:

PHP ee.php

The attached snippet will open the file and parse it into MySQL inserts. The input file format is important; if it deviates from expectations, then further parsing will be necessary for each record (between reading $rec and building $query).

<?php
 
//MySQL init
$username = "eeid";
$password = "eepw";
$hostname = "localhost";    
$dbconn = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
$database = mysql_select_db("eedb",$dbconn);
 
//open the file
$fp = fopen('ee.dat', 'r');
 
//file opened ok?
if ($fp) {
 
//loop through file records
//note: assuming record format:
//'field 1 value', 'field 2 value', 'field 3 value'
// quotes (for text fields) and commas are important
  while (!feof($fp)) {
  
  // get next record, up to 4096 characters
    $rec = fgets($fp, 4096);
  
  // build SQL insert query
    $query = "INSERT INTO aTable(col1, col2, col3) VALUES (" . $rec . ")";
    
  // execute SQL insert query
    $result = mysql_query($query);
    
  // verify insert worked; halt otherwise with error message
    if (!$result) {
        $message  = 'Invalid query: ' . mysql_error() . "\n";
        $message .= 'Whole query: ' . $query;
        die($message);
    }
    
  } // end while
}  // end if
 
 
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfr-dwnAuthor Commented:
Ok, i will test these solutions monday at work.. meanwhile, please note
that the client machine does not have php or any server applications installed. I'll try to clear this a bit:

There is one client machine and one web server. Web server has php enabled, and the mysql database is located at the server too.

Mission is to get contents of text file from local machine imported to msql database which is located at server machine. This should happen without user interaction, once a day.

I'm not sure if it is actually possible to upload with php without user interaction for security reasons.. i'll try your solutions and see. One thing i thought of was to use curl to upload the file first, or possibly some kind of small command line mysql client which could do the importing somehow without using php at all, but i have not found such client.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

bfr-dwnAuthor Commented:
Those answers were good, but they needed user interaction.. i guess this problem is pretty much unsolvable?
0
spoxoxCommented:
Noooooo...

The solution I offered above (22965583) can be incorporated into another (or the same?) .BAT file and scheduled. User interaction to create and schedule the initial process; then it runs according to the schedule.
0
fouriceCommented:
It's also possible to create a cron job that automatically calls the script. Of course, the script need to be altered a bit since you won't need the form anymore etc.
0
bfr-dwnAuthor Commented:
This is not full solution, but i think it leads me to right track, so thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.