Exporting an extremely large, poorly designed database

[SchweppesAle]

Ok guys, here's the problem.  We have a client who has a really-really big database table.  >140k entries.  I'd like to write this data to a csv file. 

 

The csv export function appears to be functioning correctly.  The query seems to work correctly as well, however only when limiting the number of return results as PHP quickly runs out of memory.  As a solution I've attempted to write the data in increments; slowly moving across their database until we've imported the entire thing.  Unfortunately it doesn't seem to move, rather it will export the first chunk repeatedly then crash.  I think it's the loop statement or something less obvious.  Take a look-I replaced the call to the csv($result) function with echo but that doesn't seem to return anything. 

 

<?php

$server = "someipaddress";
$username = "someusername";
$password = "somepassword";
error_reporting(ALL);		
	$connection = mssql_connect($server, $username, $password);

	if(!$connection)
	{
		die('Problem connecting: '.mssql());
	}

	$db = 'YRB';
	/*
	$query = "SELECT TOP 10 * FROM  dbo.PRODUCTS";*/
	mssql_select_db($db) or die('Can not select database');

$start = 0;
$loopForever = TRUE;
/*
$query = 'SELECT TOP 10 * FROM dbo.PRODUCTS
	WHERE ProductID NOT IN
	(SELECT TOP 10 ProductID FROM dbo.PRODUCTS ORDER BY ProductID DESC) ORDER BY ProductID DESC';

	$result = mssql_query($query);
	csv($result);*/



while($loopForever)
{
	$query = 'SELECT TOP 10000 * FROM dbo.PRODUCTS
	WHERE ProductID NOT IN
	(SELECT TOP '.$start.' ProductID FROM dbo.PRODUCTS ORDER BY ProductID DESC) ORDER BY ProductID DESC';

	$result = mssql_query($query);
	$number = mssql_num_rows($result);

	echo $number;

	if(!$result)
	{
		die('could not query database: '.mssql());
	}

		if($number < 10000)
		{
			$loopForever = FALSE;
			csv($result);
			exit("We're done");
		}else
			{
				csv($result);
				$start = $start + 10000;
			}

}


function csv($result)
   	{
       $count = mssql_num_fields($result);
       $output = '';
          
          /*
       for($i = 0; $i < $count; $i++)
       {
           $output .= mssql_field_name($result,$i).',';
                  
       }
       
       $output .= "\n";*/
       while($row = mssql_fetch_row($result))
       {
           $output .= implode(',', $row);
           $output .= "\n";
       }
       
       /*       echo $output;
       */
       
                      /*
           $filename = 'Query_'.date('l jS \of F Y h:i:s A').'.csv';*/
           $filename = 'database.csv';
           $file = fopen ($filename, "a");
           $write = fwrite($file,$output);
           fclose($file);
         
           if(!$write)
           {
           	die('problem writing to file');
           }        
           
   	} 
   



?>

[tail]

Why can't you do this with phpmyadmin? It has an option to export to CSV.

[trq]

Why can't you do this with phpmyadmin? It has an option to export to CSV.

 

Because it isn't a mysql database I would assume

 

MSSql's enterprise manager has functionality to export into many different formats that.

[SchweppesAle]

Why can't you do this with phpmyadmin? It has an option to export to CSV.

 

because our client is difficult.  this needs to be accomplished remotely without manipulating their database in any way. 

[laffin]

you shouldnt be storing all the results in php, and waiting to exhaust the memory limits.

depending on the data, check to see if it uses some common csv delimiters. (even tho comma may sound good, likelyhood of a comma being in a descriptrion is very high, consider using |

but the  sql fetch loop should be your csv output loop

 

some sample code

        $filename = 'database.csv';
        $file = fopen ($filename, "a");
       while($row = mssql_fetch_row($result))
       {
          fputcsv($file,$row,',');
       }
       fclose($file);

     

 

 

[crabfinger]

Well if they don't want you to modify anything on the server you could backup the database and put it on your own computer

 

BACKUP DATABASE <dbName> TO DISK = '<path>'
RESTORE DATABASE <dbName> FROM DISK = '<path>'

 

then use the management interface to "export to csv"

 

Why reinvent the wheel if the wheel already does exactly what you want it to do?

[SchweppesAle]

you shouldnt be storing all the results in php, and waiting to exhaust the memory limits.

depending on the data, check to see if it uses some common csv delimiters. (even tho comma may sound good, likelyhood of a comma being in a descriptrion is very high, consider using |

but the  sql fetch loop should be your csv output loop

 

some sample code

        $filename = 'database.csv';
        $file = fopen ($filename, "a");
       while($row = mssql_fetch_row($result))
       {
          fputcsv($file,$row,',');
       }
       fclose($file);

     

 

 

 

Turns out the script was working, just takes about 10(holy crap) minutes to finish.  Your suggestion still helped out though.  I was having problems with the string enclosures; this function did the trick, thanks. 

[laffin]

Congrats