?
Solved

Importing local text file to mysql database

Posted on 2008-11-14
7
Medium Priority
?
488 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:bfr-dwn
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:fourice
ID: 22960202
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
 
LVL 11

Accepted Solution

by:
spoxox earned 1500 total points
ID: 22965583
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
 
LVL 3

Author Comment

by:bfr-dwn
ID: 22966336
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Author Comment

by:bfr-dwn
ID: 23020050
Those answers were good, but they needed user interaction.. i guess this problem is pretty much unsolvable?
0
 
LVL 11

Expert Comment

by:spoxox
ID: 23021623
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
 
LVL 6

Expert Comment

by:fourice
ID: 23026987
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
 
LVL 3

Author Closing Comment

by:bfr-dwn
ID: 31516733
This is not full solution, but i think it leads me to right track, so thank you!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question