How to Import CSV File Into MySQL in PHP

In this article, you will learn how to import a CSV file into MySQL in PHP.

Let’s say you have a CSV file of users and their respective ages.

example.csv

User,Age
Adam,22
Brian,20
Cleo,25

Import CSV File Into MySQL

In order to import a CSV file into MySQL, you can use the LOAD DATA statement from MySQL. Before following this example, you need to create a database named ‘codesource’, a table named ‘test’, a column named ‘User’ with type varchar(10) and another column named ‘Age’ with type int(2).

full php

<?php
	// Host name
	$host = "localhost";

	// User
	$user = "root";

	// Password 
	$password = "";

	// Database name
	$db = "codesource";

	// Open a new connection to MySQL server
	$connection = mysqli_connect($host, $user, $password, $db);

	// If the connection fails
	if (!$connection) {

  		// Display message and terminate script
  		die("Connection failed: " . mysqli_connect_error());
	}

	// Path to CSV file
	$file = "C:/xampp/htdocs/example.csv";

	// Name of table
	$table = "test";

	// Load CSV file into a table named 'test' and ignore the first line in file
	$query = <<<eof
                LOAD DATA INFILE '$file'
                INTO TABLE $table
                FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                LINES TERMINATED BY '\n'
                IGNORE 1 LINES
                (User, Age)
                eof;

	// Perform a query on the connected database
	$connection->query($query);
?>

Before Importing CSV File

After Importing CSV File

Note: The LOAD DATA statement functions by reading rows of data from a text file into a table.