Import Excel into SQL database using php

Question

I looked everywhere for this code but most of them are dedicated or just have errors and none work.

I want to import an Excel file into my sql database using php codes. Till now I have tried over 5 codes and plugins (in github) but last code has best result but still doesn't work.

$file = "../uploads/".$newfilename;
//echo $file;
$file_open = fopen($file,"r");
while(($csv = fgetcsv($file_open, 1000, ";")) !== false)
{
    $data = array_map("utf8_encode", $csv);
    print_r($data);
    $name = $csv[0];
    $pro_name = $csv[0];
    $brand = $csv[0];
    $tell = $csv[0];
    try{
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $db->prepare("INSERT INTO companies 
            (company_name, pro_name, brand, tell)
            VALUES(:a1, :a2, :a3, :a4)");
        $stmt->bindparam(':a1',$name);
        $stmt->bindparam(':a2',$pro_name);
        $stmt->bindparam(':a3',$brand);
        $stmt->bindparam(':a4',$tell);
        $stmt->execute();
        echo 'done';
    }catch(PDOException $e){
        //echo "error";
    }
}

The result from this code is this:

Array
(
    [0] => PK!ª÷X¤z[Content_Types].xml ¢( ÌTÉjÃ0½úF×+I¡'.Ç6ôklØÐLÒäï
    [1] => vJÈi ½ØØÒ¼eyÑª*£%4Î&¢wE6uÚØ<Ó·Î£Õªt±£áíÍ`ºöW[LDAä¤Ä´Jaì<X>É\¨ñgÈ¥Wé\å ûÝîL%°Ô¡C/©EIÑëoÌÑóæ^Må}iRE,T.> é¸,3)h.*ÑPªÊØÃaDl<Ê Äv¤[W1W6Â°0ïØú   úä´«mÝ#
ÑXzW{«R~¹097Ï´mMÓ¢¸RÆîtáo.£l^½+©ý5À-uôÿû?ÒA¼s çïGÒÀ\Òº¼²Û
è%æBÐâmÎ¯.à'ö91ãà<rhß]dÔÕÏ@ÈÀ>4-ß#°=áA0B±ônÙdúðÿÿPK!µU0#õL_rels/.rels ¢( 
)

I have removed this line also : $data = array_map("utf8_encode", $csv); but it doesn't make any difference.

(Notice: I have to use utf8 unidcode because I have Persian words that need to be written in database!)

any solution?

the link you have shared is not working, would you please give me the working link? — M A K
– M A K, Commented Nov 4, 2020 at 6:31

LeleDumbo · Accepted Answer · 2018-10-07 20:45:56Z

1

You use fgetcsv so you assume the given excel file is formatted as CSV.
The "result" (which actually comes from your print_r($data); statement, so it's not really a result in any way), shows that the file is a zipped XML, likely to be the .xlsx format.

From just 2 points above, your current "looking for existing solution written by others" approach(*) may need to be improved. Get a proper Excel library (e.g. PhpSpreadsheet), read and understand its documentation to be able to use it for your specific needs. Most important of all, try to write it your own, don't copy paste code you don't understand from wild wild web.

(*) I'm talking about these two sentences:

I looked everywhere for this code but most of them are dedicated or just have errors and none work.

Till now I have tried over 5 codes and plugins (in github) but last code has best result but still doesn't work.

answered Oct 7, 2018 at 20:45

LeleDumbo

9,3804 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MSH Developer Over a year ago

you are right, but sometimes there isn't enough time to learn and understand a code so I had to copy a working code to my project so I can finish it in time.

LeleDumbo Over a year ago

trust me, that won't make the project "finish" anyhow. as you already experienced, you're just stuck. if you spend the time for learning a library (I admit if you write the library yourself, it might not be feasible) instead, you will be able to use it to finish your project on time. this is quite a trivial case, despite the schema will differ from one case to another, the flow stays the same.

MSH Developer · Accepted Answer · 2018-10-07 22:38:15Z

0

Finally I found a website that has a full documents and description. In simple word the solution is to convert excel (.xlsx) file into XML (.xml) file and then import it with a simple code. https://www.ibm.com/developerworks/library/os-phpexcel/index.html

answered Oct 7, 2018 at 22:38

MSH Developer

114 bronze badges

Collectives™ on Stack Overflow

Import Excel into SQL database using php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related