Import CSV File Into MySql Using PHP: Free Source Code

You'll find a lot of PHP scripts for importing of CSV files into MySql using PHP. However, almost all of them requires you to make the table manually and create the table structures manually. The little piece of code below, originally coded by me, can automatically create a table with all the required table structure and automatically import the csv file into mysql database. Well, the code is self-explanatory. Every important line has comment so that u can understand the role of every line.


The basic features of the code is:

1. It automatically creates a table using the CSV file name given by you.
2. It automatically creates table structure using the 1st line of the CSV file
3. It automatically imports the CSV file using loop, the remaining line (all but not the 1st line) are inserted into the table using SQL command INSERT

<?php

//Import csv into mysql
//The file name (without extension) is used to create the table name
//The 1st row of csv is used to create table structure
//All the columns are varchar(500)
//The script stops if any error occurs in between

//////////////////////////
//EDIT THE VARIABLES BELOW
///////////////////////////

$db = 'Database_Name';
$user = 'Username';
$host = 'Your_MySql_Host';
$pass = 'Password';

$delimiter = ',';
$csvFileName = 'CSV_File_Name';
$csvFileExtension = '.csv';

////////////////////////////////
//NO NEED TO EDIT THE CODE BELOW
////////////////////////////////

//Open the csv file
$file = fopen($csvFileName.$csvFileExtension, "r") or exit("Unable to open file!");
//Get the 1st line and replace all the symbols
$string = preg_replace("/[^a-z0-9,]+/i", "", fgets($file));
//Convert the 1st line into an array
$string = explode($delimiter,$string);
//Prepare MySql query to create table
$query = "CREATE TABLE " . $csvFileName ."(id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), ";
foreach ($string  as &$value) {$query .= $value . ' VARCHAR(500), ';}
$query = substr_replace($query ,")",-2);

//Create table with structure
mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query($query) or die(mysql_error());
echo "Table Created!<br/>Inserting Values, Please wait..";

//Insert into table looping through every line (but not the 1st line)
$queryPrefix = "INSERT INTO " . $csvFileName . " VALUES (NULL, ";
while (!feof($file)){mysql_query($queryPrefix . " '" . str_replace(',', "', '", fgets($file)) . "')") or die(mysql_error());}
fclose($file);

//Csv Import into Mysql Database Done
echo '<br/>done :)';

?>