asked on

Can someone please assist me with exploding into an array?

Experts:
Can someone please assist me with exploding this into an array so that I can populate my database table? I have attached a excel file showing format.

<?php
//error_reporting(E_ALL ^ E_NOTICE);

ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);

require_once 'excel_reader2.php';
$data = new Spreadsheet_Excel_Reader("EE-sample-excel.xls", false);



	$rows = $data->rowcount();
	$cols = $data->colcount();
	
	$labels = array();
	$records = array();
	$records_rows = array();
	for($row=1;$row<=$rows;$row++)
	{
		
		for($col=1;$col<=$cols;$col++)
		{
			$this_val = $data->val($row,$col);
			if($col==1 && empty($this_val)) break 2; //if col A is empty, break out 2 levels of for loops, the valid content rows have ended.
			
			
			
			if($row==1){//header row that contains the column labels
				//echo ''.$row.','.$col.' - '.$this_val. ' | ';    (Original)
				echo $this_val. ' | ';
				if(in_array($this_val, $labels)){
					$labels[$col] = $this_val.'.1';
				} else {
					$labels[$col] = $this_val;
				}
				
				
			} else {
			
				if($col==1){//this is col 1/A, this has the unique ID for the rows/records
					//$records[] = $this_val;
					$records_rows[$row] = $this_val;
				}else{
					$col_name = $labels[$col];
					//$records[ $records_rows[$row] ][] = array( $col_name =>$this_val, );
					$records[$records_rows[$row]][$col_name] = $this_val;
				}
				
				//echo ''.$row.','.$col.' - '.$this_val. ' | ';  (Original)
				
				//echo $this_val. ' | ';
				$exp = $this_val. ' | ';
				print_r($exp);
				
			}
			
		}
		
		//echo '<br /><hr /><br />';
	}
	
/*
	echo '<pre>';
	print_r($labels);
	echo '</pre>';
*/
/*
	echo '<pre>';
	print_r($records_rows);
	echo '</pre>';
*/

/*	
	echo '<pre>';
	print_r($records);
	echo '</pre>';
*/

Open in new window

SOLUTION

Lukasz Chmielewski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

ASKER

What shows up when you do:
$myarray = explode("|",$records[0]);

ASKER CERTIFIED SOLUTION

jaxbrian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rlb1

ASKER

Roads_Roads:
Thanks for your help! These are the errors I received....

Notice: Undefined offset: 0 in /parse-trinity.php on line 52
Array
Notice: Undefined offset: 0 in /parse-trinity.php on line 52
Array
Notice: Undefined offset: 0 in /parse-trinity.php on line 52
Array

jaxbrian:
Thanks for your help!! Here are the errors returned on your modification.

Notice: Undefined offset: 0 in /parse-trinity.php on line 52

Notice: Array to string conversion in /parse-trinity.php on line 54

Notice: Undefined offset: 1 in /parse-trinity.php on line 56

Notice: Undefined offset: 2 in /parse-trinity.php on line 57
Array

49                //echo ''.$row.','.$col.' - '.$this_val. ' | ';
				
51                //$exp = $this_val. ' | ';
52                $myarray = explode("|",$records[0]); 
				
54                $myarray = explode("|", $records);
55                $myarray[0];
56                $myarray[1];
57                $myarray[2];


58              echo $myarray[0]."<BR>";
59              echo $myarray[1]."<BR>";
60              echo $myarray[2]."<BR>";
61              echo "<BR><BR>";
                
65			}

Open in new window

jaxbrian

when you ran mine did you have line 52 in there? try it again without it and post any notices or errors you get.
I just ran the below on my server and it worked.

$records = "1|2|3|4|5|6|7";
				
 $myarray = explode("|", $records);
 $myarray[0];
$myarray[1];
 $myarray[2];


 echo $myarray[0]."<BR>";
echo $myarray[1]."<BR>";
echo $myarray[2]."<BR>";
echo "<BR><BR>";

Open in new window

rlb1

ASKER

jaxbrian,

Here is the response:

1
2
3

Fatal error: Cannot use string offset as an array in {URL removed by _alias99} on line 45

here is line 45:

$records[ $records_rows[$row] ][] = array( $col_name =>$this_val);

Open in new window

The code below works perfectly if the "Notes" column is removed. I have attempted to bypass the notes column bypassing data[4] when inserting into my DB. That will not work. Is there a way to read the Excel data, then write everything except for the "Notes" column to csv?

<?php


mysql_connect("localhost","username","password") or die("Unable to connect to SQL server");
     mysql_select_db('db') or die("Unable to SELECT DB");

     echo "Connected to DB";



require_once 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
$excel->setOutputEncoding('CP1251');
$excel->read('trinity.xls');
$x=8;
$sep = ",";
ob_start();
while($x<=$excel->sheets[0]['numRows']) {
$y=1;
$row="";
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
$row.=($row=="")?"\"".$cell."\"":"".$sep."\"".$cell."\"";
$y++;
}
echo $row."\n"; 
$x++;
}
$fp = fopen("trinity.csv",'w');
fwrite($fp,ob_get_contents());
fclose($fp);
ob_end_clean();







//  UPLOAD PRODUCT INFO  *******************************************************************

mysql_query("TRUNCATE TABLE `1trinity_excel_download`");

// CSV into MySQL import script by Mark Randall (mark@hostcobalt.com)
//
// Include the mysql connect page
//include("connect.php");
// Set the filename that you want to import
$filename="trinity.csv";
//open the file
$handle = fopen($filename, "r");
//this is what causes it to skip
fgetcsv($handle, ',');


//begin looping through the lines
while (($data = fgetcsv($handle, ',')) !== FALSE)
{

// loop all column values and escape special characters
foreach ($data as $key => $value){
  $data[$key] = htmlspecialchars($value, ENT_QUOTES, 'utf-8');
}

$import="INSERT into `1trinity_excel_download` (item_id) values ('$data[0]')";
 


//execute the mysql query
mysql_query($import) or die(mysql_error());
}
//close the file
fclose($handle);
//output a message saying its done.

$msgprod="Product Import done";
echo $msgprod;
echo "<BR>";
&#9;
&#9;&#9;&#9;

?>

Open in new window

I really appreciate your help!!

rlb1

ASKER

Thanks for your help!! I got it to work!!

while($x<=$excel->sheets[0]['numRows']) {
$y=(1|2|3|5|6|7);  //   <---  HERE IS THE CHANGE
$row="";

Open in new window

rlb1

ASKER

Thanks for your help!!