Can't convert array and display as csv

user_3052443 0
Viewed 60

I am trying to read in a txt file, convert it to csv and display it in a spreadsheet. There are many example here and I have tried many of them but the result is the same. I seem to have two problems. First, the code I use to convert the csv line adds an extra comma to the end and I can't get it deleted. Second, even when entering a test string the spreadsheet doesn't open.

When I read in my test file I get an array like this:

    array [
    Hunky Bill's Big Perogie / Pierogi Maker
    9
    4
    10
    5
    5
    1
    2
    1
    ]

The code I am using is

    $csv_file = 'arry.txt';
    $save_name = 'csvout.csv';

    if (($fp = fopen($csv_file ,'r'))) {  
       while(! feof($fp)) { 
          $item = fgets($fp);
          $item = rtrim($item, "\r\n");
          $csv_string .= '"' . $item . '",';
         
         // $csv_string = substr($csv_string, 0, -1);  
         // $csv_string[strlen($csv_string)] = '';
         // $csv_string = rtrim($csv_string, ',');
       }      
       fclose($fp);  
    }  

    header("Expires: Mon, 26 Nov 2162 00:00:00 GMT");
    header("Last-Modified: " . gmdate('D,d M Y H:i:s') . ' GMT');
    header("Cache-Control: no-cache, must-revalidate");
    header("Pragma: no-cache");
    header("Content-Type: Application/octet-stream");
    header("Content-Disposition: attachment; filename=$save_name");
          
    echo $csv_string;

Running the above displays this on the screen:

    "Hunky Bills Big Perogie  Pierogi Maker","9","4","10","5","5","1","2","1","","",

If I uncomment any of the three lines meant to remove that last comma, all of the commas are removed. Why is that?

If I skip the above and just enter this line before the echo statement, the spreadsheet still doesn't open. Should it open in the spreadsheet program?

    $csv_string = '"Hunky Bills Big Perogie  Pierogi Maker","9","4","10","5","5","1","2","1"';

Can anyone see where my problems are?

spreadsheet csv arrays php
2 Answers

The while loop that you are using adds a comma after each item. The "three lines meant to remove that last comma" are located inside the while loop. To solve that problem, try reorganizing your code like this, with the last comma removal coming after the completion of the while loop:

if (($fp = fopen($csv_file ,'r'))) {  
    while(! feof($fp)) { 
        $item = fgets($fp);
        $item = rtrim($item, "\r\n");
        $csv_string .= '"' . $item . '",';
    }
    $csv_string = substr($csv_string, 0, -1);  
    fclose($fp);
}

Further, echo $csv_string at the end of the code is instructing the program to output the string to the screen, so that is what you see. You may want to save $csv_string to a text file with a .csv extension and then try to open it with your spreadsheet program.

user_32585310

First, when debugging this kind of quote, skip all of the header and Excel stuff until you actually need it. It is so much easier to debug with raw text.

Second, instead of echo, check out var_dump, it shows the contents of variables in a clearer way.

My recommendation is to store the contents in an array and explode it with a comma at the end, see the comments for more details.

// We'll store items here temporarily
$csv_items = [];

if (($fp = fopen($csv_file, 'r'))) {
    while (!feof($fp)) {

        $item = fgets($fp);

        // Sanity check that we received something
        if (false === $item) {
            break;
        }

        // Trim both sides (unless you have a need for whitespace)
        $item = trim($item);

        // If we still have more than just an empty string
        if ($item) {
            // Append, optionally with quotes
            $csv_items[] = '"'.$item.'"';
        }
    }
    fclose($fp);
}

// Create final string
$csv_string = implode(',', $csv_items);

var_dump($csv_string);

Demo here: https://3v4l.org/Chk2M

Also, if your data might contain quotes you might need to deal with that, too. I personally use a library for this which is often overkill, but then I don't need to worry about it. You could also write the CSV using fputcsv and others.

user_2313160
Related