Importing excel data in to sql database using php

Question

I am trying to import data from excel sheet to my sql database using the following php code:

$hostname = "localhost";
$username = "root";
$password = "";
$database = "unhrd_fund_balance";


$conn = mysqli_connect("$hostname","$username","$password","$database") or die(mysql_error());


?>

<form name="import" method="post" enctype="multipart/form-data">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
</form>



<?php
if(isset($_POST["submit"]))
{
    $file = $_FILES['file']['tmp_name'];
    $handle = fopen($file, "r");
    $c = 0;
    while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
    {
        $progkey = $filesop[0];
        $grantkey = $filesop[1];
        $TOD = $filesop[2];
        $TDD=$filesop[3];
        $fund=$filesop[4];
        $orderkey=$filesop[5];
        $budget=$filesop[6];
        $precommit=$filesop[7];
        $commit=$filesop[8];
        $actuals=$filesop[9];
        $totalcommit=$filesop[10];
        $availbudget=$filesop[11];

        $sql = mysqli_query("INSERT INTO csv (FUNDED_PROG_KEY, GRANT_KEY, TOD, TDD, FUND, ORDER_KEY, BUDGET_ALLOC, PRE_COMMIT, COMMIT, ACTUALS, TOTAL_COMMIT,AVAILABLE_BUDGET) VALUES ('$progkey', '$grantkey','$TOD','$TDD','$fund','$orderkey','$budget','$precommit','$commit,'$actuals','$totalcommit','$availbudget')");


    }

        if($sql){
            echo "You database has imported successfully";
        }else{
            echo "Sorry! There is some problem.";
        }
}
?>

My SQL database is:

CREATE TABLE `fund_balances` (
  `FUNDED_PROG_KEY` varchar(20) NOT NULL,
  `GRANT_KEY` varchar(25) NOT NULL,
  `TOD` date NOT NULL,
  `TDD` date NOT NULL,
  `FUND` varchar(35) NOT NULL,
  `ORDER_KEY` int(30) NOT NULL,
  `BUDGET_ALLOC` bigint(255) NOT NULL,
  `PRE_COMMIT` bigint(255) NOT NULL,
  `COMMIT` bigint(255) NOT NULL,
  `ACTUALS` bigint(255) NOT NULL,
  `TOTAL_COMMIT` bigint(255) NOT NULL,
  `AVAILABLE_BUDGET` bigint(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am getting the following errors when I attach the xlsx file and click on submit in the form:

Notice: Undefined offset: 3 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 36

Notice: Undefined offset: 4 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 37

Notice: Undefined offset: 5 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 38

Notice: Undefined offset: 6 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 39

Notice: Undefined offset: 7 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 40

Notice: Undefined offset: 8 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 41

Notice: Undefined offset: 9 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 42

Notice: Undefined offset: 10 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 43

Notice: Undefined offset: 11 in /Applications/XAMPP/xamppfiles/htdocs/Web/Form1.php on line 44

You're mixing two things up. It sounds like you're trying to read a native Excel file into code designed to process a CSV file. They're not the same thing. If you want to read it as CSV, export it from Excel as such. If you want to read a Native Excel file, look at the PHPExcel library. — Ben Hillier

Bart Friederichs Bart Friederichs · Accepted Answer · 2016-06-21T07:12:11

First, those are not errors, but notices telling you there is no N'th element in the $filesop array.

Second, I do not think you can read an XLSX file with fgetcsv, you will need an external library for that (PHPExcel for example).

Third, you are INSERTing into csv, while the name of your table is fund_balances.

Furthermore, this: "$hostname" is useless, just use $hostname.

Finally, read up on SQL injection and prepared statements.

Importing excel data in to sql database using php

1 Answers