Read a text file and transfer contents to mysql database
Solution 1
open txt file using fopen:
$handle = @fopen("xyz.txt", "r"); //read line one by one
$values='';
while (!feof($handle)) // Loop til end of file.
{
$buffer = fgets($handle, 4096); // Read a line.
list($a,$b,$c)=explode("|",$buffer);//Separate string by the means of |
//values.=($a,$b,$c);// save values and use insert query at last or
// use mysql insert query here
}
THATS IT
Solution 2
What you may be looking for is MySQL's built-in function LOAD DATA INFILE
to load a text file containing values for a database into a database.
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.
Example:
LOAD DATA INFILE 'data.txt' INTO TABLE my_table;
You could also specify the delimiters inside of your text-file, like so:
LOAD DATA INFILE 'data.txt' INTO TABLE my_table FIELDS TERMINATED BY '|';
Update:
Here is a full-working example, I uploaded a test data file here and here is my PHP code.
$string = file_get_contents("http://www.angelfire.com/ri2/DMX/data.txt", "r");
$myFile = "C:/path/to/myFile.txt";
$fh = fopen($myFile, 'w') or die("Could not open: " . mysql_error());
fwrite($fh, $string);
fclose($fh);
$sql = mysql_connect("localhost", "root", "password");
if (!$sql) {
die("Could not connect: " . mysql_error());
}
mysql_select_db("my_database");
$result = mysql_query("LOAD DATA INFILE '$myFile'" .
" INTO TABLE test FIELDS TERMINATED BY '|'");
if (!$result) {
die("Could not load. " . mysql_error());
}
Here what the table looked before running my PHP code:
mysql> select * from test;
+--------+-----------+------------+
| DataID | Name | DOB |
+--------+-----------+------------+
| 145 | Joe Blogs | 17/03/1954 |
+--------+-----------+------------+
1 row in set (0.00 sec)
And here is the result after:
mysql> select * from test;
+--------+-------------+------------+
| DataID | Name | DOB |
+--------+-------------+------------+
| 145 | Joe Blogs | 17/03/1954 |
| 234 | Carl Jones | 01/01/1925 |
| 98 | James Smith | 12/09/1998 |
| 234 | Paul Jones | 19/07/1923 |
| 986 | Jim Smith | 12/01/1976 |
+--------+-------------+------------+
5 rows in set (0.00 sec)
Jack Brown
Updated on July 05, 2022Comments
-
Jack Brown almost 2 years
I need a php script to read a .txt file.
The content of the text file are like this:
data.txt
145|Joe Blogs|17/03/1954 986|Jim Smith|12/01/1976 234|Paul Jones|19/07/1923 098|James Smith|12/09/1998 234|Carl Jones|01/01/1925
These would then get stored into a database like this
**DataID |Name |DOB **
234 |Carl Jones|01/01/1925
I would be so grateful if someone could give me script to achieve this.
Update:
<? $handle = @fopen("data.txt", "r"); $conn = mysql_connect("localhost","username","password"); mysql_select_db("mydatabase",$conn); while (!feof($handle)) // Loop til end of file. { $buffer = fgets($handle, 4096); // Read a line. list($a,$b,$c)=explode("|",$buffer); //Separate string by the means of | echo $a."-".$b."-".$c."<br>"; $sql = "INSERT INTO data_table (iddata, name, age) VALUES('".$a."','".$b."',".$c.")"; mysql_query($sql,$conn) or die(mysql_error()); } ?>
get the following error error in your SQL syntax; ...for the right syntax to use near ')' at line 1