SQL Server - 从 PHP 导出为 CSV 作为流 - sql server - export from php to csv as a stream

sql server - export from php to csv as a stream

本文关键字：CSV Server PHP SQL | 更新日期: 2023-09-27

我有一个网站，它正在从MSSQL DB中提取数据并将其导出为csv。我遇到的问题是它正在提取所有数据并耗尽内存。错误：Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 805 bytes) in D:'WebContent'engsys.corp.ftr.com'ExportToExcel.php on line 94

有没有办法流式传输数据而不是将其全部拉到内存中？这是我现在的做法：

$getqueries = $conn->query($tsql);
//$result = $getqueries->fetchALL(PDO::FETCH_ASSOC); 
$filename = $_POST['ExportToExcel'];
header("Content-Type: application/x-csv");
header("Content-Disposition: attachment; filename=$filename.csv");
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
for ($i = 0;$i < $NumHeadings; $i++)
{
    $headings[] = $rHeadings[$i]["Headings"];
}
foreach($headings as $Heading => $value)
{
    echo "$value" . $sep;
}
//end of printing column names
echo $br;
$l = 0;
foreach($getqueries->fetch(PDO::FETCH_ASSOC) as $row)
{
        $pos = strpos($row,$sep);
        $CommentPos = strpos($rHeadings[$l]["Headings"],"comment");
        $NewLines = array("'r'n","'n'r","'n","'r");
        $UseValue = str_replace($NewLines, " ",$row);
        if($pos === FALSE)
        {
            if($CommentPos === FALSE)
            {
                echo $UseValue . $sep;
            }
            else
            {
                echo '"' . $UseValue . '"' . $sep;
            }
        }
        else
        {
            echo '"' . $UseValue . '"' . $sep;
        }
        $l++;
}

它

不是那么大的文件，最终小于 20mb，我的服务器似乎无法处理它。我环顾四周，但我没有看到任何可以解决我确切问题的东西。

我已经解决了不使用fetchALL()内存不足的问题：

$tsql = "Select Id,State,ProjectNumber,SubProjectNumber,PONumber
            from pmdb.MaterialTracking order by State,ProjectNumber";
$hsql = "select Headings from TableHeadings where TableName = 'MaterialTracking' 
         and Headings != 'Edit' order by ID";
$filename = $_POST['ExportToExcel'];
header("Content-Type: application/x-csv");
header("Content-Disposition: attachment; filename=$filename.csv");
/*******Start of Formatting for Excel*******/
//define separators (defines columns in excel)
$sep = ",";
$br = "'r'n"; //line break
$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$headings = array();
$NumHeadings = count($rHeadings);
for ($i = 0;$i < $NumHeadings; $i++)
{
    $headings[] = $rHeadings[$i]["Headings"];
}
//start of printing column names as names of SQL fields
foreach($headings as $Heading => $value)
{
    echo "$value" . $sep;
}
//end of printing column names
echo $br; //separate the headers and the data
foreach($conn->query($tsql) as $row)
{
    for ($i = 0;$i < $NumHeadings;$i++)
    {
        $CommentPos = strpos($rHeadings[$i]["Headings"],"comment");
        $NewLines = array("'r'n","'n'r","'n","'r");
        $UseValue = str_replace($NewLines, " ",$row[$i]);
        $UseValue = str_replace('"', "'",$row[$i]);
        $pos = strpos($UseValue,",");
        if ($CommentPos === FALSE || $pos === FALSE || isset($UseValue))
        {
            echo '"' . $UseValue . '"' . $sep;
        }
        else
        {
            echo $UseValue . $sep . "Not quoted";
        }
    }
}

此表中实际上有 39 列，总共（至少今天）为 39812 行。这给了我需要的csv。