Any good tutorial links how to upload .xls file in mysql?
Note: not .csv file.
thanks
How to upload .xls files
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: How to upload .xls files
The upload part is just a standard file upload. Then PHPExcelReader
http://sourceforge.net/projects/phpexcelreader/
http://sourceforge.net/projects/phpexcelreader/
(#10850)
Re: How to upload .xls files
Do not uploat TO mysql. Rather, upload to server and then store the link/filename in the mysql database.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Re: How to upload .xls files
I think you are asking how to upload data in an .xls spreadsheet to a MySQL table. Are you planning to do this once, using a control panel, such as PHPMyAdmin, or using a script (to do it repeatedly)?japeth wrote:Any good tutorial links how to upload .xls file in mysql?
Note: not .csv file.
Re: How to upload .xls files
Dear Califdon!
thank you
I found a bit solution over the net. bt thats not 100% solution to my problem.
I wish to remove unnecessary rows in my csv automatically,
please take a look at my scr.shot.
http://tinypic.com/view.php?pic=11k8602&s=6
and the codes i found.
thank you
I found a bit solution over the net. bt thats not 100% solution to my problem.
I wish to remove unnecessary rows in my csv automatically,
please take a look at my scr.shot.
http://tinypic.com/view.php?pic=11k8602&s=6
and the codes i found.
Code: Select all
<?php
$message = null;
$allowed_extensions = array('csv');
$upload_path = '';
if (!empty($_FILES['file'])) {
if ($_FILES['file']['error'] == 0) {
// check extension
$file = explode(".", $_FILES['file']['name']);
$extension = array_pop($file);
if (in_array($extension, $allowed_extensions)) {
if (move_uploaded_file($_FILES['file']['tmp_name'], $upload_path.'/'.$_FILES['file']['name'])) {
if (($handle = fopen($upload_path.'/'.$_FILES['file']['name'], "r")) !== false) {
$keys = array();
$out = array();
$insert = array();
$line = 1;
while (($row = fgetcsv($handle, 0, ',', '"')) !== FALSE) {
foreach($row as $key => $value) {
if ($line === 1) {
$keys[$key] = $value;
} else {
$out[$line][$key] = $value;
}
}
$line++;
}
fclose($handle);
if (!empty($keys) && !empty($out)) {
$db = new PDO('mysql:host=localhost;dbname=attendance', 'root', 'root');
$db->exec("SET CHARACTER SET utf8");
foreach($out as $key => $value) {
echo $sql = "INSERT INTO `report` (`";
$sql .= implode("`, `", $keys);
$sql .= "`) VALUES (";
$sql .= implode(", ", array_fill(0, count($keys), "?"));
$sql .= ")";
$statement = $db->prepare($sql);
$statement->execute($value);
}
$message = '<span class="green">File has been uploaded successfully</span>';
}
}
}
} else {
$message = '<span class="red">Only .csv file format is allowed</span>';
}
} else {
$message = '<span class="red">There was a problem with your file</span>';
}
}
?>
<!DOCTYPE HTML>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Upload CSV to MySQL</title>
<meta name="description" content="" />
<meta name="keywords" content="" />
<link href="/css/core.css" rel="stylesheet" type="text/css" />
<!--[if lt IE 9]>
<script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
</head>
<body>
<section id="wrapper">
<form action="" method="post" enctype="multipart/form-data">
<table cellpadding="0" cellspacing="0" border="0" class="table">
<tr>
<th><label for="file">Select file</label> <?php echo $message; ?></th>
</tr>
<tr>
<td><input type="file" name="file" id="file" size="30" /></td>
</tr>
<tr>
<td><input type="submit" id="btn" class="fl_l" value="Submit" /></td>
</tr>
</table>
</form>
</section>
</body>
</html>Re: How to upload .xls files
(For future reference, please make it easier to read any code that you want to post here, by enclosing it with the syntax tags, as I have done for you, above--you can use the "PHP Code" button in the Post A Reply form to do this.)
Now, to get more help, please answer my question, Are you planning to do this once, using a control panel, such as PHPMyAdmin, or using a script (to do it repeatedly)? Otherwise I have no idea how to help you.
Now, to get more help, please answer my question, Are you planning to do this once, using a control panel, such as PHPMyAdmin, or using a script (to do it repeatedly)? Otherwise I have no idea how to help you.
Re: How to upload .xls files
Upload it on server then put on mysql..it will enhance speed
-
finesofttechnologies
- Forum Newbie
- Posts: 8
- Joined: Wed Apr 02, 2014 3:32 am
- Location: New delhi
Re: How to upload .xls files
Save your Excel data as a csv file (In Excel 2007 using Save As)
Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
Enter this command:
LOAD DATA LOCAL INFILE 'C:\\temp\\yourfile.csv' INTO TABLE database.table FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (field1, field2);
Completed
Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.
Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)
Enter this command:
LOAD DATA LOCAL INFILE 'C:\\temp\\yourfile.csv' INTO TABLE database.table FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (field1, field2);
Completed