Home » Support » Help Articles » Import Data from Excel Sheet to MySQL Database Table using PHP

Import Data from Excel Sheet to MySQL Database Table using PHP

Back to Help Articles Home


The following steps are required to import data from excel sheet to mysal database table.

Step 1: Convert the Excel Sheet into a '.csv' File

Save the excel sheet into a csv file using comma (,) as separator and double quotes (") as field delimeter. You may select other symbols as separator or field delimeter.

Step 2: Create a File Upload Form

Use the following code to create a file upload form.

Step 3: Create a function to import data from csv file

Use the following php code in your class file to import data

function importdata()
{
	//Upload the file to a directory present in server
	$sFileName = $_FILES["fileCSV"]['name'];
	$sTmpFileName = $_FILES["fileCSV"]['tmp_name'];
	$sExistingFileName 	= basename($sFileName);
	$aFilename    		= explode(".",$sExistingFileName);
	$sExtName    		= array_pop($aFilename);
	$sPostFileName       = $aFilename[0];
	$sDirPath          = './files/csv-files/';
	$sNewFileName      = $sPostFileName.'.'.$sExtName;
	$sFileOrigPath     = $sDirPath.$sNewFileName;
	if(move_uploaded_file($sTmpFileName, $sFileOrigPath))
	{
	   $sNewFile      = $sNewFileName;
	}
	
	//Read the file from the directory	
	if (($getfile = fopen($sFileOrigPath, "r")) !== FALSE) //$sFileOrigPath: Path of directory where CSV file is uploaded
	{
		$data = fgetcsv($getfile, 1000, ","); //fgetcsv  Gets line from file pointer and parse for CSV fields
		while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) 
		{
			$iNum = count($data);
			$sResult = $data;
			$sCSVData = implode(",", $sResult);
			$aCSVData = explode(",", $sCSVData);
			
			$sColumn1 = $aCSVData[0];//Values of first column in excel sheet
			$sColumn1 = $aCSVData[1];//Values of second column in excel sheet
			$sColumn1 = $aCSVData[2];//Values of third column in excel sheet
			
			//Add a PDO insert statement to add data into the database table
			$oStmt = $dbh->prepare("INSERT INTO tablename SET table_column_1 = '$sColumn1', table_column_2 = '$sColumn2', table_column_3 = '$sColumn3'");
			$oStmt->execute();
		}
	}
}



Updated on Aug 31, 2016

The techReview is an online magazine by Batoi and publishes articles on current trends in technologies across different industry verticals and areas of research. The objective of the online magazine to provide an insight into cutting-edge technologies in their evolution from labs to market.

Visit techReview


English - IN (USD)
New Users? Signup.     Existing Users? Login.