How to Read / Write CSV File in PHP

In this quick article, I am going to show you how to read the content from the CSV file and update/write the content to the CSV file using PHP.

What is CSV?

CSV stands for Comma Separated Values, CSV is a simple file format used to store the tabular data with .csv extensions, such as a spreadsheet. In CSV file, all the values are separated by a comma and individual rows are separated by a newline character.

Here, I will give you an example with some database values. We can take the following database table as our reference,

CREATE TABLE user_details (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	firstname VARCHAR(30) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	email VARCHAR(50)
)

Read CSV file Using PHP

Here is the full set of example PHP code to read a CSV file and update that data into the database table,

<?php
$servername = 'localhost';
$username = 'root';
$password = 'welcome';
$dbname = 'csv_file';
 
// create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// check connection
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}
// open a file
if (($handle = fopen('file.csv', 'r')) !== FALSE) {
    $importData = []; $i = 0;
    // read the CSV file using fgetcsv() function
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        for ($c = 0; $c < $num; $c++) {
            $importData[$i][$c] = $data[$c];
        }
        $i++;
    }
    fclose($handle);
 
    $sql = 'INSERT INTO `user_details` (' . implode(',', $importData[0]) .') VALUES ';
 
    for ($i = 1; $i < count($importData); $i++) {
        $sql .= ($i == 1) ? '(NULL' : ', (NULL';
        for($j = 1; $j < count($importData[$i]); $j++) {
            $sql .= ", '".$importData[$i][$j]."'";
        }
        $sql .= ')';
    }
     
    if ($conn->query($sql) === TRUE) {
        echo 'Data updated successfully';
    } else {
        echo 'Error occured: ' . $conn->error;
    }
}
 
$conn->close();

In the above code,

  • First, enabled the database connection using the PHP function mysqli() with the database server name, user name, password and database name.
  • Second, opened the CSV file by using the PHP function fopen() with the file name and the type of access. Here, used the access type r to read the CSV file.
  • Next, used the PHP function fgetcsv() to read the CSV file. The fgetcsv() function parses the line from an open file, it reads for fields in CSV format and returns the CSV fields in an array on success, or FALSE on failure and EOF.
  • Finally, updated the data into the database using the INSERT query.

Write CSV File Using PHP

Here is the full set of example PHP code to fetch the data from the database and write that data on the CSV file,

<?php
$servername = 'localhost';
$username = 'root';
$password = 'welcome';
$dbname = 'csv_file';
 
// create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// check connection
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
} 
 
$sql = "SELECT id, firstname, lastname, email FROM user_details";
$result = $conn->query($sql);
 
if ($result->num_rows > 0) {
 
    $data = ['id,firstname,lastname,email'];
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $data[] = $row['id'] . ',' . $row['firstname'] . ',' . $row['lastname'] . ',' . $row['email'];
    }
 
    // open a file
    $fp = fopen('file.csv', 'w+');
    foreach ($data as $line) {
        // write the data into the file
        fputcsv($fp, explode(',', $line));
    }
    fclose($fp);
 
}
 
$conn->close();

In the above code,

  • Same like the before example, first, enabled the database connection using the PHP function mysqli() with the database server name, user name, password and database name.
  • Second, fetched data from the database table using the SELECT query.
  • Next, opened the CSV file by using the PHP function fopen() with the file name and the type of access. Here, used the access type w+ to write the CSV file.
  • Finally, used the PHP function fputcsv() to write the CSV file.

Here is the example CSV file,

idfirstnamelastnameemail
1ThomasRajthomas.raj@example.com
2ManojSharmamanoj.sharma@test.com
3AnilNagaranil.nagar@example.com

Read Also: How to Read / Write a File and Scan a Directory in PHP

Hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *