asked on

PHP Output large volume of lines to CSV file

Hi

I am having difficulty in outputting a large volume of lines (200,000+) in to a csv file die to memory errors. I need to be able to extract maybe up to 1,000,000 lines.

I am using the following code:
$fh = fopen($file, 'w') or die("can't open file");
fwrite($fh, $table);
fclose($fh);

I have these setting on the page:
ini_set('MAX_EXECUTION_TIME', -1);
set_time_limit(7200);
ini_set('memory_limit', '2048M');

When I output too many lines, the error i get is:
Fatal error: Out of memory (allocated 1766588416) (tried to allocate 103 bytes) in C:\websites\xxxxxx.co.uk\httpdocs\app\builder.php on line 532

The error obviously because I am outputting to memmory and then transferring to a csv.

I there a way to transfer directly to a file.

I am using PHP, SQL Server and SQLSRV.

Any help will be gratefully received

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

Fatal error: Out of memory ... on line 532

You can look at or near line 532 to see the instructions that are triggering the error. Do what Dave says - try eating the elephant one bite at a time instead of all at once.

Richard Lloyd

ASKER

Thanks for both of your comments.

I have altered my code to use the following

$sql= "select * from [xxxxx].[dbo].[xxxxxxx] ";
$result=sqlsrv_query($conn, $sql);

while ($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)) {
$array[] = $row;
}

function csv(array &$array)
{
if (count($array) == 0) {
return 'Empty Result Set';
}
ob_start();
$file = fopen("file.csv", 'w');
fputcsv($file, array_keys(reset($array)));
foreach ($array as $row) { //line 24
fputcsv($file, $row);
}
fclose($file);
return ob_get_clean();
}

This works, but now I get an error when it tries to export any date field:
Catchable fatal error: Object of class DateTime could not be converted to string in C:\xxxxxxxxxx\fiddle\csv\index.php on line 24 
[ Line 24 is shown above.]

Can you offer any advice on getting round this one?

Ray Paseur

Ray Paseur

The PHP DateTime object has no __tostring() method. You can extend the class to add a method, or you can call the format() method to turn the object into a string representation. Examples of how to handle Date/Time values are available in this article.
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

Ray Paseur

This is the opposite of what Dave recommended. This code construct is likely to be the most memory-intensive implementation you can get. It gathers all of the results set up at once, storing it in memory. A different design that might use a lot less memory would retrieve the rows with the while() loop and write each row immediately into the CSV output.

$sql= "select  * from [xxxxx].[dbo].[xxxxxxx] ";
$result=sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)) {
    $array[] = $row;
}

Open in new window

Richard Lloyd

ASKER

OK. Apologies for the dealy in coming back to you and thanks for your help so far.

I am now trying to run the script so that :

1. Generate the array of data from the sqlserver table,
2. for each record, open the file, output the $row to a csv, using fputcsv, then close the table. (this seems quite a laborious task when there are over 100,000+ lines!)
3. download the table

However I have not managed to get round the the problem that I refered to above about datetime items in the array. Is there away to convert all dattime elements in an array to a string?

Is there any other way to do this? I have been reading up in SQL Queryout, but can't find much about it.

Thanks for your continued help

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

abandoned question