Simple Way to Insert Data into Table from a CSV or Excel File
4.43/5 (4 votes)
MYSQL: Rather than reading a CSV file and inserting rows (tedious), speeded process just in one query
Introduction
This article simply describes how efficiently a speeded query can transfer data from CSV or Excel file directly into MYSQL database.
Background
There are times when we want to insert data into MYSQL which is already present in .csv(comma separated values) 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.
Using the Code
Let's have a look at the query first:
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,...)]
LOAD DATA INFILE reads data from text/CSV/Excel files at very high speeds. You can see clearly from the query that the file specified here is a .csv file.
[REPLACE | IGNORE]
Well REPLACE takes care of duplicate entries. For example, you have loaded the file nam.csv into MYSQL database which has 'emp_id' as primary key. Suppose by mistake or in future entry files, the same entry is present. If you have REPLACE tag specified in your query, there will not be any repetitions since the query will be replaced.
If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.
[FIELDS [TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
]
Fields in .csv file can be delimited/separated by ',' or '\t'(tab) or ';' etc. So you are able to specify in your query how you have separated the fields. You can also specify if each field is enclosed by ''(single quotes) or ""(double quotes).
[LINES TERMINATED BY '\n']
Usually new lines are terminated with this character '\n'. Although (say when you convert Excel file into .csv file) in text mode, you cannot see the \n in file. You can specify order of columns in which you want data to be added by using:
[(col_name,...)]
This query can be better understood if we take a small example in PHP. Say, you are having an application in which you allow the user to select file on his machine (front end) and then your job is to write PHP code which will allow you to transfer file contents into MYSQL database.
Front_end.html
<html>
<head>
<script> // This function will pop a window which will tell the
// user the order of fields and format of .csv file
// you can create pop_up_csv.html file in same directory and
// modify it with required format
function popitup(url)
{
new_wind=window.open(url,'name','height=700,width=1500');
if (window.focus) {new_wind.focus();
return false;
}
}
</script>
</head>
<body>
<!-- Adding searchable query data ( File => Table ) -->
Add data : The file should be a .csv file <a href="pop_up_csv.html"
onclick="return popitup('pop_up_csv.html')"
> Check the format here </a> </br></br>
<!-- On browsing of a file when you will click on button this page
will be directed to add_data_BE.php-->
<form action="add_data_BE.php" method="post" enctype="multipart/form-data">
<label for="file"> </label>
<input type="file" name="file" id="file" />
</br></br> <input type="submit" value="Load data file into database"
name="button1"/>
</form>
</div>
</body>
</html>
add_data_BE.php
<?php
if (isset($_POST['button1'])) // 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 log_analyse
FIELDS TERMINATED BY \"\t\"
LINES TERMINATED BY \"\n\"
( Lang,Doc_Type,Title,Authors,No_of_author,
Corp,Aff,Pub,Pub_yr,Pub_name,Vol,Issue,Keywords )";
mysql_query($q) or die(mysql_error());
}
}
}
?>
<?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);
}
?>
History
- 22nd July, 2010: Initial post