automatically import and sanitize excel file to mysql
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I have a LAMP server that hosts a form and data usually gets inputted via the form. I now have data that I would like to automatically import to another form on a different table. The data will be retrieved by the lamp server via FTP to a Windows server that generates the excel files. The files are generated every 5 minutes and are deleted in FIFO. There is usually 12 excel files in the directory at all times, the names are incremented each time a file is created. If I open up the file in excel, the 40th row contains the first row of data. If I import the file via myphpadmin it creates a table Sheet1 as follows: Col A is date, B is time, C is seconds. D, F, H, J, L, N; contain data with the most recent row at the bottom. On the lamp server A is id, B is TIMESTAMP, C thru H are data columns. Newest data should be displayed at the top and the date/time from the row data should be displayed. If the server is restarted it should check for the newest row of data. The TIMESTAMP doesn't need to be displayed on the form but I would like to keep it if possible. Data is in the form of a number up to 4 digits with one decimal place xxxx.x

Attached is a excel file and the form that displays the data: https://drive.google.com/file/d/0B5l0BFwSXhircDZoeGVjNjBVWVU/view?usp=sharing

I would like to update the data on the form every 60 seconds using something like this: http://crunchify.com/how-to-refresh-div-content-without-reloading-page-using-jquery-and-ajax/

-

Edit: You are correct that, should have been H and not I. My mistake. This is not an outward facing site so it's just a single page, not an CMS. The second file in the attachment is what it looks like, only I removed the inputs from the top half of form. I'm attaching an example of what the original form looks like.

https://drive.google.com/file/d/0B5l0BFwSXhiraTJveUhOLWVoRUU/view?usp=sharing

I don't want to restart the server at all, but should it restart for whatever reason I just wanted to make sure it starts up and imports automatically without any user input.

Your task description is too vague. First, do you need the code for file retrieval via ftp or you already have it implemented? To make it clear, do all the excel files contain the same data with the newest data in the latest file or they have different data that have to be combined from all the available files? Regarding the import - do you mean that you need it converted into the format of "A is id, B is TIMESTAMP, C thru I are data columns"? What do you want to see in the TIMESTAMP field? Should it be the combined data from DATE, TIME, SECONDS fields from original file?
dekkard almost 4 years ago
The FTP needs to be setup as well. Correct, they contain other information but the only important row is the 40th row. That is correct, they need to be converted. The separate date and time needs to be converted to replace the current timestamp field.
dgz almost 4 years ago
awarded to Wikimedia

Crowdsource coding tasks.

1 Solution


Hello dgz,
There is a lot about your problem i dont understand. But as far as i understood it you want to fetch a couple of excel files via ftp to your lamp server. They have the structure of your example file. And then put the contents in a mysql table, which has the form id,timestamp,data1,data2,data3,data4,data5,data6.
And you named the columens in your mysql table for whatever reasons A,B,C,D,E,F,G,H,I.
Your example-excel document only contains 6 data values so it should at least be A up to H and not up to I.
That beeing said here is an example solution as a php script which you could run every 5min via cron to update the values in your database. If you need anything else or i understood you wrong please tell shouldnt be to hard to give you a working solution:

#!/usr/bin/php
<?php
//Ftp-Data
$ftp_server="";
$ftp_user_name="";
$ftp_user_pass="";
$ftp_directory='/html/test';
//MYSQL-Data
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$table = "myTable";

// set up basic ftp connection
$conn_id = ftp_connect($ftp_server);
// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass); 
$mode = ftp_pasv($conn_id, TRUE);
// get contents of the current directory
$contents = ftp_nlist($conn_id, $ftp_directory);
// connect to mysql databse
$conn = new mysqli($servername, $username, $password, $dbname);
// if that worked
if (!$conn->connect_error) {
    //get thorugh all the files on the ftp server
    foreach($contents as $file){
        // if its an xlsx file
        if(strcasecmp(trim(pathinfo($file,PATHINFO_EXTENSION)),"xlsx")==0){
            // generate temporay file to download to
            $lfile=tempnam(sys_get_temp_dir(),'xlsx');
            // download file and if succesfull
            if (ftp_get($conn_id, $lfile, $file, FTP_BINARY)) {
                //open the file and read the data
                if($xml=simplexml_load_file('zip://'.$lfile."#xl/worksheets/sheet1.xml")) {
                //select the row its actually not 40 but 37, because empty rows dont count
                $row=$xml->sheetData->row[36];
                //Create the Timestamp from columns A,B,C
                $date=explode('/',(($row->c[0]->v)?$row->c[0]->v:$row->c[0]->is->t));
                $time=explode(':',(($row->c[1]->v)?$row->c[1]->v:$row->c[1]->is->t));
                $secs=(($row->c[2]->v)?$row->c[2]->v:$row->c[2]->is->t);
                $tstamp=mktime($time[0],$time[1],$secs+0,$date[1],$date[2],$date[0]);
                //Insert Data and Timestamp in Database
                $sql = "INSERT INTO ".$table ."'(B, C, D, E, F, G, H)
                        VALUES ('".$tstamp."',".
                                (($row->c[3]->v)?$row->c[3]->v:$row->c[3]->is->t)."',".
                                (($row->c[5]->v)?$row->c[5]->v:$row->c[5]->is->t)."',".
                                (($row->c[7]->v)?$row->c[7]->v:$row->c[7]->is->t)."',".
                                (($row->c[9]->v)?$row->c[9]->v:$row->c[9]->is->t)."',".
                                (($row->c[11]->v)?$row->c[11]->v:$row->c[11]->is->t)."',".
                                (($row->c[13]->v)?$row->c[13]->v:$row->c[13]->is->t)."');";
            }
        //Delete the temporay file afterwards
        unlink($lfile);
        }
    }
}
// close the ftp connection
ftp_close($conn_id);
// close the mysql connection
$conn->close()
?>

What confuses me most is that you talk about forms and ajax and so on. If you want to show the data from database on your homepage this shouldnt be a problem. But i would need to see the homepage then, need to know which cms you use and so on. And anyway forms are for retrieving user input in html. A form is a bad choice for showing data. Hope this helps, it definitly solves what you suggest you need in your topic

Just read your description again, you actually want to restart the whole lamp server before retreiving new data? sounds rather strange, but then you need to put it on the server as an startup script, by putting the file in /etc/init.d or something not as a cronjob then ofc
Ibenor almost 4 years ago