Parsing a CSV File using PHP

There will be times you will be tasked to enter all the data stored inside an excel file into the database. Why waste your time encoding all the data or hiring someone to do it for you if you can simply create a simple script that will read through the columns of your spreadsheet?



In this tutorial I am going to discuss how you can access the columns of your spreadsheet and insert it into your database.

Setting up a CSV File

First you will want to create a CSV file, create a new text file in your desktop or project folder, name it anything you want without spaces. For the extension you will have to enter “.csv”, now open the file you just created using your Excel Reader.

You will notice that the interface looks similar to Excel. Now open the file containing your Excel table data and copy and paste it into the CSV file you created then save it.

Parsing the CSV File

There is a cool function called “fgetcsv()” which we will be using in parsing the data from your CSV file:

In your project folder, create a file: index.php and add the following code then save it:

$file = fopen('folder_name/csv_file.csv', 'r');
while ( ( $line = fgetcsv( $file ) ) !== FALSE) {
    echo '[crayon-592847d39d5f9891581222/]';
}
fclose( $file );

Make sure to change first parameter of the fopen to the destination of your CSV file.

To make sure everything is working perfectly without errors, try accessing your project folder from your browser. If successful, you should see several Arrays containing the data of each row of your CSV file.

Accessing the Columns within the Loop

To access the column, simply use the reference key of the columns:

echo $line[0];
$column1 = $line[1];

Inserting Data into the Database

Bellow is a simple example of inserting data into the database using MySQLi Object-oriented.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$file = fopen('folder_name/csv_file.csv', 'r');
while ( ( $line = fgetcsv( $file ) ) !== FALSE) {
    $sql = "INSERT INTO table_name (column1, column2, column3)
    VALUES ('" . $file[0] . "', '" . $file[1] . "', '" . $file[2] . "')";
}
fclose( $file );

$conn->close();
?>