Jump to content

Eporting to csv file (newb here)

alacn

By alacn
in PHP Coding Help

 Share

Recommended Posts

alacn Newbie

alacn

Posted
Posted

Hi All,

 

Im modifying someone elses PHP code (survey tool) (and i havent done PHP for years) Basically the code exports to an external csv file to a file called export.csv

However everytime i get it to export, it creates copies. ie it will export to export(2).csv and export(3).csv.

 

How can i make it so that it exports and overwritesa/ammed the existing export.csv file?

 

I had a look at the code and i believe this code below is doing the exporting but not sure

 

code doing the export ( i think)

 

<?php

include('classes/main.class.php');
include('classes/special_results.class.php');

$survey = new UCCASS_Special_Results;

echo $survey->results_csv(@$_REQUEST['sid'],$_REQUEST['export_type']);

?>

 

The class that it uses to export

 

<?php

//======================================================
// Copyright (C) 2004 John W. Holmes, All Rights Reserved
//
// This file is part of the Unit Command Climate
// Assessment and Survey System (UCCASS)
//
// UCCASS is free software; you can redistribute it and/or
// modify it under the terms of the Affero General Public License as
// published by Affero, Inc.; either version 1 of the License, or
// (at your option) any later version.
//
// http://www.affero.org/oagpl.html
//
// UCCASS is distributed in the hope that it will be
// useful, but WITHOUT ANY WARRANTY; without even the implied warranty
// of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// Affero General Public License for more details.
//======================================================

class UCCASS_Special_Results extends UCCASS_Main
{
    function UCCASS_Special_Results()
    {
        $this->load_configuration();

        //Increase time limit of script to 2 minutes to ensure
        //very large results can be shown or exported
        set_time_limit(120);
    }

    function results_table($sid)
    {
        $sid = (int)$sid;

        if(!$this->_CheckAccess($sid,RESULTS_PRIV,"results_table.php?sid=$sid"))
        {
            switch($this->_getAccessControl($sid))
            {
                case AC_INVITATION:
                    return $this->showInvite('results_table.php',array('sid'=>$sid));
                break;
                case AC_USERNAMEPASSWORD:
                default:
                    return $this->showLogin('results_table.php',array('sid'=>$sid));
                break;
            }
        }

        $data = array();
        $qid = array();
        $survey = array();

        $survey['sid'] = $sid;

        $query = "SELECT q.qid, q.question, s.name, s.user_text_mode, s.survey_text_mode, s.date_format
                  FROM {$this->CONF['db_tbl_prefix']}questions q, {$this->CONF['db_tbl_prefix']}surveys s
                  WHERE q.sid = $sid and s.sid = q.sid ORDER BY q.page, q.oid";
        $rs = $this->db->Execute($query);
        if($rs === FALSE)
        { $this->error('Error in query: ' . $this->db->ErrorMsg()); return; }

        $questions = array();
        if($r = $rs->FetchRow($rs))
        {
            $survey_text_mode = $r['survey_text_mode'];
            $user_text_mode = $r['user_text_mode'];
            $date_format = $r['date_format'];
            $survey['name'] = $this->SfStr->getSafeString($r['name'],$survey_text_mode);

            do{
                $data['questions'][] = $this->SfStr->getSafeString($r['question'],$survey_text_mode);
                $qid[$r['qid']] = $r['qid'];
            }while($r = $rs->FetchRow($rs));
        }
        else
        { $this->error('No questions for this survey.'); return; }

        if(isset($_SESSION['filter_text'][$sid]) && isset($_SESSION['filter'][$sid]) && strlen($_SESSION['filter_text'][$sid])>0)
        { $this->smarty->assign_by_ref('filter_text',$_SESSION['filter_text'][$sid]); }
        else
        { $_SESSION['filter'][$sid] = ''; }

        $query = "SELECT GREATEST(rt.qid, r.qid) AS qid, GREATEST(rt.sequence, r.sequence) AS seq,
                  GREATEST(rt.entered,r.entered) AS entered,
                  q.question, av.value, rt.answer FROM {$this->CONF['db_tbl_prefix']}questions q LEFT JOIN {$this->CONF['db_tbl_prefix']}results
                  r ON q.qid = r.qid LEFT JOIN {$this->CONF['db_tbl_prefix']}results_text rt ON q.qid = rt.qid LEFT JOIN
                  {$this->CONF['db_tbl_prefix']}answer_values av ON r.avid = av.avid WHERE q.sid = $sid {$_SESSION['filter'][$sid]}
                  ORDER BY seq, q.page, q.oid";

        $rs = $this->db->Execute($query);
        if($rs === FALSE)
        { $this->error('Error in query: ' . $this->db->ErrorMsg()); return; }

        $seq = '';
        $x = -1;
        while($r = $rs->FetchRow($rs))
        {
            if(!empty($r['qid']))
            {
                if($seq != $r['seq'])
                {
                    $x++;
                    $seq = $r['seq'];
                    $answers[$x]['date'] = date($date_format,$r['entered']);
                }
                if(isset($answers[$x][$r['qid']]))
                { $answers[$x][$r['qid']] .= MULTI_ANSWER_SEPERATOR . $this->SfStr->getSafeString($r['value'] . $r['answer'],$user_text_mode); }
                else
                { $answers[$x][$r['qid']] = $this->SfStr->getSafeString($r['value'] . $r['answer'],$user_text_mode); }
            }
            $last_date = date($date_format,$r['entered']);
        }
        $answers[$x]['date'] = $last_date;

        $xvals = array_keys($answers);

        foreach($xvals as $x)
        {
            foreach($qid as $qid_value)
            {
                if(isset($answers[$x][$qid_value]))
                { $data['answers'][$x][] = $answers[$x][$qid_value]; }
                else
                { $data['answers'][$x][] = ' '; }
            }
            $data['answers'][$x][] = $answers[$x]['date'];
        }

        $this->smarty->assign_by_ref('data',$data);
        $this->smarty->assign_by_ref('survey',$survey);
        return $this->smarty->fetch($this->template.'/results_table.tpl');
    }

    function results_csv($sid, $export_type=EXPORT_CSV_TEXT)
    {
        $sid = (int)$sid;


        $retval = '';

        if(!$this->_CheckAccess($sid,RESULTS_PRIV,"results_csv.php?sid=$sid"))
        {
            switch($this->_getAccessControl($sid))
            {
                case AC_INVITATION:
                    return $this->showInvite('results_csv.php',array('sid'=>$sid));
                break;
                case AC_USERNAMEPASSWORD:
                default:
                    return $this->showLogin('results_csv.php',array('sid'=>$sid));
                break;
            }
        }

        header("Content-Type: text/plain; charset={$this->CONF['charset']}");
        header("Content-Disposition: attachment; filename=Export.csv");

        $query = "SELECT q.qid, q.question, s.date_format
                  FROM {$this->CONF['db_tbl_prefix']}questions q, {$this->CONF['db_tbl_prefix']}surveys s
                  WHERE q.sid = $sid and s.sid = q.sid ORDER BY q.page, q.oid";
        $rs = $this->db->Execute($query);
        if($rs === FALSE)
        { $this->error('Error in query: ' . $this->db->ErrorMsg()); return; }

        $questions = array();
        if($r = $rs->FetchRow($rs))
        {
            $date_format = $r['date_format'];
            do{
                $questions[$r['qid']] = $r['question'];
            }while($r = $rs->FetchRow($rs));
        }
        else
        { $this->error('No questions for this survey'); return; }

        if(isset($_SESSION['filter_text'][$sid]) && isset($_SESSION['filter'][$sid]) && strlen($_SESSION['filter_text'][$sid])>0)
        { $this->smarty->assign_by_ref('filter_text',$_SESSION['filter_text'][$sid]); }
        else
        { $_SESSION['filter'][$sid] = ''; }


        $query = "SELECT GREATEST(rt.qid, r.qid) AS qid, GREATEST(rt.sequence, r.sequence) AS seq,
                  GREATEST(rt.entered, r.entered) AS entered,
                  q.question, av.value, av.numeric_value, rt.answer FROM {$this->CONF['db_tbl_prefix']}questions q LEFT JOIN {$this->CONF['db_tbl_prefix']}results
                  r ON q.qid = r.qid LEFT JOIN {$this->CONF['db_tbl_prefix']}results_text rt ON q.qid = rt.qid LEFT JOIN
                  {$this->CONF['db_tbl_prefix']}answer_values av ON r.avid = av.avid WHERE q.sid = $sid {$_SESSION['filter'][$sid]}
                  ORDER BY seq, q.page, q.oid";

        $rs = $this->db->Execute($query);
        if($rs === FALSE)
        { $this->error('Error in query: ' . $this->db->ErrorMsg()); return; }

        $seq = '';
        $x = 0;
        while($r = $rs->FetchRow($rs))
        {
            if(!empty($r['qid']))
            {
                if($seq != $r['seq'])
                {
                    $x++;
                    $seq = $r['seq'];
                    $answers[$x]['date'] = date($date_format,$r['entered']);
                }

                switch($export_type)
                {
                    case EXPORT_CSV_NUMERIC:
                        if(empty($r['answer']))
                        { $value = $r['numeric_value']; }
                        else
                        { $value = $r['answer']; }
                    break;

                    case EXPORT_CSV_TEXT:
                    default:
                        if(empty($r['answer']))
                        { $value = $r['value']; }
                        else
                        { $value = $r['answer']; }
                    break;
                }

                if(isset($answers[$x][$r['qid']]))
                { $answers[$x][$r['qid']] .= MULTI_ANSWER_SEPERATOR . $value; }
                else
                { $answers[$x][$r['qid']] = $value; }
            }
            $last_date = date($date_format,$r['entered']);
        }
        $answers[$x]['date'] = $last_date;

        $line = '';
        foreach($questions as $question)
        { $line .= "\"" . str_replace('"','""',$question) . "\","; }
        $retval .= $line . "Datetime\n";

        $xvals = array_keys($answers);

        foreach($xvals as $x)
        {
            $line = '';
            foreach($questions as $qid=>$question)
            {
                if(isset($answers[$x][$qid]))
                {
                    if(is_numeric($answers[$x][$qid]))
                    { $line .= "{$answers[$x][$qid]},"; }
                    else
                    { $line .= "\"" . str_replace('"','""',$answers[$x][$qid]) . "\","; }
                }
                else
                { $line .= ","; }
            }
            $retval .= $line . '"' . $answers[$x]['date'] . "\"\n";
        }

        return $retval;
    }
}

?>

Link to comment
https://forums.phpfreaks.com/topic/230089-eporting-to-csv-file-newb-here/
Share on other sites
alacn Newbie

alacn

Posted
  • Author
Posted

i extracted the following code from the above code because it had export.csv in it which is the name of the file that gets extracted. The name of the file that i want to overwrite/ammend.

 

Do i have to edit something around here?

 

        header("Content-Type: text/plain; charset={$this->CONF['charset']}");
        header("Content-Disposition: attachment; filename=Export.csv");

DavidAM Member

DavidAM

Posted
Posted

When you say "export" are you talking about using your browser to download the file?

 

This line:

 header("Content-Disposition: attachment; filename=Export.csv");

 

is a suggestion to the browser to save the file as "Export.csv". However, the browser can do what it wants (usually based on your settings). My experience has been, that, in order to prevent the accidental loss of an existing file, the browser (in Windows at least) will add the "(2)", "(3)", ... to a filename when saving it. I have my browser set to always prompt me for the filename, so I can choose to overwrite, or even rename the file.

 

Unfortunately, I don't think there is any way that you can control this from PHP (and most likely not even from JavaScript). The closest you can come would be to add something to the filename you put in that header, such as the Year-Month-Day (i.e. Export20110309.csv) so when you see the files in your download folder you know at least when each was downloaded. Your other choice is to change your browser settings to always prompt you for a filename when downloading files.

alacn Newbie

alacn

Posted
  • Author
Posted

When you say "export" are you talking about using your browser to download the file?

 

This line:

 header("Content-Disposition: attachment; filename=Export.csv");

 

is a suggestion to the browser to save the file as "Export.csv". However, the browser can do what it wants (usually based on your settings). My experience has been, that, in order to prevent the accidental loss of an existing file, the browser (in Windows at least) will add the "(2)", "(3)", ... to a filename when saving it. I have my browser set to always prompt me for the filename, so I can choose to overwrite, or even rename the file.

 

Unfortunately, I don't think there is any way that you can control this from PHP (and most likely not even from JavaScript). The closest you can come would be to add something to the filename you put in that header, such as the Year-Month-Day (i.e. Export20110309.csv) so when you see the files in your download folder you know at least when each was downloaded. Your other choice is to change your browser settings to always prompt you for a filename when downloading files.

 

 

I understand.

 

Ok so is there a way inwhich i can delete the existing one through php so that when the new one gets saved, it doesnt make a copy?

And also is there a way i can format the export.csv (which is opened up in excel) ?

 

Because at the moment it exports the results of a survey out, and when i open it up in excel, it just looks really messy because theres no borders,colors or anything.

 

Im thinking, is there a way to format the excel from php after ive saved it?

 

Or if you have a completely better solution?

 

p.s to answer your questions, yes browser downloading file

 

Best Regards,

 

Alacn

DavidAM Member

DavidAM

Posted
Posted
Ok so is there a way inwhich i can delete the existing one through php so that when the new one gets saved, it doesnt make a copy?

No. You cannot delete a file on the client machine from the server (PHP). That would be a huge security issue. You can't even delete a file using JavaScript on the client side.

 

And also is there a way i can format the export.csv (which is opened up in excel) ?

 

Because at the moment it exports the results of a survey out, and when i open it up in excel, it just looks really messy because theres no borders,colors or anything.

 

Im thinking, is there a way to format the excel from php after ive saved it?

 

CSV stands for "Comma Separated Values". A CSV file is only meant to contain data, it cannot hold any formatting. If you want to add formatting, you would have to build an Excel (or other type of formatted) file instead. It seems to me that I have seen references to a PHP library that allows you to read and write XLS (native Excel) files. But I don't know anything about it as I have never used it.

alacn Newbie

alacn

Posted
  • Author
Posted

Ok so is there a way inwhich i can delete the existing one through php so that when the new one gets saved, it doesnt make a copy?

No. You cannot delete a file on the client machine from the server (PHP). That would be a huge security issue. You can't even delete a file using JavaScript on the client side.

 

And also is there a way i can format the export.csv (which is opened up in excel) ?

 

Because at the moment it exports the results of a survey out, and when i open it up in excel, it just looks really messy because theres no borders,colors or anything.

 

Im thinking, is there a way to format the excel from php after ive saved it?

 

CSV stands for "Comma Separated Values". A CSV file is only meant to contain data, it cannot hold any formatting. If you want to add formatting, you would have to build an Excel (or other type of formatted) file instead. It seems to me that I have seen references to a PHP library that allows you to read and write XLS (native Excel) files. But I don't know anything about it as I have never used it.

 

Ok No worries. I guess i can just format the downloaded file using a visual basic program.

 

Cheers for your help mate.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.