Sureba67 | Journey Of A Nifty Day Trader

Excel To MySql Database | Code From An Amateur

May 3, 2013 sureba67 3 comments

Presenting here the work of a day trader and above all a school dropout.

The script presented below first reads and then imports data from Excel to Mysql database, using PHPEXCEL project.

Version details: PHPEXCEL:1.7.8; Apache:2.4.4; PHP:5.4.13; Excel:2007

I dedicate my code to 3 professional programmers –

1. d’alar’cop

2. LWol

3. Bashing-om

and to my best friend, my mentor and a stark critic – Mr. Ashwin Vyas, without their encouragement and support I would not have even dreamt of attempting to start venturing into such a complex world of programming.

My main objective was to write a script to store real time stock quotes from Excel sheet to Mysql database; to achieve that, first I decided to learn testing a test data from Excel to Mysql database.

I used PHPExcel Project developed by Mr. MarkBaker in my script to read Excel sheet and export the same to Mysql database using Mysqli Prepared INSERT INTO statement.

In order to ensure freshers like me get full idea of how to use PHPEXCEL, I have presented my full working script, starting from establishing connection to Mysql database till inserting data to Mysql table.

<?php  
//In case you want the script to be executed at a predefined time interval, use this   
/*header('Refresh: 30'); */

Establishing connection to Mysql Database:

  
$mysqli = new mysqli("localhost", "(username)","(password)>","(database)");   
if (mysqli_connect_errno()) { 	 
echo mysqli_connect_error(); 	 
exit(); }

PHPEXCEL Reader:

  
require 'c:/server/www/classes/phpexcel.php';
require_once 'c:/server/www/classes/phpexcel/iofactory.php';

$inputFileName 	= 'c:/server/users.xls';
$inputFileType	= 'Excel5'; 

$objReader		= PHPExcel_IOFactory::createReader("$inputFileType");
$objReader->setReadDataOnly(true);

$objPHPExcel	= $objReader->load("$inputFileName");
$objWorksheet	= $objPHPExcel->getActiveSheet();

$highestRow		= $objWorksheet->getHighestRow();
$highestColumn	= $objWorksheet->getHighestColumn();
$highestColumnIndex	= PHPExcel_Cell::columnIndexFromString($highestColumn);

Display Excel data on webpage:

  
echo '<table>' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
	echo '<tr>' . "\n";

for ($col = 0; $col <= $highestColumnIndex; ++$col) {
	echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
}
echo '</tr>' . "\n";	
}
echo '</table>' . "\n";

Preparing to import data from Excel to Mysql Database:

for($row = 2; $row <= $highestRow; ++$row) {
for($col = 0; $col < $highestColumnIndex; ++$col) { 	
$rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row);

}

// Prepared Statement
$stmt = $mysqli->prepare("INSERT INTO users (Username,Email,Gender,Country) VALUES (?,?,?,?)");

//Prepared Statement Bound
$stmt->bind_param('ssss', $Username, $Email, $Gender, $Country);

	$Username 	="$rows[0]";
	$Email 		="$rows[1]";
	$Gender 	="$rows[2]";
	$Country 	="$rows[3]";

//Prepared Statement Executed
$stmt->execute();
printf("%s Row Inserted.\n", $stmt->affected_rows);

//Prepared Statement Closed
$stmt->close();

// If you don't want to use prepared statement, you can use this one
/* $mysqli->query("INSERT INTO users (Username,Email,Gender,Country) VALUES ('$rows[0]', '$rows[1]', '$rows[2]', '$rows[3]')"); */

}

$mysqli->close();
?>

Images of Data in Excel, webpage and Mysql Table:

Data in Excel:

Webpage and Mysql Table:

Healthy And Wealthy Coding

Healthy And Wealthy Life

Categories: Coding Tags: Excel To Mysql Database, Export Excel, Import Excel, MySql, Mysqli INSERT INTO Statement, Mysqli Prepared Statement, PHP, PHPEXCEL, Sureba67

Indian Stocks | A View On Daily Charts | 02-03-2013

March 2, 2013 sureba67 Leave a comment

Presenting here the daily charts of my most favorite stocks with HMA-Bollinger Bands Manual Day Trading System (HMA-BB MDTS), as on 1^st March, 2013.

To get regular intraday updates, please follow my facebook page –https://www.facebook.com/hmabbdaytrader

Subjects in focus:

1. Axis Bank; 2. IDFC; 3. State Bank Of India; 4. Tatamotors

NSE Axis Bank (Daily)