Page 1 of 3

Create a csv file

Posted: Wed Apr 13, 2011 11:25 am
by Flashart
Hi all

Can someone help point me in the right direction as to why this code isn't working?

Essentially I want to create a csv, and whilst this script does this, the csv is blank. I have got a simple example working like this one:

Code: Select all

<?php

$sales = array (array('Northeast', '2005-01-01', '2005-02-01', 12.54),
				array('Northwest', '2005-01-01', '2005-02-01', 546.33),
				array('Southeast', '2005-01-01', '2005-02-01', 93.26),
				array('Southwest', '2005-01-01', '2005-02-01', 945.21),
				array('All Regions', '--', '--',1597.34) );
				
	$fh = fopen('sales.csv', 'w') or die ("Can't open sales.csv");
	foreach ($sales as $sales_line) {
		if (fputcsv($fh, $sales_line) == false) {
		 die("Can't write CSV line");
		}
		
	}
	fclose($fh) or die ("Can't close sales.csv");
	echo "File 'sales_line.csv' written successfully";
	
	?>
Which works fine. Now I want to recreate that for my other script but it no worky...I have tried referencing all the different arrays and variables ($result, $ga) but to no avail. I'm sure i'm missing something simple. here is my code.

Code: Select all

define('ga_email','someone@gmail.com');
define('ga_password','apassword');
define('ga_profile_id','somenumbers');

require 'gapi.class.php';

$ga = new gapi(ga_email,ga_password);

$ga->requestReportData(ga_profile_id,array(
	'date',
	'dayOfWeek',
	'campaign',
	'adgroup'),
	
	array(
	'visits', 
	'visitBounceRate',
	'pageviewsPerVisit',
	'avgTimeOnSite',
	'goal1Completions',
	'transactionRevenue'));
?>
<table>
<tr>
	<th>Date</th>
	<th>Day</th>
	<th>Campaign</th>
	<th>Adgroup</th>
	<th>Visits</th>
	<th>Bounce Rate</th>
	<th>Pages Per Visit</th>
	<th>Avg Time On Site</th>
	<th>Goal Completions</th>
	<th>Revenue</th>
</tr>
<?php
foreach($ga->getResults() as $result):

?>
<tr>
  <td><?php echo $result ?></td>
 <td><?php echo date ('Y-n-j', strtotime($result->getDate()));?></td>
  <td><?php echo $result->getdayOfWeek()?></td>
 <td><?php echo $result->getCampaign()?></td>
  <td><?php echo $result->getAdgroup()?></td>
  <td><?php echo $result->getVisits()?></td>
 <td><?php echo $result->getVisitBounceRate()?></td>
  <td><?php echo $result->getpageviewsPerVisit()?></td>
  <td><?php echo $result->getAvgTimeOnSite()?></td>
  <td><?php echo $result->getgoal1Completions()?></td>
  <td><?php echo $result->gettransactionRevenue()?></td>
 
</tr>-->
<?php
endforeach
?>
</table>
<?php
$fh = fopen('analytics.csv', 'w') or die ("Can't open analytics.csv");
	foreach ($result as $yesterday) {
		if (fputcsv($fh, $yesterday) == false) {
		 die("Can't write CSV line");
		}
		
	}
	fclose($fh) or die ("Can't close analytics.csv");
	echo "File 'analytics.csv' written successfully";
	?>
I would appreciate some help in identifying why it won't work. Still a newb..!

Thanks
Peter

Re: Create a csv file

Posted: Wed Apr 13, 2011 5:21 pm
by incubi
fputcsv is looking for an array of values not a string for example.

Code: Select all


$result[] = "sdf";
$result[] = "werer";
$result[] = "sssss";	

$fh = fopen('analytics.csv', 'w') or die ("Can't open analytics.csv");
fputcsv($fh, $result);
fclose($fh);
exit(0);
In your code $yesterday is not an array its the value within one. :)

Hope that's the prob,

Lee

Re: Create a csv file

Posted: Thu Apr 14, 2011 5:07 am
by Flashart
Ok cheers for the pointer Lee

I have amended the code to:

Code: Select all

<?php
				
	$fh = fopen('analytics.csv', 'w') or die ("Can't open analytics.csv");
	foreach ($ga->getResults() as $yesterday) {
		if (fputcsv($fh, $ga->getResults()) == false) {
		 die("Can't write CSV line");
		}
		
	}
	fclose($fh) or die ("Can't close analytics.csv");
	echo "File 'analytics.csv' written successfully";
	?>
This now populates a csv file...but not how I want. I need to retrieve each of the columns as depicted in the table. I'm guessing I need a foreach llop for rows but not sure how to implement this.

Can anyone help? Much appreciated.

Peter

Re: Create a csv file

Posted: Thu Apr 14, 2011 3:11 pm
by incubi
Hi Peter

When you say "get the columns" do you mean get the column names?

If yes: Then you know them when you write the csv file but you can always open the csv again and pull the first row into an array.

if no: Then please explain.

Code: Select all

<?PHP
$file_handle = fopen("analytics.csv", "r");
while (!feof($file_handle) ) 
{
   $line_of_text = fgetcsv($file_handle, 1024);
   print $line_of_text[0] . $line_of_text[1]. $line_of_text[2] . "<BR>";
}
fclose($file_handle);
?>

Lee

Re: Create a csv file

Posted: Thu Apr 14, 2011 3:44 pm
by Flashart
Apologies Lee. My explanation was rather vague!

Essentially I wanted the csv to have the following format. For example, I wouldn't want the column headers (Date, Day,Campaign etc), I would want the data to be as such:

Cell A Cell B Cell C Cell D Cell E Cell F etc
2011-04-11 Monday London Shows Active Ghost-Show 100 etc

this would then loop through the entire array returning all the data for as many rows as there is held in the array. I have yet to implement your example, so that may be just the ticket!

Best
Peter

Re: Create a csv file

Posted: Thu Apr 14, 2011 4:02 pm
by Flashart
I have tried the code and unfortunately that doesn't work however that's no doubt to my poor explanation. I'm not sure why the example I posted (the sales one) works and yet the same code doesn't work in my example!

Obviously there are difference however in the simple example it's taking the array called $sales and writing each line correctly in the csv file, like this:

Northeast 01/01/2005 01/02/2005 12.54
Northwest 01/01/2005 01/02/2005 546.33
Southeast 01/01/2005 01/02/2005 93.26
Southwest 01/01/2005 01/02/2005 945.21
All Regions -- -- 1597.34

Taking the same logic, I need to return the result of the array which I think is

Code: Select all

$ga->requestReportData(ga_profile_id,array(
        'date',
        'dayOfWeek',
        'campaign',
        'adgroup'),
        
        array(
        'visits', 
        'visitBounceRate',
        'pageviewsPerVisit',
        'avgTimeOnSite',
        'goal1Completions',
        'transactionRevenue'));
?>
Or more specifically $ga. Is this not the array I want? I just can't figure out how I print out every row to a csv!

Re: Create a csv file

Posted: Thu Apr 14, 2011 4:13 pm
by Flashart
Further to this I have just done print_r($ga) and it prints out the entire array which shows me that's what I need to incorporate! Now the key is where I put it!

Re: Create a csv file

Posted: Thu Apr 14, 2011 4:42 pm
by incubi
Okay not sure but I think I'm caught up now. The last to statements got me a little mixed up.
"I just can't figure out how I print out every row to a csv!"
Then in the next post
"and it prints out the entire array which shows me that's what I need to incorporate! Now the key is where I put it!"

At this point you have the data in the array you want and you know how to output it now its a matter of writing the support code; is that where we are? :)

Lee

Re: Create a csv file

Posted: Fri Apr 15, 2011 3:10 am
by Flashart
Hi Lee

Not sure what you mean by "support code" but I'm guessing it's the code to write each line of the array to a line in the csv (?!) in which case yes that's where we are!

I need to figure out where to put the array $ga in the foreach loop within the csv generation script, so that when I open the csv file, i see each row of data.

I appreciate your patience and help!

Re: Create a csv file

Posted: Fri Apr 15, 2011 9:20 am
by incubi
Not a problem let me know how it turns out or if you have any other questions.

Lee

Re: Create a csv file

Posted: Fri Apr 15, 2011 3:06 pm
by Flashart
Still no further...i tried a

Code: Select all

$fh = fopen('analytics.csv', 'w') or die ("Can't open analytics.csv");
	foreach ($ga as $key => $yesterday) {
		if (fputcsv($fh, $yesterday) ==false) {
		 die("Can't write CSV line");
		}
		
	}
	fclose($fh) or die ("Can't close analytics.csv");
	echo "File 'analytics.csv' written successfully";
but i get a error message. I am quite stuck now on this! Any help would be appreciated.

Re: Create a csv file

Posted: Fri Apr 15, 2011 4:21 pm
by incubi
Hi,

I need t know what $ga is and what's in it?

Lee

Re: Create a csv file

Posted: Sat Apr 16, 2011 4:40 am
by Flashart
Hi Lee

Thanks for your continued support. $ga I think is the array. This script is actually pre-written by a project called gapi which communicates with the Google Analytics api. From my limited php understanding, looking at the original code, $ga seems to be the array which all the returned by data is then contained in. When I do print_r($ga) it spits out all the data, all the bounce rates, all the visits etc, so it definitely contains what I need.

The table code prints out like this:

Date Day Campaign Adgroup Visits Bounce Rate Pages Per Visit Avg Time On Site Goal Completions Revenue
2011-4-15 5 Campaign 1 London Shows 0 0 0 0 0 0
2011-4-15 5 Campaign 2 Scotland shows 1 0 1 47 0 0
2011-4-15 5 Campaign 3 Kent shows 1 0 1 150 0 0


etc etc. This is what I want to replicate within my csv file, with the slight modification of I don't want the column headers. I probably should have written this at the start!

Re: Create a csv file

Posted: Sat Apr 16, 2011 12:51 pm
by incubi
Hi,

If $ga is a 1D array like this

Code: Select all

$ga = array("1","2","3","3","3","3","3","3","3");
then I see why you get the error. So we need to understand that better. Using your example and adding commas it could be a D1 Array or a 2D. Just depends out the script outputs the data.

2011-4-15, 5, Campaign, 1, London Shows, 0, 0, 0, 0, 0, 0
2011-4-15, 5, Campaign 2, Scotland shows, 1, 0, 1, 47, 0, 0
2011-4-15 5, Campaign, 3, Kent, shows, 1, 0, 1, 150, 0, 0

My bet is that $ga is not structured like you think it is.
Point me to where you got the code and I'll download it and look it over. If that's not something you can do due to restrictions then use the print_r and send me the output.

Lee

Re: Create a csv file

Posted: Sun Apr 17, 2011 1:34 pm
by incubi
Hi, I got the email with the sample I'll need one other thing.
When you try to run your foreach code and it errors what is the error?
If you get the error back in the browser I'll need that and the one that's in the PHP log.

Lee