PHP read text file and insert into MySQL database

Ahsan Zameer

6 years ago

Today I was working on an in house project and suddenly one of my colleagues came to me with a text file and said please import this file into database. I did that for him.

At a same time I got an idea why not this small task should share with my PHP beginners. So in this tutorial I am going to show how to read text file and insert into mysql database.

Read text file and insert into MySQL database

First let me show you some of the built-in php functions which I am going to use in this process.

fopen() : Open a file or url. fopen() takes2 arguments. First argument is a file name or url and second argument is mode. Mode parameter specifies the type of access you require to the file.
feof() : Checks the end of file is reached or not.
fgets() : Returns a line from an open file.
explode() : Breaks string into an array. It takes 2 arguments. First argument is a separator and second is string.
list() : Assign variables as if they were an array.
fclose() : Close the open file.

Create Database:


create database demo;

create database demo;

Create Database Table:


CREATE TABLE `empoyee_details` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL DEFAULT NULL,
`city` VARCHAR(255) NULL DEFAULT NULL,
`postcode` VARCHAR(255) NULL DEFAULT NULL,
`job_title` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;

CREATE TABLE `empoyee_details` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(255) NULL DEFAULT NULL,

`city` VARCHAR(255) NULL DEFAULT NULL,

`postcode` VARCHAR(255) NULL DEFAULT NULL,

`job_title` VARCHAR(255) NULL DEFAULT NULL,

PRIMARY KEY (`id`)

)

ENGINE=InnoDB

;

Text file content:


Heber,Camrynborough,26728,Home Health Aide
Modesto,West Janet,15152-2683,Software Engineer
Dante,East Chanel,74689-6886,Entertainment Attendant
Nolan,Murphyville,32561-8079,Credit Authorizer
Jovany,O'Reillyton,44371,Medical Assistant
Jaeden,Greenfort,06179-1759,School Social Worker
Efrain,West Blairborough,11282-0496,Electronic Drafter
Travon,South Tatum,76603-0822,Manufactured Building Installer
Agustina,North Gertrudeland,18950,Health Services Manager

Heber,Camrynborough,26728,Home Health Aide

Modesto,West Janet,15152-2683,Software Engineer

Dante,East Chanel,74689-6886,Entertainment Attendant

Nolan,Murphyville,32561-8079,Credit Authorizer

Jovany,O'Reillyton,44371,Medical Assistant

Jaeden,Greenfort,06179-1759,School Social Worker

Efrain,West Blairborough,11282-0496,Electronic Drafter

Travon,South Tatum,76603-0822,Manufactured Building Installer

Agustina,North Gertrudeland,18950,Health Services Manager

Database Connection:


$conn = mysqli_connect('localhost','root','','demo');

if(!$conn)
{
    die(mysqli_error());
}

$conn = mysqli_connect('localhost','root','','demo');

if(!$conn)

{

die(mysqli_error());

}

Read text file and insert data:


$open = fopen('employee-data.txt','r');

while (!feof($open)) 
{
	$getTextLine = fgets($open);
	$explodeLine = explode(",",$getTextLine);
	
	list($name,$city,$postcode,$job_title) = $explodeLine;
	
	$qry = "insert into empoyee_details (name, city,postcode,job_title) values('".$name."','".$city."','".$postcode."','".$job_title."')";
	mysqli_query($conn,$qry);
}

fclose($open);

$open = fopen('employee-data.txt','r');

while (!feof($open))

{

$getTextLine = fgets($open);

$explodeLine = explode(",",$getTextLine);

list($name,$city,$postcode,$job_title) = $explodeLine;

$qry = "insert into empoyee_details (name, city,postcode,job_title) values('".$name."','".$city."','".$postcode."','".$job_title."')";

mysqli_query($conn,$qry);

}

fclose($open);

In the above code first I open a employee-data.txt using reading (r) mode. Then I used while loop using feof() function and make sure loop must be iterated till the end of file. Then I stored file line in the $getTexLine variable and then convert that line into an array by using comma. I already knew that we have 4 type of data on each line so I used list() function and create 4 variables which are $name, $city, $postcode, $job_title. After that I create a mysql insert statement and add data into table. This process will continue until employee-data.txt content ends.