How to Parse CSV files with PHP and Insert data into MySQL database

By Ujwal Kumar Sahoo

In 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:

1. Reading an existing CSV
2. 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:-

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,

• How to create a CSV file using PHP
• Display HTML code on the web page in PHP