将100个Excel文件传输到MySQL

Question

I use PHP dbase extention to read dbase files and upload them into MySQP.

My Code:

 if(dbase_open($FileDir,0)){

                    $dbOpen = dbase_open($FileDir,0);
                    $records = dbase_numrecords($dbOpen);

                    $qry = "";
                    $qry = "INSERT INTO ".$Table." (`SCADA`, `Date`, `PlantNo`, `State`, `SubState`, `Frequency`) VALUES ";

                    for ($i=1; $i<=$records; $i++){

                        $rows = dbase_get_record_with_names($dbOpen,$i);
                        $qry .= '('.$SCADA.','.$rows['Date'].','.$rows['PlantNo'].','.$rows['State'].','.$rows['SubState'].','.$rows['Frequency'].'),';

                    }

                    $qry = substr($qry, 0, -1);
                    $Insert = $conn -> prepare($qry);
                    $Insert -> execute();

Now I have to do the same but with xls files! I know that I can convert them into csv and import them via PHP, but I have 100 xls files... How can I transfer them to MySQL? Ant idea?

Answer 1

Direct import from Excel to MySQL will not be possible without a third party proprietary product, that you would need to buy like - dbForge Studio or something (I have not tried this one, so I cant even recommend it). Tool called phpmyadmin used to have this functionality, but its depreciated since version 3.4.5, so if you install an old one it might be an option for you. That really depends on several factors.

For a general start, ofcourse, you should make sure that the file you want to import has the same columns, and column order as the database where you want to import it into.

If you convert, or export your xls files into CSV (its a seperate story on how to do that and I will not cover here, plenty of resources or online converters out there), you can merge all of them either in Excel, or from your console. On all modern operating systems you can just put all files in the same directory, cd or dir into it and run "cat *.csv > merged.csv" which will merge all files into one. (copy /b *.csv merged.csv on windows machines)

Once you have a single merged file you can run php to execute the mysql import - "LOAD DATA INFILE 'c:/path/to/file/merged.csv' INTO TABLE tablename" (more about it here - http://dev.mysql.com/doc/refman/5.1/en/load-data.html)

Ofcourse you can also automate exporting XLS into CSV by using a php library like PHPExcel - https://phpexcel.codeplex.com/