PHP合并两个CSV文件仅返回前两行问题技术求助

问题背景 #

你尝试用PHP合并两个CSV文件，从第一个CSV提取Cost字段（对应代码中的$spent），从第二个CSV提取payout字段，关联依据是两个文件中的推广活动名称（第一个的Campaign Name、第二个的name）。但运行后发现只有前两行能正确匹配到数据，后续行无法获取第二个CSV的payout值。

第一个CSV（提取Cost字段） #

Campaign ID,Campaign Name,Impressions,Rate,Cost
4833222,ZroJumiaNGMob1,15335,0.00014,2.1469
4833236,ZroJumiaNGMob2,36921,0.00015,5.53815
4877020,ZroJumiaNGMob3,781926,0.00015,117.2889
4948833,ZroJumiaNGMob4,900899,0.00026,234.23374
4984715,ZroJumiaNGMob5,440423,0.00021,92.48883
4984722,ZroJumiaNGMob6,542272,0.00024,130.14528

第二个CSV（提取payout字段） #

Offer.name,name,Stat.impressions,Stat.conversions,Stat.clicks,payout
Aliexpress Online Store - revShare International,ZroJumiaNGMob1,0,205,83598,47.4551
Aliexpress Online Store - revShare International,ZroJumiaNGMob2,0,13,12080,1.6562
Aliexpress Online Store - revShare International,ZroJumiaNGMob3,0,50,14750,13.0269
Aliexpress Online Store - revShare International,ZroJumiaNGMob4,0,17,7108,3.5583
Aliexpress Online Store - revShare International,ZroJumiaNGMob5,0,25,11045,2.1942
Aliexpress Online Store - revShare International,ZroJumiaNGMob6,0,70,27187,12.3148

你的原代码片段：

if ($ext1 == 'csv' && $ext2 == 'csv') { 
    $path1 = ROOT . "/uploads/" . md5(uniqid()) . ".csv"; 
    $path2 = ROOT . "/uploads/" . md5(uniqid()) . ".csv"; 
    $file1 = $_FILES['upload']['tmp_name']; 
    $file2 = $_FILES['upload2']['tmp_name']; 
    move_uploaded_file($file1, $path1); 
    move_uploaded_file($file2, $path2); 
    $file1 = fopen($path1, 'r'); 
    $file2 = fopen($path2, 'r'); 
    if (file_exists($path1) && file_exists($path2)) { 
        header('Content-Type: text/csv'); 
        header('Content-Disposition: attachment; filename="sample.csv"'); 
        $output_name = md5(uniqid()) . ".csv"; 
        $output = ROOT . "/uploads/output/" . $output_name; 
        $fp = fopen($output, 'wb'); 
        fputcsv($fp, array('campaign', 'spent', 'payout', 'profit', 'roi'), ','); 
        $found_campaigns = []; 
        fgetcsv($file1); // Skip first line 
        while (($data = fgetcsv($file1)) !== FALSE) { 
            array_push($found_campaigns, $data[$campaign]); 
            echo "<pre>"; 
            var_dump($found_campaigns); // Showing the found campgains 
            echo "</pre>"; 
            echo "</br>"; 
            // Check if the second file have same campgain name with the first file campgain name if yes take the payout field and merge them with the spent field. 
            // This returning only the data of the two first lines only ( Need to return all the equal fields ) 
            $found_in_file_2 = false; 
            $pout = ''; 
            while (($data2 = fgetcsv($file2)) !== FALSE && !$found_in_file_2) { 
                var_dump($data2); // Show the fields of the second file 
                if ($data2[$campaign2] == $data[$campaign]) { 
                    $found_in_file_2 = true; 
                    $pout = $data2[$payout]; 
                } 
            } 
            $pout = str_replace('$', '', $pout); 
            $line = [ 
                $data[$campaign], 
                $data[$spent], 
                $pout, 
                $pout - $data[$spent], 
                ($pout - $data[$spent]) / $data[$spent] 
            ]; 
            fputcsv($fp, $line, ','); 
        } 
        $file1 = fopen($path1, 'r'); 
        $file2 = fopen($path2, 'r'); 
        fgetcsv($file2); // Skip first line 
        while (($data = fgetcsv($file2)) !== FALSE) { 
            if (!in_array($data[$campaign2], $found_campaigns)) { 
                $found_in_file_1 = false; 
                $spt = ''; 
                while (($data2 = fgetcsv($file1)) !== FALSE && !$found_in_file_1) { 
                    if ($data2[$campaign] == $data[$campaign2]) { 
                        echo $data2[$campaign] . ' ' . $data[$campaign2] . '<br>'; 
                        $found_in_file_1 = true; 
                        $spt = $data2[$spent]; 
                    } 
                } 
                $spt = str_replace('$', '', $spt); 
                $pout = $data[$payout]; 
                $pout = str_replace('$', '', $pout); 
                if ($spt > 0) { 
                    $roi = ($pout - $spt) / $spt; 
                } else { 
                    $roi = 0; 
                } 
                $line = [ 
                    $data[$campaign2], 
                    $spt, 
                    $pout, 
                    $pout - $spt, 
                    $roi 
                ]; 
                fputcsv($fp, $line, ','); 
            } 
        } 
        fclose($fp); 
        $now = date("d_m_Y H:i",time()); 
        echo '<a class="btn btn-primary" href="uploads/output/' . $output_name . '" download="'. $now .'.csv">Download Merged File</a>'; 
    } 
} 

核心原因分析 #

问题出在文件指针没有重置：

• 第一次循环第一个CSV的行时，你遍历第二个CSV找到匹配项，此时第二个CSV的文件指针已经停在匹配行的下一行；
• 第二次循环第一个CSV的行时，你直接继续从第二个CSV的当前指针位置开始读取，而不是回到文件开头；
• 当循环到第一个CSV的第三行及以后时，第二个CSV的指针已经走到文件末尾，无法读取到任何内容，自然匹配不到对应的payout值。

另外，你在处理完第一个CSV的所有行后，重新打开了两个文件，但同样的问题也存在于第二个循环中——每次查找第一个CSV时，指针没有重置，导致后续匹配失败。

修复方案 #

方案1：每次查找前重置文件指针（简单直接） #

在第一个CSV的循环内部，每次开始查找第二个CSV之前，用rewind()重置文件指针到开头，然后重新跳过表头：

// 在第一个CSV的while循环内，查找第二个CSV之前添加：
rewind($file2); // 重置第二个CSV的文件指针到开头
fgetcsv($file2); // 重新跳过表头

同样，在处理第二个CSV的循环内部，每次查找第一个CSV之前也要重置指针：

// 在第二个CSV的while循环内，查找第一个CSV之前添加：
rewind($file1); // 重置第一个CSV的文件指针到开头
fgetcsv($file1); // 重新跳过表头

方案2：预加载CSV到数组（更高效） #

如果CSV文件不大，建议先把第二个CSV的内容加载到一个关联数组中，用活动名称作为键，这样不需要每次都遍历整个文件，效率更高：

if ($ext1 == 'csv' && $ext2 == 'csv') { 
    $path1 = ROOT . "/uploads/" . md5(uniqid()) . ".csv"; 
    $path2 = ROOT . "/uploads/" . md5(uniqid()) . ".csv"; 
    $file1 = $_FILES['upload']['tmp_name']; 
    $file2 = $_FILES['upload2']['tmp_name']; 
    move_uploaded_file($file1, $path1); 
    move_uploaded_file($file2, $path2); 

    // 预加载第二个CSV到关联数组：键为活动名称，值为payout
    $campaignPayoutMap = [];
    $file2Handle = fopen($path2, 'r');
    fgetcsv($file2Handle); // 跳过表头
    while (($data2 = fgetcsv($file2Handle)) !== FALSE) {
        $campaignName = $data2[$campaign2];
        $campaignPayoutMap[$campaignName] = str_replace('$', '', $data2[$payout]);
    }
    fclose($file2Handle);

    // 预加载第一个CSV到关联数组（可选，用于后续反向匹配）
    $campaignSpentMap = [];
    $file1Handle = fopen($path1, 'r');
    fgetcsv($file1Handle); // 跳过表头
    while (($data1 = fgetcsv($file1Handle)) !== FALSE) {
        $campaignName = $data1[$campaign];
        $campaignSpentMap[$campaignName] = str_replace('$', '', $data1[$spent]);
    }
    fclose($file1Handle);

    if (file_exists($path1) && file_exists($path2)) { 
        header('Content-Type: text/csv'); 
        header('Content-Disposition: attachment; filename="sample.csv"'); 
        $output_name = md5(uniqid()) . ".csv"; 
        $output = ROOT . "/uploads/output/" . $output_name; 
        $fp = fopen($output, 'wb'); 
        fputcsv($fp, array('campaign', 'spent', 'payout', 'profit', 'roi'), ','); 

        $found_campaigns = [];
        // 处理第一个CSV的所有行，匹配payout
        $file1Handle = fopen($path1, 'r');
        fgetcsv($file1Handle); // 跳过表头
        while (($data = fgetcsv($file1Handle)) !== FALSE) { 
            $campaignName = $data[$campaign];
            $found_campaigns[] = $campaignName;
            $spent = str_replace('$', '', $data[$spent]);
            $pout = isset($campaignPayoutMap[$campaignName]) ? $campaignPayoutMap[$campaignName] : 0;
            
            $profit = $pout - $spent;
            $roi = $spent > 0 ? $profit / $spent : 0;

            $line = [ 
                $campaignName, 
                $spent, 
                $pout, 
                $profit, 
                $roi 
            ]; 
            fputcsv($fp, $line, ','); 
        } 
        fclose($file1Handle);

        // 处理第二个CSV中不在第一个CSV里的行
        $file2Handle = fopen($path2, 'r');
        fgetcsv($file2Handle); // 跳过表头
        while (($data = fgetcsv($file2Handle)) !== FALSE) { 
            $campaignName = $data[$campaign2];
            if (!in_array($campaignName, $found_campaigns)) { 
                $pout = str_replace('$', '', $data[$payout]);
                $spent = isset($campaignSpentMap[$campaignName]) ? $campaignSpentMap[$campaignName] : 0;
                
                $profit = $pout - $spent;
                $roi = $spent > 0 ? $profit / $spent : 0;

                $line = [ 
                    $campaignName, 
                    $spent, 
                    $pout, 
                    $profit, 
                    $roi 
                ]; 
                fputcsv($fp, $line, ','); 
            } 
        } 
        fclose($file2Handle);
        fclose($fp); 

        $now = date("d_m_Y H:i",time()); 
        echo '<a class="btn btn-primary" href="uploads/output/' . $output_name . '" download="'. $now .'.csv">Download Merged File</a>'; 
    } 
} 

这个方案的优势是只需要遍历每个CSV两次（预加载+输出），而不是N次（N为第一个CSV的行数），性能提升明显，尤其是当CSV文件较大时。

验证说明 #

修复后，两个CSV中的所有匹配活动都会正确关联Cost和payout字段，生成完整的合并CSV，包含所有行的campaign、spent、payout、profit和roi数据。

内容的提问来源于stack exchange，提问作者Prog AF