excel data into database using php

Question

this is my sample excelsheet values

State   1972-1973   1973-1974   1974-1975   1975-1976   1976-1977   1977-1978   1978-1979   1979-1980   1980-1981   1981-1982
Alabama $733,750    $1,066,300  $1,136,244  $1,343,670  $1,476,307  $1,642,927  $1,507,315  $1,849,825  $2,402,873  $2,079,000 
Alaska  $1,019,000  $1,100,000  $1,180,500  $1,172,300  $1,415,300  $1,411,700  $1,666,500  $2,026,400  $3,409,800  $7,200,000 
Arkansas    $890,496    $1,173,304  $1,193,362  $1,735,266  $1,824,536  $1,929,071  $2,090,590  $2,173,595  $2,042,632  $2,203,864

through php coding i need to store in db.how to store it.

change you exel document to access database and follow this tutorial use access database with php — Haben
– Haben, Commented Aug 20, 2013 at 5:26
did i need to create my table with column that is state and year before exporting? — sangee
– sangee, Commented Aug 20, 2013 at 5:35

Namrata Shrivas · Accepted Answer · 2014-04-01 10:09:20Z

just try this

<title>Upload page</title>
<style type="text/css">
body {
background: #E3F4FC;
font: normal 14px/30px Helvetica, Arial, sans-serif;
    color: #2b2b2b;
}
a {
color:#898989;
font-size:14px;
font-weight:bold;
text-decoration:none;
}
a:hover {
color:#CC0033;
}

h1 {
font: bold 14px Helvetica, Arial, sans-serif;
color: #CC0033;
}
h2 {
font: bold 14px Helvetica, Arial, sans-serif;
color: #898989;
}
#container {
background: #CCC;
margin: 100px auto;
width: 945px;
}
#form           {padding: 20px 150px;}
#form input     {margin-bottom: 20px;}
</style>
</head>
<body>
<div id="container">
<div id="form">

<?php

include "e2.php"; //Connect to Database

            $deleterecords = "TRUNCATE TABLE books"; //empty the table of its current records
            mysql_query($deleterecords);

            //Upload File
            if (isset($_POST['submit'])) {
                if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
                echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
                    echo "<h2>Displaying contents:</h2>";
                    readfile($_FILES['filename']['tmp_name']);
                }

                //Import uploaded file to Database
                $handle = fopen($_FILES['filename']['tmp_name'], "r");

                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $import="INSERT INTO books (BookID,Title,Author,PublisherName,CopyrightYear) VALUES('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";

                    mysql_query($import) or die(mysql_error());
                }

                fclose($handle);

                print "Import done";

                //view upload form
            }else {

                print "Upload new csv by browsing to file and clicking on Upload<br />\n";

                print "<form enctype='multipart/form-data' action='index.php' method='post'>";

                print "File name to import:<br />\n";

                print "<input size='50' type='file' name='filename'><br />\n";

                print "<input type='submit' name='submit' value='Upload'></form>";

            }

            ?>

            </div>
            </div>
            </body>
            </html>

this is e2.php

$db = mysql_connect("localhost","root","") or die("Could not connect.");

if(!$db)

    die("no db");

if(!mysql_select_db("books",$db))

    die("No database selected.");

?>

steven · Accepted Answer · 2013-08-20 05:44:04Z

0

the following worked for me if the source is a csv file:

$filename = "/path/to/your/csvfile.csv";
$handle = fopen ($filename, "r");
$buffer = "";
while (!feof($handle)) {
     $buffer .= fgets($handle, 16384);
}
$array = parse_csv_php($buffer);
// do what you want with your array...    




function parse_csv_php(&$data,$delim=';',$enclosure='"'){
        $enclosed=false;
        $fldcount=0;
        $linecount=0;
        $fldval='';
        for($i=0;$i<strlen($data);$i++) {
                $chr=$data{$i};
                switch($chr) {

                        case $enclosure:
                                if($enclosed&&$data{$i+1}==$enclosure) {
                                 $fldval.=$chr;
                                 ++$i; //skip next char
                                } else { $enclosed=!$enclosed; }
                        break;

                        case $delim:
                                if(!$enclosed) {
                                 $ret_array[$linecount][$fldcount++]=$fldval;
                                 $fldval='';
                                } else { $fldval.=$chr; }
                        break;

                        case "\r":
                                if(!$enclosed&&$data{$i+1}=="\n")
                                continue;

                        case "\n":
                                if(!$enclosed)  {
                                        $ret_array[$linecount][$fldcount]=$fldval;
                                        $linecount++;
                                        $fldcount=0;
                                        $fldval='';
                                } else { $fldval.=$chr; }
                        break;

                        default:
                                $fldval.=$chr;
                }
        }
        if($fldval) {
        $ret_array[$linecount][$fldcount]=$fldval;
        }
        return $ret_array;
}

edited Aug 20, 2013 at 5:44

answered Aug 20, 2013 at 5:27

steven

4,8952 gold badges31 silver badges61 bronze badges

3 Comments

sangee Over a year ago

how i need to use this?

sangee Over a year ago

s steven i have tried this and i got those values in array now i need to give my insert query

steven Over a year ago

you don't know how to do an insert query? I dont't know which db you are using and i know nothing about your db-structure so i think you should read some tutorials. If you are using mysql the query should be something like this: INSERT INTO tablename (fieldname1, fieldname2, fieldname3) VALUES ('value1','value2','value3')

Henry Ecker · Accepted Answer · 2024-06-15 19:25:07Z

Try this

simplex excel library and script you can create your own excel import to mysql.

 if (isset($_FILES['file'])) {
        
        require_once "simplexlsx.class.php";
        
        $xlsx = new SimpleXLSX( $_FILES['file']['tmp_name'] );
        
        echo '<h1>Parsing Result</h1>';
        echo '<table border="1" cellpadding="3" style="border-collapse: collapse">';
        
        list($cols,) = $xlsx->dimension();
        
        foreach( $xlsx->rows() as $k => $r) {
    //      if ($k == 0) continue; // skip first row
            echo '<tr>';
            for( $i = 0; $i < $cols; $i++)
                echo '<td>'.( (isset($r[$i])) ? $r[$i] : '&nbsp;' ).'</td>';
            echo '</tr>';
        }
        echo '</table>';
    }
    
    ?>
    <h1>Upload</h1>
    <form method="post" enctype="multipart/form-data">
    *.XLSX <input type="file" name="file"  />&nbsp;&nbsp;<input type="submit" value="Parse" />
    </form>

Collectives™ on Stack Overflow

excel data into database using php

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related