Click here to Skip to main content
12,999,417 members (49,217 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


9 bookmarked
Posted 22 Jul 2010

Simple Way to Insert Data into Table from a CSV or Excel File

, 22 Jul 2010
Rate this:
Please Sign up or sign in to vote.
MYSQL: Rather than reading a CSV file and inserting rows (tedious), speeded process just in one query


This article simply describes how efficiently a speeded query can transfer data from CSV or Excel file directly into MYSQL database.


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:

     INTO TABLE tbl_name
             [OPTIONALLY] ENCLOSED BY '']
     [IGNORE number LINES]

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.


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 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).


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:


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.


<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)
                        if (window.focus) {new_wind.focus();
                        return false;
<!--  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" 



         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 />";
                {       //              Errorless  start 
                        $file_name=$_FILES["file"]["name"];echo $file_name;
                                echo "Please the input file should be a .csv file";
                                //      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
					$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());

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);


  • 22nd July, 2010: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

namrata khandeparkar
Software Developer (Junior)
India India
hello . I am studying BE in information technology in GOA.
My interests lie in programming in mobile communication in etc.

You may also be interested in...

Comments and Discussions

QuestionUnable to insert data from Excel file(.xlsx) into mysql database Pin
Member 992038018-Mar-13 1:35
memberMember 992038018-Mar-13 1:35 
GeneralMy vote of 3 Pin
Asatua28-Jul-10 3:01
memberAsatua28-Jul-10 3:01 
GeneralNot an article Pin
digital man21-Jul-10 21:19
memberdigital man21-Jul-10 21:19 
GeneralRe: Not an article Pin
digital man22-Jul-10 3:49
memberdigital man22-Jul-10 3:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170624.1 | Last Updated 22 Jul 2010
Article Copyright 2010 by namrata khandeparkar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid