1

I am going crazy with one problem in my web app.

I have a SCADA system that everyday dumps excel files to my server with data from one thermal plant.

I have a web app in PHP / Jquery that reads this data from a MySQL database and show this in charts and tables.

But I have to manually import the excel files to MySQL. What I want to do, is to build a PHP script, that will do as follow:

The user tries to make a chart, and if the data is not on MySQL it will ask if he wants to import the data from the excel files in the server. Than the excel files are copied into MySQL temporary tables and inserted into the correspond table. Then the data can be displayed to the user and will keep stored in the MySQL database.

All its working, but I need make an automatic way to import this data from the excel files to MySQL tables.

Thanks in advance for your help

devwebapp
  • 135
  • 2
  • 10
  • So which of the many PHP libraries capable of reading Excel files have you looked at, and what problems have you encountered with them? – Mark Baker Dec 18 '13 at 23:33
  • Thank you Mark for taking the time to read this! I have looked into some.. But most of them is necessary to convert the excel file into csv. Now I was looking into http://phpexcel.codeplex.com/. Note: The excel files have personalised extensions. They can be open in MS access. – devwebapp Dec 18 '13 at 23:35
  • 1
    In addition to my own PHPExcel, There are a number of PHP libraries for reading real Excel BIFF (.xls) or OfficeOpenXML (.xlsx) files (not simply csv files) listed in my answer to [this question](http://stackoverflow.com/questions/3930975/alternative-for-php-excel) – Mark Baker Dec 18 '13 at 23:51
  • Thank you Mark! I'll try PHPExcel and SimpleExcel! – devwebapp Dec 19 '13 at 00:07
  • Mark, just one question. My files are dBase IV not excel. Is it possible with this libraries? – devwebapp Dec 19 '13 at 10:02
  • If they're dBase IV files, then "No".... dBase IV format files are totally unrelated to any spreadsheet file formats. – Mark Baker Dec 19 '13 at 10:07
  • If you're working with dBase IV files in PHP, then http://www.php.net/manual/en/book.dbase.php and http://pecl.php.net/package/dbase – Mark Baker Dec 19 '13 at 10:07
  • Thank you Mark. I was using this script. http://www.ostalks.com/2009/12/31/import-dbf-files-to-mysql-using-php/ But I am using a PHP version that does not support this library. – devwebapp Dec 19 '13 at 10:42
  • http://www.phpclasses.org/package/1302-PHP-Extract-information-from-a-DBF-database-file.html or http://www.phpclasses.org/package/2673-PHP-Access-dbf-foxpro-files-without-PHP-ext-.html may help, pure PHP libraries for reading/writing dbf files – Mark Baker Dec 19 '13 at 11:09

1 Answers1

1

hey there actually i had the same problem before i did the following to solve it: create a function in php to execute query to import the file from your destination public function InsertNewData($filename){ $dbtable = substr($filename, 0,-4);

    $sql = " LOAD DATA LOCAL INFILE 'http://your-ip-and-root/uploads/$filename'"
         . " INTO TABLE $dbtable FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\n'";
    $error = " <font color = 'red'>error Load Data the file Please contact the DB Admin </font>";
    $connection = ResourceManager::getConnection();
    $resultado = 0;
    $connection->beginTransaction();
    try {
        $prepare = $connection->prepare($sql);                     
        $prepare->execute();    
        $connection->commit();
    } catch (Exception $e) {
       echo $error;          
        $connection->rollBack();

    }
    $connection = null;

} and then you can call this function from your page when upload excel file is completed

hope it helped.

Sameh Alhosni
  • 26
  • 1