Import .CSV File Into MySql Using PHP

This the very easy to understand and implement PHP script to import the .CSV file into the MYSQL database.

Let us discuss the code with easy steps, so that every thing is crystal clear.

STEP 1: Connect With The DataBase Using PHP

This code is simply connecting you to your database (your_database_name). Just replace host_name, username and password with your actual values.

    $link_id = mysql_connect("host_name", "username", "password") or die("Could not connect.");
    if(!mysql_select_db("your_database_name",$link_id))        die("database was not selected.");

STEP 2: Read the CSV File And Import Into MySql

a) In order to read .CSV file, open it.

This code is opening your .CSV file for reading. It is assumed that you have uploaded your file on the server in the same directory.

$file_handle = fopen("my_csv_file.csv", "r");

b) Read the my_csv_file.csv line by line and import it into the database:

This code is reading one line of the .CSV file at one time. Then it separates that line on the basis of comma(,). Here we assume that

there are three values in each line or record. These are user_name, address and class. These three values are stored in an array $line_of_data.

Then a query is made that inserts these three values in my_table_name .

This process continues till all the lines are read from the .CSV file and are imported into the MYSQL table.

while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE) {

       $line_import_query="INSERT into my_table_name(user_name,user_address,user_class) values('$line_of_data
[0]','$line_of_data[1]','$line_of_data[2]')";

      mysql_query($line_import_query) or die(mysql_error());

     }

Explanation In Detail:

Variable $file_handle has got the .CSV file by opening it for reading purpose.

The while loop is looping till all the lines are read from the file. Each line of data should be 1000 characters long at the maximum. If your line has more than 1000 characters then you can change the 1000 to 2000 etc. The comma in the command is telling that line should be broken on the basis of comma. If your file is tab delimited then you can use the tab symbol there

Now this data of line is stored in an array $line_of_data. There are three values. These three are stored in three subscripts of this array.

Then simple the INSERT query is inserting this three values in our table. The query is self explanatory.

I hope you have enjoyed reading this article.