Inserting text files in to the databse - PHP, MySQL(PHPMyAdmin)

I have 3 text files at three different locations in the same drive, these text files contains some alerts as a resultant of few scripts. 1 script generates 1 text files.

Below is the format of alerts in the text file -

4/10/2010 4:05:04 AM,ServerName,0
4/10/2010 4:05:04 AM,ServerName,0
4/10/2010 4:05:04 AM,ServerName,0
and so on

I have a table in MySql Database(PHPMyAdmin), say DB Name - dbo_alert and Table Name - tab_alert
there are 3 Columns in the table - Server_Name, Date_Time, Status

Now i have to create a script which suppose to fetch all the data from the text file and insert the same data in to the database under relevant column.

Who is Participating?
Om PrakashConnect With a Mentor Commented:
Please check the similar example
CREATE TABLE `friendstel` ( 
`id` bigint(6) NOT NULL auto_increment, 
`tel` varchar(10) NOT NULL default '', 

$sql_static = 'inSERT inTO friendstel (id, tel) VALUES(NULL, '; 
$fp = fopen ("mitch888.txt", "r"); 
while (!feof ($fp)) {  
	$content = fgets( $fp, 4096 );  
	$sql = $sql_static . $content .')';  
	// echo "$sql<br />";  
fclose ($fp); 

Open in new window

phpsalesConnect With a Mentor Commented:
Change text file name and MySQL information below as needed.

//connect to mysql
$conn = mysql_connect("host","username","password") or die(mysql_error());
mysql_select_db("databasename",$conn) or die(mysql_error());

//open file
$filepath = "/tmp/inputfile.txt";
$handle = fopen($filepath, "rb");
	//for each line in file
		//read line into buffer
        $buffer = fgets($handle, 4096);
		//split by comma into array
		$data = explode(",",trim($buffer)); //e.g. array("4/10/2010 4:05:04 AM","ServerName","0");
		//build SQL
		$sql = sprintf("INSERT INTO tab_alert (Date_Time,Server_Name,Status) VALUES ('%s','%s','%s');", $data[0], $data[1], $data[2]);
		//execute query or die with error message if issue
		$result = mysql_query($sql, $conn) or die(mysql_error());

Open in new window

flytox06Connect With a Mentor Commented:

If you can run LOAD DATA INFILE statement somehow (shell script or through PHPMyAdmin) I'd advise you to do it. Try something like :

LOAD DATA INFILE '/path/to/yourfile.csv' INTO dbo_alert.tab_alert (Server_Name, Date_Time, Status) FIELDS TERMINATED BY ',';

of course you can run it on PHP. It's the fastest way to load external data. That will require you have FILE grants or you will need LOCAL keyword between LOAD and DATA.

Check documentation.

Hope that helps.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Follow below steps to import your text file in database using phpmyadmin >

- First rename txt file to csv and open in excell
- Convert date format from 12 hr to 24 hour to remove AM/PM
- Save file and confirm format by opening it again in notepad
- Open phpMydmin > Database
- Open table and select Import link
- Browse and Select your text file for "Location of the text file"
- Select "CSV using LOAD DATA" in "Format of imported file" option
- Enter "," at the place of ";" in "Fields terminated by"
- In "Column names" enter "Date_Time,Server_Name, Status"
- uncheck "Use LOCAL keyword"
- GO

This is done
pradeep_bansalAuthor Commented:
Thanks paresh,

But these text files generated by script on daily basis. There is script (written in vbscript) which is scheduled to run in every 10 minutes, this script use to write the results on a text file or you can consider it as a log file, every time the script runs that text file get updated and at the end of the day text file suppose to have all the alerts for the day.
Currently i am moving this text file from one place to other(reository) but now there is a need to insert that data everyday into the database.
pareshprajapatiConnect With a Mentor Commented:
For this you can do following

Make generated file accessible to php program

Solution1 :
Generate a cronjob (Scheduled Tasks) for php program
This program will read text file (one by one line, format data, create insert query and run) daily at your given time and import in database

Solution 2:
Change vb script to generate date in 24 hour format
Generate a cronjob (Scheduled Tasks) for php program
This program will use "LOAD DATA INFILE" mysql command to import your file in database

Solution 2 will be better for big text files.
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.

All Courses

From novice to tech pro — start learning today.