Import CVS , Excel or XML file into MySQL with php

When we want to insert data into MYSQL which is already present in .CVS (comma separated values) or XML or Excel files. Reading the file row by row (say in PHP) and inserting into MYSQL is very poor style of coding which is inefficient and takes a lot of time. MYSQL has an inbuilt query and I will be elaborating about this.

To import an Excel or XML file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file which I have describe before in here.

You can then import it into a MySQL table by running:
[php]

LOAD DATA [LOW_PRIORITY] INFILE 'file_name.csv' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS [TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]

[/php]

To complete this process I have used two file as the name of "index.php" and "load.php"
Source code of "index.php":
[php]

<form action="load.php" method="post" enctype="multipart/form-data">
<input type="file" name="file" id="file" />
<input type="submit" value="Load" name="load"/>
</form>

[/php]

Source code of "load.php":
[php]
<?php

function connect_db(){
// Please make changes : input your username and password
$con = mysql_connect("localhost","username","password");
if (!$con){
die('Could not connect: ' . mysql_error());
}
// enter your database name
mysql_select_db("databasename", $con);
}

if (isset($_POST['load'])){ // Do THE FOLLOWING WHEN BUTTON IS PRESSED

echo "button on is pressed";
if ($_FILES["file"]["error"] > 0){
echo "Error: " . $_FILES["file"]["error"] .
"You have not selected a file or some other error <br />";
}
else{ // Errorless start
$file_name=$_FILES["file"]["name"];echo $file_name;
$file_type=$_FILES["file"]["type"];
if($file_type!='text/csv'){
echo "Please the input file should be a .csv file";
}
else{ // only executed if file is .csv
echo "its correct";
// Creating a temporary copy on the server
$location=""; // write the location on
// server where a copy should be created
move_uploaded_file($_FILES["file"]["tmp_name"],
$location . $_FILES["file"]["name"]);

connect_db(); // MYSQL connection settings
// I have provided a sample query :
// Please make changes as per your database table and columns
$q="LOAD DATA
INFILE '$file_name' INTO TABLE cars
FIELDS TERMINATED BY \"\t\"
LINES TERMINATED BY \"\n\"
( color, price)";
mysql_query($q) or die(mysql_error());
}
}
}

?>


[/php]