How to Parse CSV files with PHP and Insert data into MySQL database
By Ujwal Kumar SahooIn this article, we will learn how to read an existing CSV file and how it can be uploaded to the database.
so, we will cover it in two phases:
- Reading an existing CSV
- Insert the contents of CSV file into our database(Here used database name is Codespeedy).
Reading an existing CSV file in PHP
The function fgetcsv() is used to handle content present in the CSV file.
Syntax:-
fgetcsv(filename,max_length_of_file,separater);
Note:-
- By default, the separator is a comma(“,”).
Example:-
fgetcsv("filename.csv",9999,",");
Here is a simple example of reading a file and display the result:
<?php
// Open the file for reading
$myfile = fopen("mycsv.csv", "r");
// each line into the local $data variable
while (($data = fgetcsv($myfile, 9999, ",")) !== FALSE)
{
// Read the data from a single line
echo 'Roll: ' . $data[0] . '<br>';
echo 'Name: ' . $data[1] . '<br>';
echo 'Mark: ' . $data[2] . '<br>';
echo '<br>';
}
// Close the file
fclose($myfile);
?>
Output:-
Roll: 1 Name: John Mark: 38 Roll: 2 Name: Sid Mark: 72 Roll: 3 Name: Jk Mark: 20
Inserting CSV data into MySQL database
This illustrates a simple example of the CSV data inserted to the database:
<?php
// Create connection and checking connection
$con=mysqli_connect("localhost","root","","codespeedy") or die(mysqli_error());
//open mycsv.csv file in 'r' mode i.e in reading mode
$file = fopen('mycsv.csv', 'r');
while ( ( $line = fgetcsv( $file,9999,"," ) ) !== FALSE) {
$sql = "INSERT INTO csvrecords (Roll, Name, Mark)
VALUES ('" . $line[0] . "', '" . $line[1] . "', '" . $line[2] . "');";
$con->multi_query($sql);
}
fclose( $file );
//closing connection
mysqli_close($con);
?>
Output:-
| Roll | Name | Mark |
1 |
John |
38 |
2 |
Sid |
72 |
3 |
Jk |
20 |
This is how CSV data is parsed to an array as well as the database.
If any problem is arising with the above article, please put a comment below.
See also,
Leave a Reply