PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Dec 15, 2017 12:54 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: How to upload .xls files
PostPosted: Fri Feb 15, 2013 1:06 am 
Offline
Forum Newbie

Joined: Fri Feb 08, 2013 3:21 am
Posts: 12
Any good tutorial links how to upload .xls file in mysql?

Note: not .csv file.

thanks


Top
 Profile  
 
PostPosted: Fri Feb 15, 2013 10:48 am 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13459
Location: New York, NY, US
The upload part is just a standard file upload. Then PHPExcelReader

http://sourceforge.net/projects/phpexcelreader/

_________________
(#10850)


Top
 Profile  
 
PostPosted: Mon Feb 18, 2013 8:11 am 
Offline
Tranquility In Moderation
User avatar

Joined: Sun Feb 06, 2005 8:18 pm
Posts: 5001
Location: Indiana
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.


Top
 Profile  
 
PostPosted: Mon Feb 18, 2013 9:24 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
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)?


Top
 Profile  
 
PostPosted: Mon Feb 18, 2013 9:46 pm 
Offline
Forum Newbie

Joined: Fri Feb 08, 2013 3:21 am
Posts: 12
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.

Syntax: [ Download ] [ Hide ]
<?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>


Top
 Profile  
 
PostPosted: Mon Feb 18, 2013 10:11 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
(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.


Top
 Profile  
 
PostPosted: Fri Jul 05, 2013 11:58 am 
Offline
Forum Commoner

Joined: Mon Jul 01, 2013 1:49 pm
Posts: 25
Upload it on server then put on mysql..it will enhance speed


Top
 Profile  
 
PostPosted: Tue May 27, 2014 6:57 am 
Offline
Forum Newbie

Joined: Wed Apr 02, 2014 3:32 am
Posts: 8
Location: New delhi
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group