Online computer courses, code, programming tutorial and sidebar information for monitoring Canadian S&P/TSX index. Build friendship and networking. Welcome to visit my blogs often!!! I also have two other sites: YouTube Channel and Google site.
Adsense
Popular Posts
- PHPWind-- A PHP forum script applcaition in China
- How to blend adsense inside your post?
- Formatting my post
- Notepad++ - Add C++ compiler
- Install PHPMailer 5.2.4 and use smtp gmail
- Set up a child account and set screen time limit in Windows 8
- Wayback Machine - see archived versions of web pages across time
- phpexcel toggle expand and hide column in EXCEL and summary
- Install PHP ibm_db2 extension in Linux (redHat)
- PHP: add a download as pdf file button in report page
Friday, January 28, 2011
PHP, load EXCEL/CSV file to mysql
Excel files can be saved as csv format first.
1. First Create a database and table to match columns in csv, for example: first column name, then email and phone
CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;
DROP TABLE IF EXISTS `user`;
CREATE TABLE user
(
name VARCHAR(50) NOT NULL DEFAULT '',
email VARCHAR(50) NOT NULL DEFAULT '',
phone int(11),
PRIMARY KEY (`email`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
PHP script
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = 'root';
$db_pwd = 'mypasswd';
$database = 'mydatabase';
$table = 'user';
if (!mysql_connect($db_host, $db_user, $db_pwd))
die("Can't connect to database");
if (!mysql_select_db($database))
die("Can't select database");
if(isset($_POST['submit']))
{
$fname = $_FILES['sel_file']['name'];
echo 'upload file name: '.$fname.' ';
$chk_ext = explode(".",$fname);
if(strtolower(end($chk_ext)) == "csv")
{
$filename = $_FILES['sel_file']['tmp_name'];
$handle = fopen($filename, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql = "INSERT into user(name,email,phone) values('$data[0]','$data[1]','$data[2]')";
mysql_query($sql) or die(mysql_error());
}
fclose($handle);
echo "Successfully Imported";
}
else
{
echo "Invalid File";
}
}
?>
<h1>Import CSV file</h1>
<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
Import File : <input type='file' name='sel_file' size='20'>
<input type='submit' name='submit' value='submit'>
</form>
Subscribe to:
Post Comments (Atom)
how to check duplicate data when you import the csv file?
ReplyDeletewhat if duplication of entry in the database from csv file like same name? any code for that? thanks in advance
ReplyDeleteThis comment has been removed by the author.
ReplyDeletefor phone number if i put 0101234567 in excel CSV file, it will became 101234567 and display as 101234567 in database..how to solve it?
ReplyDeletethanks
Save it as varchar in db.
Deletesimile boy i think that depends of the type of data you define for the colon , if you need to keep the 0 , i think you must use the type VARCHAR.
ReplyDeleteI get the below error:
ReplyDeleteupload file name: test.csv You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name,Country,Currency) values('Company name','Country','Currency')' at line 1
Please help me resolve this.
Thanks in advance
getting error on running file
ReplyDeleteCannot GET /%3C