How to Read / Write CSV File in PHP

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

What is CSV?

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

In the below example, I will explain how to read a CSV file and update that data into the database and how to write a CSV file with database values.

Here is the example database table structure,

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

Here is the 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, we 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.

Write CSV File:

Here is the 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, we used the PHP function fputcsv() to write the CSV file.

Here is the example CSV file,

id firstname lastname email
1 Thomas Raj thomas.raj@example.com
2 Manoj Sharma manoj.sharma@test.com
3 Anil Nagar anil.nagar@example.com

Read more: 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 *