Excel to MySql Database!

Topics: User Forum
May 17, 2010 at 11:41 AM

I have gone through the last 10 pages of the discussion, and through the documentation - but have not found any solid answers.

I have a number of XLS,XLXS files we get on a weekly basis - basically with columns of product name, price, description, brand etc

I need to only extract the model and price rows, and import them into my SQL database. They are a number of Sheets per file, all with different amount of rows.

Is it not possible to extract informaiton and store them in variables? EG, Take all rows from column A and column F in a loop and use and mysql insert to enter the rows into a table in the database?

So basically, what I am battling with is to get the informaiton into variables that I can work with - it seems my only options are to save as HTML/PDF/EXEL etc.....

Is this possible?

 

May 19, 2010 at 4:55 PM

Depending on the size of the files, yes, you can do this.

After creating the PHPExcel object and loading the file into it, you can iterate over it kind of like this:

$objWorksheet = $objPHPExcel->getActiveSheet();

foreach ($objWorksheet->getRowIterator() as $row) {
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false);
  $values = array();
  foreach ($cellIterator as $cell) 
  {
    array_push($values, $cell->getValue());
  }
  // build the insert statement from the values in the cells.
  // remember to skip the header line(s) if your files have them.
  $sql = "INSERT INTO TABLE VALUES (".implode(',', $values).")";
  // insert into the DB here, I am just echoing it out to screen
  echo $sql;
}

 

May 19, 2010 at 5:19 PM

Here is an updated version of the foreach that will handle your data correctly:

  foreach ($cellIterator as $cell) 
  {
    $cellval = $cell->getValue();
    if (is_numeric($cellval)) {
      array_push($values, $cellval);
    } else {
      array_push($values, '"'.$cellval.'"');
    }
  }

This way, it encloses the strings with double quotes.

 

May 25, 2010 at 8:33 PM

I have a question about inputting the data into the db in the right format. Specifically I'm trying to input date columns from an .xls into a MySQL DB. How can I tell if a column is formatted as a Date?