CSV generated file in PHP : last value is truncated

Question

In CodeIgniter 2, I'm generating a CSV file from fetching data from my Oracle Database (volume of data can be a less than 10 lines to hundreds of thousands of lines, this is why I "unset" each $line) using the following code :

        $conn = $this->db;

        $stid = oci_parse($conn->conn_id, $sql);
        oci_execute($stid);

        // Header extraction
        $headers = "";
        $values = "";

        $row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS );

        foreach($row as $key => $value) {
            $headers = $headers.$key.';';
            $values = $values.$value.';';
        }
        // Remove of last comma and adding in a line break
        $headers = substr($headers,0,-1). "\r\n";
        $values = substr($values,0,-1). "\r\n";
        $data = $headers . $values;

        $values = "";

        // Parsing all data to concatenate the values
        while (($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
            $line = "";
            foreach ($row as $key => $value) {
                $line .= $value . ';';
            }
            $line = substr($line, 0, -1) . "\n";
            $data = $data . $line;
            // Freeing memory for the line
            unset($line);
        }
        return $data;

Using this piece code, my CSV file is properly generated except for one thing.

When I'm exporting just a small number of lines, everything is fine. I have all the values, for ALL lines, comma seperated. However, when I'm exporting a few hundred or thousand of lines, the LAST value from the LAST line is always truncated by 5 characters (data in the database is OK).

HEADER1;HEADER2;HEADER3;HEADER4;HEADER5
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
[.......]
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX

I thought maybe it was some cache limitation or something, but it happens after a few hundred lines exported and wether it's just a few hundred or several hundred thousand lines.

I can't figure this one out ...

Can anybody help ?

Thanks in advance.

I'd suggest using PHP's fputcsv() function rather than outputting it yourself with that foreach loop. — Simba
– Simba, Commented Sep 9, 2015 at 15:00
Also, given the fairly simple dump you're doing, isn't it possible to use Oracle to output the CSV directly? (maybe like this? I don't know Oracle that well, but it's the sort of thing I'd expect to be able to do with a decent DB engine these days) — Simba
– Simba, Commented Sep 9, 2015 at 15:04
Exporting Query result into CSV format from SQL Dev or TOAD or any Oracle Client is easy. Here I need to export from a web browser using PHP interface. I'm not sure how to implement fputcsv() in my code as I need to unset each line to avoid have my Cache full. — Kavatah
– Kavatah, Commented Sep 9, 2015 at 15:32
fputcsv() will output to a file. However, if you're struggling with memory space (which I can see you would be doing the above), consider exporting the CSV to a temp file on disk; then all PHP has to do is readfile() to send it to the browser and then delete it. If you can get Oracle to output the CSV file for itself, then PHP's memory limits never need to be worried about. And it'll be a lot quicker. — Simba
– Simba, Commented Sep 9, 2015 at 15:42
Actually, using Codeigniter built in csv functions and file generator, I managed to generate my csv file on the server. After I have done this, I forced the download of the generated file. This I noticed the following : my file on the server is complete... Last value of last line is not truncated. However, the file that I download as that truncated last line. So my above code isn't the issue... I tried with Firefox and chrome and I have the same behavior... Very strange... — Kavatah
– Kavatah, Commented Sep 9, 2015 at 19:42

Kavatah · Accepted Answer · 2015-09-15 10:13:08Z

2

Original issue described in this post not related to the code.

The issue comes from the Header Content Length passed by CodeIgniter force_download() function (from CI download helper).

header("Content-Length: ".strlen($data));

strlen($data) doesn't return (in my case) the correct length of data and my browser then only downloads the amount of data that was passed in the header, hence truncating a few characters at the end.

Not finding anything on this "issue", I decided to comment out the header Content length line

edited Sep 15, 2015 at 10:13

answered Sep 10, 2015 at 10:59

Kavatah

436 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CSV generated file in PHP : last value is truncated

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related