Solved

Can someone please assist me with exploding into an array?

Posted on 2011-09-23
10
433 Views
Last Modified: 2013-12-13
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

EE-sample-excel.xls



Current Results:

item_id | lineItemDescription | description | Brand | notes | price1 | net_weight | Available | photoID | product_group_desc | list | upc | class_description | map | inv_mast_uid | WHMAZ8902 | Mazda 1989-02 Wire Harness | Mazda 1989-02 Wire Harness | GENERIC BRAND | <1>Mazda 1989-2002 | 1.99 | 0.1 | 37 | 6729 | Installation Accessories/Supplies | 3.80 | | Consumer Electronics | | 10583 | 0990501000 | Fender 4 Oz Guitar Polish | Fender 4 Oz Guitar Polish | FENDER | | 3.99 | 0.15 | 71 | 6585 | Polish | 7.40 | | Guitars & Basses | | 403 |


Thanks for your help!!  
0
Comment
Question by:rlb1
  • 6
  • 2
  • 2
10 Comments
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 200 total points
ID: 36588994
Wouldn't it be just
$myarray = explode("|",$records);
?
0
 

Author Comment

by:rlb1
ID: 36589022
Roads Roads,

I have tried everything explode, implode, etc.

Here are the results of the explodet:

item_id | lineItemDescription | description | Brand | notes | price1 | net_weight | Available | photoID | product_group_desc | list | upc | class_description | map | inv_mast_uid | ArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArray


I need to break this thing up in groups of 15 and then insert it into the table probably with a foreach.
How do I structure correctly?

Thanks!




0
 

Author Comment

by:rlb1
ID: 36589032
To clarify, there are 15 fields per line...

item_id | lineItemDescription | description | Brand | notes | price1 | net_weight | Available | photoID | product_group_desc | list | upc | class_description | map | inv_mast_uid |
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 36589062
What shows up when you do:
$myarray = explode("|",$records[0]);
0
 
LVL 2

Accepted Solution

by:
jaxbrian earned 300 total points
ID: 36592527
$myarray = explode("|", $records);
$myarray[0];
$myarray[1];
$myarray[2];

and so on for as many variables as you have. each part of the array/explode will be assigned to $myarray[]; and then you can do whatever you want to with the parts.

you could then just insert each piece into mysql. VALUES ("$myarray[0];", "$myarray[1];", "$myarray[2];",  "and so on")
just an idea
0
 

Author Comment

by:rlb1
ID: 36593263
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


0
 
LVL 2

Expert Comment

by:jaxbrian
ID: 36593667
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

0
 

Author Comment

by:rlb1
ID: 36593711
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!!
0
 

Author Comment

by:rlb1
ID: 36593816
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

0
 

Author Closing Comment

by:rlb1
ID: 36593832
Thanks for your help!!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question