How to get the value in the array

General PHP Help
#1

I have a simple code

for($j=1;$j <= $xlsx->sheetsCount();$j++){
echo ‘

’;

print_r( $xlsx->rows($j) );

echo ‘
’;
}

The output looks like this

Array
(
[ 0 ] => Array
(
[ 0 ] => CountryCodeRate
)

[ 1 ] => Array
(
[ 0 ] => AFGHANISTAN93$0.44535
)

[ 2 ] => Array
(
[ 0 ] => AFGHANISTAN MOBILE937$0.45525
)

[ 3 ] => Array
(
[ 0 ] => AFGHANISTAN MOBILE9370$0.45525
)

I would like to parse the info, into a variable and display so the output will look like this

skip first element [ 0 ] => CountryCodeRate

Country=AFGHANISTAN
Code=93
Rate=$0.44535

Country=AFGHANISTAN MOBILE
Code=937
Rate= $0.45525

Country=AFGHANISTAN MOBILE
Code=9370
Rate= $0.45525

Thank for the help

#2

Which Excel lib are you using? I would guess you’d be best off accessing the columns of the row instead of reading the entire row and then parsing it yourself.

#3

I’m using class SimpleXLSX by Sergey Schuchkin

I just want to put the content of the array into variables so I can format and display. No need to store or save it to a database.

#4

Could you add a sample xlsx file, the output from the rows() method in that lib doesn’t match what you get. You could also be better off using another excel parser, this seems to have been abandoned 3 years ago (and lacks a lot of features compared to others) - might be some compatibility issues.

#5

I only need this simple script as a temporary solution. I like the class, it’s simple to use and my xlsx is very basic. Thanks for helping.

sample.zip (5.84 KB)

#6

Ok, opening that file in libreoffice shows the data being only in column 1, like your example output in the OP would suggest.

Before moving on to manually parsing this
How do you get your excel data? Is it possible for you to get this formatted properly (ie: having the headers/values spread out over columns 1-3 instead of jamming them all in column 1)

#7

That’s the purpose of the PHP script is to parse it because it’s all in one cell.

	//for($j=1;$j <= $xlsx->sheetsCount();$j++){
		//echo '<pre>';
		//print_r( $xlsx->rows(1) );
		//print_r( $xlsx->rowsEx($j) );
		//echo '</pre>';
		
		$counter = 0 ;
		foreach($xlsx->rows(1) as $data) {
			if($counter++ == 0) continue;
			$dataitem = $data[0];
			echo $dataitem."<br />";
		}

	//}

I figure how to get data from the array, next step is t0 parse $dataitem looking for the first occurance of a number which is the code until it hit $. The remaining would be the rate.

#8

[php]<?php

include ‘simplexlsx.class.php’;

$xlsx = new SimpleXLSX(‘sample.xlsx’);
$rows = $xlsx->rows();

// remove the first row
array_shift($rows);

$result = array();
foreach ($rows as $row) {
/*
* Using regex to split the string
* ([A-Z]+) - match uppercase letters
* (\d+) - match a number
* (\S+) - match whatever is left
*/
preg_match(’/([A-Z]+)(\d+)(\S+)/’, $row[0], $matches);
$result[] = array(
‘Country’ => $matches[1],
‘Code’ => $matches[2],
‘Rate’ => $matches[3]
);
}

echo ‘

’;

var_dump($result);[/php]
#9

Thanks. That was perfect!

#10

I found the following data
BOSNIA & HERZEGOVINA MOSTAR38751902$0.2256
US Virgin Islands1340$0.0399
AUSTRALIA VAS611471$3
GLOBAL SATELLITE NETWORK INT ORANGE MOBISTAR8831300$18

I modified the preg match but I can’t figure out if the rate has no cents (decimal point)
preg_match(’/([A-Za-z &]+)(\d+)($\d+.\d+)/’, $row[0], $matches);

#11

I modified the regex, it was correct in the comment

[php]preg_match(’/([A-Z]+)(\d+)(\S+)/’, $row[0], $matches);[/php]

#12

I saw your comment and I tried it but it didn’t work for me the first time. I cut and paste your code and it works.

can preg_match be in reverse order. How would you handle a string like this
TURKEY 444 SERVICES90444$0.063

This is the only one that has a number for country.

Thanks for all your help. I appreciate it.

#13

The following splits everything into individual groups for use:

[php]

<?php $str = 'TURKEY 444 SERVICES90444$0.063'; $pattern = '/([A-Z]+)\s(\d{3})\s+([A-Z]{0,8}+)(\d+)(\$\d+\.\d{0,3})/i'; if (preg_match($pattern, $str, $matches)) { echo "
";
    print_r($matches);
    echo "
"; } else { echo "

Not found.

"; } [/php]
Sponsor our Newsletter | Privacy Policy | Terms of Service