Import cvs file

[fantomasdm]

HI! I have a problem with create import filter. With table anagrafica,

Code: Select all

CREATE TABLE `anagrafica` (
  `idanagrafica` int(10) unsigned NOT NULL auto_increment,
  `arte` varchar(3) NOT NULL default '',
  `nome` varchar(30) NOT NULL default '',
  `cognome` varchar(30) NOT NULL default '',
  `sesso` char(1) NOT NULL default '',
  `cf` varchar(15) default NULL,
  `domicilio` varchar(60) NOT NULL default '',
  `email` varchar(35) default NULL,
  `telefono` varchar(15) default NULL,
  `datanascita` date NOT NULL default '0000-00-00',
  `luogonascita` varchar(30) NOT NULL default '',
  `statocivile` enum('Single','Sposato','Separato','Divorziato') NOT NULL default 'Single',
  `occupazione` varchar(40) default NULL,
  `altezza` int(10) unsigned default NULL,
  `peso` int(10) unsigned default NULL,
  `sangue` varchar(8) default NULL,
  `cmtipo` varchar(10) default NULL,
  `cmscadenza` date default NULL,
  `tesscadenza` date default NULL,
  `assscadenza` date default NULL,
  `matricola` varchar(20) default NULL,
  `dataiscrizione` date default NULL,
  `titolo` varchar(20) default NULL,
  `cintura` varchar(10) NOT NULL default '',
  `foto` blob NOT NULL,
  `foto_mimetype` varchar(30) default 'image/jpeg',
  PRIMARY KEY  (`idanagrafica`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;


in class anagrafica I have create this methods copy from other topic in this forum:

Code: Select all

function __import__csv(&$data, $defaultValues=array()){
    // build an array of Dataface_Record objects that are to be inserted based
    // on the CSV file data.
    $records = array();
   // first split the CSV file into an array of rows.
  $rows = explode("\n", $data);

  foreach ( $rows as $row ){
    if ($row setValues($defaultValues);
          // Now we add the values from the CSV file.
        $record->setValues(
            array(
                  'idanagrafica'=>$idanagrafica,
              'arte'=>$arte, 
              'nome'=>$nome,   
              'cognome'=>$cognome,   
              'sesso'=>$sesso,   
              'cf'=>$cf, 
              'domicilio'=>$domicilio,   
              'email'=>$email,   
              'telefono'=>$telefono,   
              'datanascita'=>$datanascita,   
              'luogonascita'=>$luogonascita,   
              'statocivile'=>$statocivile,   
              'occupazione'=>$occupazione,   
              'altezza'=>$altezza, 
              'peso'=>$peso,   
              'sangue'=>$sangue,   
              'cmtipo'=>$cmtipo,   
              'cmscadenza'=>$cmscadenza,   
              'tesscadenza'=>$tesscadenza,   
              'assscadenza'=>$assscadenza,   
              'matricola'=>$matricola,   
              'dataiscrizione'=>$dataiscrizione,   
              'titolo'=>$titolo,   
              'cintura'=>$cintura,
              'foto'=>$foto,
              'foto_mimetype'=>$foto_mimetype
                 )
            );
  // Let us check first if the row has some value. If it is empty don't insert.
     if (($row[$nome]) == '') {
    unset($record);}
    else    {
        // Since there is value, now add the record to the output array.
        $records[] = $record;
        unset($record);
    }
    }
   }
    // Now we return the array of records to be imported.
   
    return $records;
    unset($ws);
}


When I try to import this file cvs:

33,"HRD","pippo ","franco","Maschio","sdfsdfsd","Roma Via ciccio 40","fantomasdm@kkkk.it",342342432,22/04/75,"Roma","Single","Developer",178,80,"0RH-","NRM",10/07/08,10/07/08,10/07/08,"T0104",10/07/08,"YDJ","bianca","/gest/index.php?-action=getBlob&-table=anagrafica&-field=foto&-index=0&idanagrafica=30","image/png"

I get this error:

row:33,"HRD","pippo ","franco","Maschio","sdfsdfsd","Roma Via ciccio 40","fantomasdm@kkkk.it",342342432,22/04/75,"Roma","Single","Developer",178,80,"0RH-","NRM",10/07/08,10/07/08,10/07/08,"T0104",10/07/08,"YDJ","bianca","/gest/index.php?-action=getBlob&-table=anagrafica&-field=foto&-index=0&idanagrafica=30","image/png"
Fatal error: Cannot use object of type PEAR_Error as array in /var/www/vhosts/lotta.genesisliceo.it/httpdocs/dataface-0.7.1/Dataface/Table.php on line 2486

I have some question about import, the first field is `idanagrafica` that is auto_increment, I have to import it too? Is possible to skip some field in import? Like Blob fields as foto? Whe import field that using a valuelist as vocabulary I have to import code value o description: Ex for field [sangue] I have valuelist
[Sangue]
0RH+ = 0 RH positivo
0RH- = 0 RH negativo
A = Gruppo A
B = Gruppo B
AB = Gruppo AB
In cvs file I have to use 0RH+,0RH-,A,B or 0 RH positivo,0 RH negativo,Gruppo A...?

Thanks for help, and Sorry for my bad English!!
P.S. Happy Easter!!

[Paul]

Hi,

Firstly.. I don't think there is a need for the if statement here:

if ($row setValues($defaultValues);

instead use

Code: Select all

$record->setValues($defaultValues);

You are missing the following after foreach ( $rows as $row ){

Code: Select all

list($arte, $nome) = explode(',', $row);
$record = new Dataface_Record('anagrafica', array());
       
// We insert the default values for the record.
$record->setValues($defaultValues);

Now...

the first field is `idanagrafica` that is auto_increment, I have to import it too?

No you don't... Leave this out all together and Mysql will populate it for you automatically if set to auto increment

Is possible to skip some field in import?

Yes... Just specify the ones you want to import in your list as above

Code: Select all

ist($arte, $nome) = explode(',', $row);


Remember to watch your Mysql Structure as you have some cells as NOT NULL like

`foto` blob NOT NULL

Im not sure how you can import a photo/foto from a CSV file so if you have this as NOT NULL it will never work.

Best to import the details and then add the foto. Hence you need to have foto as NULL


Hope this Helps

also you have heaps of fields like `cintura` varchar(10) NOT NULL default '',

Why have Null and set a default to a space or nothing?

Paul

[fantomasdm]

Hi! Thanks for Help!!

I try to re-paste code, becose some parts are missing (But now don't use code tag!)

function __import__csv(&$data, $defaultValues=array()){
// build an array of Dataface_Record objects that are to be inserted based
// on the CSV file data.
$records = array();
// first split the CSV file into an array of rows.
$rows = explode("\n", $data);

foreach ( $rows as $row ){
if ($row != "") {
echo "row:".$row;
// We iterate through the rows and parse the fields
// so that they can be stored in a Dataface_Record object.
list( $arte, $nome, $cognome, $sesso, $cf, $domicilio, $email, $telefono, $datanascita, $luogonascita, $statocivile, $occupazione, $altezza, $peso, $sangue, $cmtipo, $cmscadenza, $tesscadenza, $assscadenza, $matricola, $dataiscrizione, $titolo, $cintura) = explode(',', $row);
$record = new Dataface_Record('anagrafica', array());

// We insert the default values for the record.
$record->setValues($defaultValues);
// Now we add the values from the CSV file.
$record->setValues(
array(
'arte'=>$arte,
'nome'=>$nome,
'cognome'=>$cognome,
'sesso'=>$sesso,
'cf'=>$cf,
'domicilio'=>$domicilio,
'email'=>$email,
'telefono'=>$telefono,
'datanascita'=>$datanascita,
'luogonascita'=>$luogonascita,
'statocivile'=>$statocivile,
'occupazione'=>$occupazione,
'altezza'=>$altezza,
'peso'=>$peso,
'sangue'=>$sangue,
'cmtipo'=>$cmtipo,
'cmscadenza'=>$cmscadenza,
'tesscadenza'=>$tesscadenza,
'assscadenza'=>$assscadenza,
'matricola'=>$matricola,
'dataiscrizione'=>$dataiscrizione,
'titolo'=>$titolo,
'cintura'=>$cintura,
)
);
print_r($record->getValues());
// Let us check first if the row has some value. If it is empty don't insert.
if (($row[$nome]) == '') {
unset($record);}
else {
// Since there is value, now add the record to the output array.
$records[] = $record;
unset($record);
}
}
}
// Now we return the array of records to be imported.

return $records;
}
the debug echo and print_r give me good result! But I get same error:
Fatal error: Cannot use object of type PEAR_Error as array in /var/www/vhosts/lotta.genesisliceo.it/httpdocs/dataface-0.7.1/Dataface/Table.php on line 2486

[fantomasdm]

HI! I found error!!
In fields.ini I have:
__sql__ = "select *,idanagrafica as eta from anagrafica"

becouse I using in class anagrafica the function:
function eta__display(&$record){
if ($record->strval('datanascita')=="")
return "0";
else
return round(dateDiff("-", date("Y-m-d", time()), $record->strval('datanascita'))/365, 0);
}

for calculate age!!

but If I remove this code and __slq__ in fields.ini is work!!
Is possible to using calculate field and import cvs?

[Paul]

Hi,

On a second look I think I can see your problem. It is this line

round(dateDiff("-", date("Y-m-d", time()), $record->strval('datanascita'))/365, 0);

There is no DateDiff function in PHP (mysql has the datediff function) you must make your own or steve might have a function similar that can be used.

Perhaps steve can advise or you will need to recode. There are plenty example on the www

Paul

[fantomasdm]

I have create my own function dateDiff for calculate age.
It's work fine.

[fantomasdm]

Hi this is function
function dateDiff($dformat, $endDate, $beginDate) {
$date_parts1=explode($dformat, $beginDate);
//echo $date_parts1[1].'-'.$date_parts1[2].'-'.$date_parts1[0];
$date_parts2=explode($dformat, $endDate);
$start_date=gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
$end_date=gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
return $end_date - $start_date;
}

it's working with italian date format (day,month,year). But I don't think that the problem is here

[Paul]

Hi,

Are the values of the CSV actually being inserted into your database?

Because the error is when the form is trying to be displayed, I think after the csv has been imported.

Debugging:

make sure everything is in the same case. .ini files are case sensitive. also check your conf.ini

what does the following display if inserted before your return statement

echo round(dateDiff("-", date("Y-m-d", time()), $record->strval('datanascita'))/365, 0);

Paul

[fantomasdm]

Hi I have comment function eta__display and the problem is the same.
I have put the debug code
$record = new Dataface_Record('anagrafica', array());
print_r($record->getValues());

and i see that the array have key with field 'eta'.
I think that the problem is with the contructor Dataface_Record that generate
associative array with fields reading from fields.ini :
__sql__ = "select *,nome as eta from anagrafica"

in this case field eta is not present in table, and I get error.

I hope that my English is no too bad that Compromise the Understanding!!

[Paul]

Perhaps you should upgrade to the latest version first and see if the problem persists. I know it fixed a few issues I had.

I did a quick mock up in one of my tables minus the date part and all works fine.

Company\fields.ini

Code: Select all

__sql__="select *, idCompany as eta from Company"

Company.php

Code: Select all

class tables_Company{

function eta__display(&$record){
if ($record->strval('idcompany')!="")
return "0";
else
return "1";
}
}
?>

The eta field is displayed correctly in the main view.

Again... Please refer to my previous post and check all case.