Solved

Question on generating csv file

Posted on 2014-10-21
12
151 Views
Last Modified: 2014-10-27
Here's my code:

<?php 
ini_set("memory_limit","500M");
error_reporting(E_ALL);

set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database)
or die ("couldn't connect to server");

$output="";

$michelle="select * from twitter_csv";
$michelle_query=mysqli_query($cxn, $michelle)
or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

$filename="twitter_data.csv";

// OPEN THE CSV FILE HERE
$file = fopen($filename, 'w');

// GET THE FIELD NAMES
$heading=mysqli_fetch_fields($michelle_query);
fputcsv($file, $heading);

// GET THE DATA SET
while($michelle_row=mysqli_fetch_assoc($michelle_query))
{
    fputcsv($file, $michelle_row);
}

// REWIND THE CSV FILE HERE
rewind($file);

// SEND HEADERS AND DATA
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);

fpassthru($file);
?>

Open in new window


This is the error I get:

Warning: fopen(twitter_data.csv): failed to open stream: Permission denied in /opt/lampp/htdocs/twit/good_twitter_csv.php on line 20

Warning: fputcsv() expects parameter 1 to be resource, boolean given in /opt/lampp/htdocs/twit/good_twitter_csv.php on line 24

What do these errors mean and how do I fix it?
0
Comment
Question by:brucegust
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 58

Assisted Solution

by:Gary
Gary earned 150 total points
ID: 40394648
The second error is as a result of the first error.
Check the permissions on the folder, is it writable?
0
 

Author Comment

by:brucegust
ID: 40394704
No, it's not.

Let me ask you this: The reason I'm going this route is because this code, while it works, was not accommodating commas. Anytime one showed up, it resulted in an additional column being generated and that's not going to work.

Thing is, Gary, I think this is your code! How can I tweak it so it "escapes" those commas?

Here's what I've got:

<?php 
ini_set("memory_limit","500M");
error_reporting(E_ALL);

set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database)
or die ("couldn't connect to server");

$output="";

$michelle="select * from twitter_csv";
$michelle_query=mysqli_query($cxn, $michelle)
or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

//gets the field names from your table and sets them up as the first row in your csv file

$heading=mysqli_fetch_fields($michelle_query);

foreach ($heading as $val)
{
	$output .='"'.$val->name .'",';
}
$output .="\n";


while($michelle_row=mysqli_fetch_array($michelle_query))
{
	for($i=0; $i<$michelle_columns; $i++)
	{
		$output .=''.$michelle_row["$i"].',';
	}
$output .="\n";
}

$filename="twitter.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
?>

Open in new window


It's what I've been using and it works great, except for that comma dynamic.

To answer your question, the folder is not writeable. I may be able to change that, but I would prefer learning how to make the above code work rather than having to start over with a different approach.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40394762
If the folder isn't writable then you cannot change any file in it.

Does it need to be a csv, can you use a tab delimited file?

Your problem with the commas is because you didn't want the quote marks around the columns,
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 100 total points
ID: 40394766
The quoting is a little inconsistent in your code sample.  If this is still creating issues, show an example of what the code generates vs. what you want the result to look like.
<?php 
ini_set("memory_limit","500M");
error_reporting(E_ALL);

set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database) or die ("couldn't connect to server");

$output="";

$michelle="select * from twitter_csv";
$michelle_query=mysqli_query($cxn, $michelle) or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

//gets the field names from your table and sets them up as the first row in your csv file
$heading=mysqli_fetch_fields($michelle_query);
$output = '"'.implode('","',$heading).'"\n';


while($michelle_row=mysqli_fetch_array($michelle_query))
{
  $output .= implode(',',$michelle_row)."\n";
}

$filename="twitter.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
?>

Open in new window

0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 50 total points
ID: 40394822
The trailing comma is because the comma is being appended without a check to see if there is a next column. You could try this - note the change in how the comma's are added. We add a comma before we add the column data but only if the buffer is not empty.
<?php 
ini_set("memory_limit","500M");
error_reporting(E_ALL);

set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database)
or die ("couldn't connect to server");

$output="";

$michelle="select * from twitter_csv";
$michelle_query=mysqli_query($cxn, $michelle)
or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

//gets the field names from your table and sets them up as the first row in your csv file

$heading=mysqli_fetch_fields($michelle_query);

foreach ($heading as $val)
{
	// ADD A COMMA IF THE LINE IS NOT EMPTY
	// IF WE GET HERE THERE IS ANOTHER COLUMN
	// SO PREPEND IT WITH A COMMA
	if ($output !='') $output .= ',';
	
	$output .='"'.$val->name .'",';
}
$output .= trim($output, ",",) . "\n";


while($michelle_row=mysqli_fetch_array($michelle_query))
{
	$line = '';
	for($i=0; $i<$michelle_columns; $i++)
	{
		if ($line !='') $line .= ',';
		$line .=''.$michelle_row["$i"];
	}
$output .= $line . "\n";
}

$filename="twitter.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
?>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40394856
I thought we went over this in an earlier question?
http://iconoun.com/demo/temp_brucegust.php

The part you want is located at the end, around line 170.

The generated file is here:
http://iconoun.com/demo/storage/brucegust.csv

<?php // demo_temp_brucegust.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28533249.html

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// THE DATA SET IS UTF-8
$mysqli->set_charset("utf8mb4");
mb_internal_encoding("UTF-8");

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE `verizon`
( `id`                 INT           NOT NULL AUTO_INCREMENT PRIMARY KEY
, `twitter_id`         VARCHAR(150)  NOT NULL DEFAULT ''
, `actor_id`           VARCHAR(150)  NOT NULL DEFAULT ''
, `actor_display_name` VARCHAR(150)  NOT NULL DEFAULT ''
, `posted_time`        DATETIME      NOT NULL DEFAULT '0000-00-00 00:00:00'
, `display_name`       VARCHAR(150)  NOT NULL DEFAULT ''
, `geo_coords_lat`     DECIMAL(16,9) NOT NULL DEFAULT 0.0
, `geo_coords_lon`     DECIMAL(16,9) NOT NULL DEFAULT 0.0
, `location_name`      VARCHAR(150)  NOT NULL DEFAULT ''
, `posted_day`         DATE          NOT NULL DEFAULT '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// MAP OF COLUMN NAMES  // AND SAMPLE VALUES
$map = array
( 'id'                  //  "2"                      <-- AUTO_INCREMENT KEY
, 'twitter_id'          //  "364536294640451585"
, 'actor_id'            //  "322321678"
, 'actor_display_name'  //  "Mutt"
, 'posted_time'         //  "2013-08-06 00:00:00"
, 'display_name'        //  "Twitter for Android"
, 'geo_coords_lat'      //  "32.867479200"
, 'geo_coords_lon'      //  "-96.641121600"
, 'location_name'       //  "Garland"
, 'posted_day'          //  "2013-08-06"
)
;

// READ THE TEST DATA
$url = 'http://filedb.experts-exchange.com/incoming/2014/10_w41/876263/verizon-data.csv';
$utl = 'storage/brucegust_testdata.csv';
$fpr = fopen($url, 'rb');
if (!$fpr) trigger_error("Unable to open $url", E_USER_ERROR);

// LOAD THE DB TABLE
while ($csv = fgetcsv($fpr))
{
    $csv = array_combine($map, $csv);
    unset($csv['id']);
    foreach ($csv as $key => $val)
    {
        $csv[$key] = $mysqli->real_escape_string($val);
    }
    $cols = implode(',', array_keys($csv));
    $data = "'" . implode("','", $csv) . "'";
    $sql = "INSERT INTO verizon ($cols) VALUES ($data)";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }
}


// ASSIGN VARIABLES FOR OUR QUERY
$latitude_1  = 32;
$latitude_2  = 36;
$longitude_1 = -94;
$longitude_2 = -86;
$start_date  = '2013-07-04';
$end_date    = '2013-08-06';

// CREATE A SELECT QUERY
$sql
=
"
SELECT
  actor_id
, actor_display_name
, posted_time
, display_name
, geo_coords_lat
, geo_coords_lon
, location_name
, posted_day
FROM verizon
WHERE
( geo_coords_lat >= '$latitude_1' AND geo_coords_lat <= '$latitude_2')
AND
( geo_coords_lon >= '$longitude_1' AND geo_coords_lon <= '$longitude_2')
AND
( posted_day >= '$start_date' AND posted_day <= '$end_date')
LIMIT 400000
"
;

// RUN THE QUERY OR FAIL ON ERROR
$res = $mysqli->query($sql);
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// HOW MANY ROWS WERE RETURNED?
$cnt = $res->num_rows;
if($cnt == 0)
{
    echo PHP_EOL . "NO ROWS MATCHED QUERY: $sql";
}
else
{
    // WRITE A CSV FILE HERE
    echo PHP_EOL . "$cnt ROWS MATCHED QUERY: $sql<br>";
    $out = 'storage/brucegust.csv';
    $fpw = fopen($out, 'wb');
    if (!$fpw) trigger_error("Unable to Open $out", E_USER_ERROR);

    // GET COLUMN NAMES
    $fld = $res->fetch_fields();
    $csv = array();
    foreach ($fld as $obj)
    {
        $csv[] = $obj->name;
    }
    fputcsv($fpw, $csv);

    // GET DATA VALUES
    while ($row = $res->fetch_assoc())
    {
        fputcsv($fpw, $row);
    }
    fclose($fpw);

    // PRESENT A LINK FOR THE CLIENT
    echo PHP_EOL . 'CSV: <a target="_blank" href="' . $out . '">' . $out . '</a>';
}

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:brucegust
ID: 40395460
Gary - I need a csv file.

Steve - I got this when I ran your code:

Catchable fatal error: Object of class stdClass could not be converted to string in /opt/lampp/htdocs/twit/good_twitter_csv_test.php on line 17

JulianH - I still had the same problem, although I was totally digging your logic.

Ray - Yes, you've seen this before, but I went in a different direction when it became evident that I was going to need write permission to the directory. That's why, although your input is all over this project, I didn't use that particular piece of "Ray Cake..."

But guys, we're closer...

Here's what I wound up doing and it worked:

while($michelle_row=mysqli_fetch_array($michelle_query))
{
      for($i=0; $i<$michelle_columns; $i++)
      {
            $output .=''.str_replace(",", "&#44;", $michelle_row["$i"]).',';
      }
$output .="\n";
}

I just did a "replace" so in the event one of the users documented their name with a comma, I wasn't going to have to worry about a new column.

But we're not out of the woods, yet...

Attached is a screenshot of the new and improved csv file. While the previous quandary was a result of a renegade comma, I don't know what is causing this hiccup here.

You can see the dilemma with "Will Brooks." For no apparent reason, his row is a complete mess. Any ideas as to why his particular row chooses to go south?

What do you think?
what-the-smoke.docx
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40395591
Looks like there is a line-feed character in Will Brooks data.  This is normal and expected in CSV files.

I really think you might want to get write permissions so you can do this the right way, with fputcsv().  If you use the standard methods, you can get correct results very quickly and easily.  But trying to create your own CSV files would seem to require writing a state engine that replicates the functionality of fputcsv().  That's an advanced programming task and it's completely unnecessary -- all you need to do is get the directory write permissions for a temporary directory!
0
 

Author Comment

by:brucegust
ID: 40395695
So, Ray, let me explain this back to you: The nature of the CSV "beast" is that you're bound to encounter characters within your data that will be processed as "code." When you say "line feed," in this case that could be a carriage return or a line break which is enough to wreak havoc on a CSV export process.

That said, I'm doing well to get the necessary permissions and use the fputcsv() dynamic if for no other reason in that it anticipates those kinds of things and prevents them from clogging the pipes.

Yes?
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 200 total points
ID: 40395733
I'll try not to make this response too long, but it contains a lot of "deep background" information that may not be obvious unless you've grown accustomed to handling many different character sets and binary data.

I don't know all of the details of your data set, except to say that it looked like it contained some binary data in some of the fields, perhaps UTF-8 or other character encoding.  If you open a file for 'wb' you can record this data correctly.  Most CSV files contain only ASCII characters, but that is increasingly changing as the world moves away from ISO-8859-1 character sets toward UTF-8 character sets. (Learn more here).

Whatever you call them (line-feeds, carriage returns, end-of-line characters), they are an OS-dependent collection of x0A and x0D characters that create new lines in certain output documents.  In HTML, these do not create new lines when rendered in the browser viewport, but do create new lines when rendered in "view source."  They are not processed as "code" in the same ways that JavaScript might get processed; they simply create new lines, in much the same way that the letter "A" creates the letter "A" - it is what it is.

New lines do not clog the pipes; they simply put the following data on the next line.  New lines are considered whitespace in much the same way that blanks or tabs are considered whitespace.  They are all perfectly acceptable inside CSV data elements, and fputcsv() will quote them correctly.  These characters will only "wreak havoc" if they are not quoted correctly.  So yes, using fputcsv() is a good step in the right direction.  

Is it likely to be enough?  Maybe, depending on your data set and the way you write your files.  Is there a better way, perhaps by trying to outsmart fputcsv() and write your own state engine to provide quotes?  I doubt it.

So, "Yes, get the write permissions!"
0
 

Author Comment

by:brucegust
ID: 40395777
Got it!

Thank you!
0
 
LVL 58

Expert Comment

by:Gary
ID: 40395896
What are you importing this into, you can normally set whatever delimiter you want in most programs
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

707 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

13 Experts available now in Live!

Get 1:1 Help Now