How to upload .xls files

Tutorials on PHP, databases and other aspects of web development. Before posting a question, check in here to see whether there's a tutorial that covers your problem.

Moderator: General Moderators

Post Reply
japeth
Forum Newbie
Posts: 12
Joined: Fri Feb 08, 2013 2:21 am

How to upload .xls files

Post by japeth »

Any good tutorial links how to upload .xls file in mysql?

Note: not .csv file.

thanks
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to upload .xls files

Post by Christopher »

The upload part is just a standard file upload. Then PHPExcelReader

http://sourceforge.net/projects/phpexcelreader/
(#10850)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: How to upload .xls files

Post by s.dot »

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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to upload .xls files

Post by califdon »

japeth wrote:Any good tutorial links how to upload .xls file in mysql?

Note: not .csv file.
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
Forum Newbie
Posts: 12
Joined: Fri Feb 08, 2013 2:21 am

Re: How to upload .xls files

Post by japeth »

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.

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>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to upload .xls files

Post by califdon »

(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.
clark551
Forum Commoner
Posts: 25
Joined: Mon Jul 01, 2013 1:49 pm

Re: How to upload .xls files

Post by clark551 »

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

Post by finesofttechnologies »

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
Post Reply