In this article we will learn How to Import Text File to MySQL Database in PHP. In this example, PDO and SplFileObject classes are used to import Data to MySQL Database in php.
Step 1: Firstly,we create the data.txt file as follows
1 2 3 4 5 6 | Pamela;C. Shafer;1996-05-14 Kimberly;J. Meade;1986-06-10 Edward;E. Smith;1956-12-24 Stephen;E. Escobar;2001-2-5 |
Step 2: Creating table. I created my table as customers and set id as auto increment.
Step 3: SplFileObject reads the text file line by line until reaches end of the text file. Each line contains person’s name, lastname and birth date. After reading line, we use list() function to use to assign values to a list of variables in one operation.
1 2 3 4 5 6 7 8 | $file = new SplFileObject("data.txt"); while (!$file->eof()) { $line = $file->fgets(); list($name,$lname,$birth)=explode(";",$line); /* SQL INSERT*/ } |
Step 4: Insert variables to database. After assign line to variable, PDO object improrts data to MySQL Database as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $dsn = 'mysql:dbname=sample;host=127.0.0.1'; $user = 'root'; $password = ''; try { $dbh = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Connection Error: ' . $e->getMessage(); } $file = new SplFileObject("data.txt"); while (!$file->eof()) { $line = $file->fgets(); list($name,$lname,$birth)=explode(";",$line); $sth = $dbh->prepare('INSERT INTO `customers` (`id`, `name`, `lastname`, `birth`) VALUES (NULL, ?, ?, ?);'); $sth->bindValue(1, $name, PDO::PARAM_STR); $sth->bindValue(2, $lname, PDO::PARAM_STR); $sth->bindValue(3, $birth, PDO::PARAM_STR); $sth->execute(); } |
The above code connects to a MySQL database. The code connects to a database named ‘sample’ on the localhost using the root user and an empty password. It creates a new instance of the PDO class using the MySQL PDO driver, the host and the database name. If an error occurs while trying to connect to the database, an error message is displayed.
The code then creates a new instance of the SplFileObject class and opens a file named ‘data.txt’. The code uses a while loop to read the file until it reaches the end of the file (EOF). It uses the fgets() method to read each line of the file and assigns it to a variable $line. It then uses the explode function to split the line into an array where the values are separated by “;”, and assigns each value to a variable $name, $lname, and $birth.
The code then prepares an SQL statement to insert data into the ‘customers’ table and assigns it to a variable $sth. It then uses the bindValue method to bind the values of the variables $name, $lname, and $birth to the respective placeholders in the SQL statement. The execute method is then used to execute the prepared statement. The process is repeated until the end of the file is reached.
Here is the answer of how to import Text File to MySQL Database. This examples works with PHP 5.1+ version.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <?php /* Connect to a MySQL database using driver invocation */ $dsn = 'mysql:dbname=sample;host=127.0.0.1'; $user = 'root'; $password = ''; try { $dbh = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } $file= new SplFileObject('data.txt'); while(!$file->eof()) { $line=$file->fgets(); list($name,$lname,$birth)=explode(';',$line); $sth = $dbh->prepare('INSERT INTO customers values(NULL,?,?,?)'); $sth->bindValue(1,$name,PDO::PARAM_STR); $sth->bindValue(2,$lname,PDO::PARAM_STR); $sth->bindValue(3,$birth,PDO::PARAM_STR); $sth->execute(); } ?> |
This script connects to a MySQL database using PHP’s PDO library. It sets the connection parameters such as the data source name (DSN), username, and password, and then creates a new PDO instance using those parameters. If the connection fails, an error message is displayed.
It also opens a file named “data.txt” using the PHP’s SplFileObject class. It reads the file line by line until the end of the file is reached. Each line is exploded into an array, where the values are separated by a semicolon. Then the script prepares an SQL statement to insert the values into the “customers” table. Finally, it binds the values to the placeholders of the prepared statement and execute the statement.
