How to Load a CSV File into a MySQL Table Using PHP

In this article, we show how to load a CSV file into a MySQL table using PHP.

You can't just load any file into a MySQL table. The file must be specially formatted for database tables.

This type of file must be a comma-separated values (CSV) file, also called a comma-delimited file. It is a common format used to transfer information between various databases. It is a file formatted so that the data inside of it is organized as rows and columns. This CSV-formatted file can then be used to transfer data to tables of databases.

The CSV file can take many formats. It can be one created by such a company as Microsoft with a special .csv file extension. Or it can just be a plain text file, with data organized in rows and columns in that text file.

In the commercial world of CSVs, certain companies such as Microsoft have created a special file extension with .csv to indicate an Excel spreadsheet that is used as a CSV. PHP and MySQL can read these files just as it can read a plain text file with data organized in rows and columns. So if you created an Excel spreadsheet with certain data and you want that data to be inserted into a MySQL database table, you simply save it with a .csv and then specify this file to be loaded into the MySQL table. MySQL & PHP, in the same way, can read plain text files that have been formatted in CSV format.

The general format of the MySQL query to load data from a CSV file to a MySQL table is shown below.

LOAD DATA LOCAL INFILE 'data.text' INTO TABLE table_name FIELDS TERMINATED BY ','

So the above PHP code loads the file data.txt into the table named table_name where the columns of the table are separated by a comma (,).

Now we will show how to tie this in with PHP.

PHP Code

The PHP code to load the data from a CSV file into a MySQL table is shown below.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $load= mysql_query("LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE table_name FIELDS TERMINATED BY ','"); if($load !== FALSE) { echo("The data has been successfully loaded"); }else{ echo("The data has not been loaded."); } ?>

So the following code gets the data required to make a connection to a database and actually selects a database.

After we do this, we create a variable named $load. This $load variable is set equal to a mysql_query() function that has the parameter "LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE table_name FIELDS TERMINATED BY ','". This loads the CSV-formatted text file, data.txt' into the table named table_name, where the columns of the data are separated by a comma. The comma acts as the delimiter. You don't have to use a comma as the delimiter. You could also use others symbols, such as "|" or any other character. You would then specify, "FIELDS TERMINATED BY 'character', using the character you chose to be the delimiter. The reason you may use another character besides the comma is if you are using commas inside of one of the fields. Then you would obviously need to use another character as the delimiter. If you are not using commas in any of the columns of the table, then a comma is seen as the default value, so to speak. I mean, a CSV file is called a comma-separated values file.

If this statement executes successfully, then the data (rows and columns) in the CSV file will be uploaded to the table. So the table will have these new values.

Example

Table Structure

As an example, I created a table named engineeringfirm.

The structure of this table is shown below.

So the table above is named engineering firm. It has 3 columns, name, age, occupation. It gives the name, age and occupation of an employee at this engineering firm.

The table has no data entered into it. So it's created but it's completely empty.

CSV File

Now I create a CSV file. You can create CSV in multiple formats.

However, I chose to create one in a plain text (.txt) file. I named it employees.txt because it represents the employees of this engineering firm.

The contents in this file can be seen below or at the following link: CSV File in Plain Text Format.

Below is the contents of this CSV file.

John Bradley,29,software engineer Peter Brave,28,electrical engineer Bob Matthews,27,chemical engineer

These are the 3 rows we want to enter into the table, engineeringfirm.

Now, using the PHP code above, and inserting the correct table name and CSV file, the following results can be seen below of the table which now has the 3 rows of the CSV file.

Actual PHP Output