import data from excel file

[md9]

Dear Users,
Has anyone successfully written import filter for MS excel spreadsheet. I followed the excellent howto on Xataface site:
http://www.xataface.com/documentation/h ... rt_filters
but couldn't get way ahead with it. Importing CSV was working, but for no reason it was adding another empty row at the end of the data. I also gave a try to pear package suggested on
http://xataface.com/forum/viewtopic.php?t=4111
but couldn't write the import filter. Any help is appreciated.

Thank you.
mahendra

[shannah]

I have used the PEAR excel parser successfully many times...

I don't really have a simple example because most of the filters were quite complex, but here are some snippets:

Code: Select all

function import(&$data, $defaults=array()){
    $this->data =& $data;
    $this->defaults =& $defaults;
    $app =& Dataface_Application::getInstance();
   
     

    import('include/Spreadsheet/Excel/reader.php');
    $this->reader = new Spreadsheet_Excel_reader();
    $this->reader->setOutputEncoding('CP1251');
   
    // first we read the spreadsheet into a file since that is what
    // the reader wants
    $tempdir = DATAFACE_SITE_PATH.'/templates_c';
    $tmpnam = tempnam($tempdir, 'dataface-companies-excell-import');
    $handle = fopen($tmpnam,'w');
    fwrite($handle,$data);
    fclose($handle);
   
    // feed the spreadsheet into the reader
    $this->reader->read($tmpnam);
   
   
    // This spreadsheet contains multiple worksheets... loop through
    // them here.
    for ($i=0; $i reader->sheets); $i++){
      $ws =& $this->reader->sheets[$i]
      if ( !isset($end_row) ) $end_row = $ws['numRows'];
      for ($i=$start_row; $isetValues($this->defaults);
     $record->setValues(
      array(
      'company_name'=>$data[2],
      'mailing_state'=>$data[1],
      'mailing_country'=>'USA',
      'main_phone'=>$data[3],
      'main_website'=>@$data[4],
      'contact_name'=>@$data[5],
      'resource_type'=>$category_id
      )
      );
     $this->records[] =& $record;
     $this->successes++;
          unset($data);
      }
      unset($ws);
         
    }
}


This should give you an idea. (Note that this snippet has been hacked to pieces to make it less obfuscated, so you can't copy/paste and expect it to work. However the basics of how to use the pear spreadshet reader are there.

[md9]

Following is the code which I modified a bit for importing CSV files. The problem was, MSEXCEL used to save CSV with one additional empty row at end. This resulted in unnecessary import of empty row along with values. Now the code checks for the empty spaces by searching if the value for e.g. name is empty in array. I hope this might help someone.

Code: Select all

class tables_name {
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 ){
   // We iterate through the rows and parse the fields
  //  so that they can be stored in a Dataface_Record object.
        list($name,$sex,$address,$city,$state) = explode(',', $row);
        $record = new Dataface_Record('system', array());
     
        // We insert the default values for the record.
        $record->setValues($defaultValues);
          // Now we add the values from the CSV file.
        $record->setValues(
            array(
                'name'=>$name,
                'sex'=>$sex,
                'address'=>$address,
                'city'=>$city,
                'state'=>$state
                 )
            );
  // Let us check first if the row has some value. If it is empty don't insert.
     if (($row[$name]) == '') {
    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);
}