In this tutorial, I will explain to you how to read CSV files with the help of PHP. And how you can import CSV file data into a MySql table with PHP. So, PHP provides you the function that you can use to read CSV files.
There are many ways to read CSV files but in this tutorial, we will use two functions to read CSV files fopen() and fgetcsv() The first function we used to check and read the file with the help of fopen() function with “r” mode. There are many modes of fopen() function you can check all modes at the below link.
https://www.php.net/manual/en/function.fopen.php
fopen ( string $filename , string $mode [, bool $use_include_path = FALSE [, resource $context ]] ) : resource
The next function is fgetcsv() this function is specific to read line by line of CSV file and return data into an array. This function is similar to fgets() the only difference is CSV specification.
fgetcsv ( resource $handle [, int $length = 0 [, string $delimiter = "," [, string $enclosure = '"' [, string $escape = "\\" ]]]] ) : array
So we use these two functions to extract data from CSV file and print into the table and also save into the Database. The below code is just an example to show you who CSV data converts into an array with fgetcsv() function.
<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle)) !== FALSE) {
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>\n";
$row++;
for ($c=0; $c < $num; $c++) {
echo $data[$c] . "<br />\n";
}
}
fclose($handle);
}
?>
And the below code shows how you read the CSV file and show it into the table and save it into the database table.
<table align="center" width="800" border="1" style="border-collapse:collapse; border:1px solid #ddd;" cellpadding="5" cellspacing="0">
<thead>
<tr bgcolor="#FFCC00">
<th>Sr#</th>
<th>Country Code</th>
<th>Country Name</th>
<th>Capital</th>
</tr>
</thead>
<tbody>
<?php
if (($handle = fopen("you-file-name.csv", "r")) !== FALSE) {
$n = 1;
while (($row = fgetcsv($handle)) !== FALSE){
if($n>1){
?>
<tr>
<td><?php echo $n;?></td>
<td><?php echo $row[0];?></td>
<td><?php echo $row[1];?></td>
<td><?php echo $row[2];?></td>
</tr>
<?php
}
$n++;
}
fclose($handle);
}
?>
</tbody>
</table>
To save data into the database table first we need to connect with the database so you need to create a connection with the database with PHP consider the below code.
<?php
$db = new mysqli('localhost','root','','test');
if ($db->connect_errno) {
echo "Failed to connect to MySQL: " . $db->connect_error;
exit();
}
?>
In this example, we will dump countries’ data into the table to create a database table with the help of the below query.
--
-- Table structure for table `countries`
--
CREATE TABLE `countries` (
`id` int(5) NOT NULL,
`countryCode` char(2) NOT NULL DEFAULT '',
`countryName` varchar(45) NOT NULL DEFAULT '',
`currencyCode` char(3) DEFAULT NULL,
`population` varchar(20) DEFAULT NULL,
`fipsCode` char(2) DEFAULT NULL,
`isoNumeric` char(4) DEFAULT NULL,
`north` varchar(30) DEFAULT NULL,
`south` varchar(30) DEFAULT NULL,
`east` varchar(30) DEFAULT NULL,
`west` varchar(30) DEFAULT NULL,
`capital` varchar(30) DEFAULT NULL,
`continentName` varchar(15) DEFAULT NULL,
`continent` char(2) DEFAULT NULL,
`areaInSqKm` varchar(20) DEFAULT NULL,
`isoAlpha3` char(3) DEFAULT NULL,
`geonameId` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `countries`
--
ALTER TABLE `countries`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `countries`
--
ALTER TABLE `countries`
MODIFY `id` int(5) NOT NULL AUTO_INCREMENT;
COMMIT;
To dump data into the database consider the below query. You just need to write this query into the loop.
<?php
$db->query('INSERT INTO `countries`(`countryCode`, `countryName`, `currencyCode`, `population`, `fipsCode`, `isoNumeric`, `north`, `south`, `east`, `west`, `capital`, `continentName`, `continent`, `areaInSqKm`, `isoAlpha3`, `geonameId`) VALUES ("'.$row[1].'","'.$row[2].'","'.$row[3].'","'.$row[4].'","'.$row[5].'","'.$row[6].'","'.$row[7].'","'.$row[8].'","'.$row[9].'","'.$row[10].'","'.$row[11].'","'.$row[12].'","'.$row[13].'","'.$row[14].'","'.$row[15].'","'.$row[16].'")');
?>
The final complete script.
The complete PHP reads the CSV file and dumps it into the table the is given below.
<?php
$db = new mysqli('localhost','root','','test');
if ($db->connect_errno) {
echo "Failed to connect to MySQL: " . $db->connect_error;
exit();
}
if(($handle = fopen("countries.csv", "r")) !== FALSE){
while(($row = fgetcsv($handle)) !== FALSE){
$db->query('INSERT INTO `countries`(`countryCode`, `countryName`, `currencyCode`, `population`, `fipsCode`, `isoNumeric`, `north`, `south`, `east`, `west`, `capital`, `continentName`, `continent`, `areaInSqKm`, `isoAlpha3`, `geonameId`) VALUES ("'.$row[1].'","'.$row[2].'","'.$row[3].'","'.$row[4].'","'.$row[5].'","'.$row[6].'","'.$row[7].'","'.$row[8].'","'.$row[9].'","'.$row[10].'","'.$row[11].'","'.$row[12].'","'.$row[13].'","'.$row[14].'","'.$row[15].'","'.$row[16].'")');
}
fclose($handle);
}
?>