Solved

Can someone please assist me with exploding into an array?

Posted on 2011-09-23
10
429 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
Comment Utility
Wouldn't it be just
$myarray = explode("|",$records);
?
0
 

Author Comment

by:rlb1
Comment Utility
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
Comment Utility
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
 
LVL 27

Expert Comment

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

Accepted Solution

by:
jaxbrian earned 300 total points
Comment Utility
$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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rlb1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your help!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now