?
Solved

Can someone please assist me with exploding into an array?

Posted on 2011-09-23
10
Medium Priority
?
438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
10 Comments
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 800 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
WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

 
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 1200 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

777 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