Custom cron importcsv

[jmbarra78]

Hi,
I need a cron task that import csv files every 5 minutes, without headers. ImportCSV plugin needs headers in csv files, so I would like develop my custom code. My first problem is csv file is not found... This is my piece of code:

defined('_JEXEC') or die();
// Get a db connection.
$db = FabrikWorker::getDbo();
//import csv file
define('CSV_PATH','../proyecto/media/csv/cliente1/finca1/invernadero1/');
$csv_file = CSV_PATH . "data.csv"; // Name of your CSV file
if (!file_exists($csv_file)) {
        echo "File not found. Make sure you specified the correct path.\n";
        exit;
}

I get "File not found..."My project is in http://www.mydomain.com/proyecto
what's wrong in CSV path?
If I use code below instead of "if (!file_exists($csv_file))..."

$csvfile = fopen($csv_file, 'r') || die("File not found");
$datos = fgetcsv($csvfile, 1000, ",");

...I get NULL with var_dump($datos)...

 

[troester]

Try with
JPATH_SITE.'/media/csv/cliente1/finca1/invernadero1/'

 

[jmbarra78]

Thanks Troester JPATH_SITE works, but I get NULL

defined('_JEXEC') or die();
//$db = JFactory::getDbo();
$db = FabrikWorker::getDbo();
$path=JPATH_SITE.'/media/csv/cliente1/finca1/invernadero1/';
$csv_file = $path . "data.csv"; // Name of your CSV file
print_r($csv_file);
if (!file_exists($csv_file)) {
        echo "File not found. Make sure you specified the correct path.\n";
        exit;
}
$csvfile = fopen($csv_file, 'r') || die("No se puede abrir fichero de datos");
$datos = fgetcsv($csvfile, 1000, ",");
print_r($datos); //I get NULL

My data.csv:

2014-08-07 19:20:00,62,72,10,39,0,0,0,0,1,1,1
2014-08-07 19:50:00,35,44,20,32,0,0,0,0,1,1,1
2014-08-07 20:20:00,31,102,24,30,0,0,0,0,1,1,1

 

[jmbarra78]

I've just test my code and works for me, I thought NULL output was an error. I've tested adding this code:

if (($handle = fopen($csv_file, 'r')) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       
        $total = count($data);
 
        for ($e=0; $e < $total; $e++) {
            echo $data[$e] . "<br />";
        }
        echo "<br />";
    }
    die("Fin");//Stop here to see the output
    fclose($handle);
}

CSV File is shown

I will continue developing the code...

 

[jmbarra78]

I would like share my code. This php cron task is a simple (very simple) way to import all csv files of a directory. CSV Files don't have headers. If import fails send an email to me and save file names imported successfully in a text file:
Suggestions are welcome:

<?php
function enviar_correo(){
$para      = 'xxxxx@gmail.com';
$titulo    = 'Error de importacion de fichero';
$mensaje  = 'Error de importacion de fichero del cliente1 Finca1 Invernadero1';
$cabeceras = 'De: Tensiometros' . "\r\n" .
    'Responder a:xxxxxxx@gmail.com' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();
 
mail($para, $titulo, $mensaje, $cabeceras);
}
//Script para importar archivos csv sin cabecera.
//Le incorporamos la cabecera y elegimos los campos de destino
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();
$db = JFactory::getDbo();
//$db = FabrikWorker::getDbo();
$path=JPATH_SITE.'/media/csv/cliente1/finca1/invernadero1';
//$csv_file = $path . "data.csv"; // Name of your CSV file
$query = $db->getQuery(true);
$columns = array('date_time','fecha','T1','T2','T3','T4','CE1','CE2','TA','HR','idinvernadero','idfinca','idcliente');
$idinvernadero=1;
$idfinca=1;
$idcliente=1;
$fecha=date("Y-m-d H:m:s");
$total=0;
$path_done=$path.'/done';
//Filter only CSV files
$filter = "\.CSV$|\.csv$";
$exclude = array('done', '.svn', 'CVS');
$arraycsv_files = JFolder::files($path, $filter, false, true, $exclude);
foreach ($arraycsv_files as $id){
$filecsv=$id;
$filename=substr($filecsv,strlen($path),strlen($filecsv));
var_dump($filename);
if (($handle = fopen($filecsv, 'r')) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $values = array($fecha,$data[0],$data[1],$data[2],$data[3],$data[4],$data[5],$data[6],$data[7],$data[8],$idinvernadero,$idfinca,$idcliente);
        $total++;
        if ($total==1)
        {
        $query
            ->insert($db->quoteName('fb_unidades'))
            ->columns($db->quoteName($columns))
            ->values(implode(',',$db->quote($values)));
        }
        else
        {
            $query
            ->insert($db->quoteName('fb_unidades'))
            ->values(implode(',',$db->quote($values)));
        }
        $db->setQuery($query);
    }//end_while
        try {
        // Execute the query
        $result = $db->execute();
        }
        catch (Exception $ex) {
            enviar_correo();
        // catch any database errors.
   
        }
    fclose($handle);
}
else
    die("Open File Error");
//Move file to done directory
rename($path . $filename,$path_done.$filename);
$procesados=fopen($path.'/'."procesados.txt","a") or die("Error in file creation");
fputs($procesados,substr($filename,1));
fclose($procesados);
}//end_foreach
?>

 

[jmbarra78]

Why doesn't the scheduled task work? The script works if you press run button, but doesn't work automatically. I've scheduled every 5 minutes.
I tried a simple script without success

Regards!

 

[troester]

The scheduled task is run only if there's a frontend activity on your site.

 

[jmbarra78]

I have frontend activity in my website but sheduled task is not run... What kind of activity should be?

 

[cheesegrits]

Any front end page load should kick off our cron plugin as the very last thing to happen during page load, which will then check to see if any scheduled tasks need running. Check that the Fabrik Cron system plugin is installed and enabled.

If that's not the issue, the only other thing to check is that the plugin is published, and you haven't selected "Require querystring".

Have you tried setting "Log Events" to yes, and see if anything shows up in the fabrik_log table?

-- hugh

 

[troester]

Check that the Fabrik Cron system plugin is installed and enabled.

Yup, I've run into this also several times. In former (Fabrik2) times this was tested by Fabrik (you couldn't create a scheduled task at all if the plugin was not there).

 

[cheesegrits]

Ah, I never knew that, LOL! Would probably be a good thing to do in 3.x.

I also need to check to see if things still become invisible if they bug out. We used to have an issue where, if the plugin fatally errored such that our system plugin was unable to mark it as runable again, it would disappear from the list of tasks, unless you selected some specific "status" to filter the list by.

-- hugh