导出excel或csv大行和动态列

Question

我有超过5k行和17列的excel数据，我在php中使用了嵌套循环技术，但这需要很长时间，使用xls文件格式处理数据需要45分钟，而使用csv文件格式需要30分钟，有没有一种技术可以加快将文件从 excel 上传到数据库（我使用 Postgresql）。我使用嵌套循环是因为有多少列取决于参数，而数据库的INSERTorUPDATE进程也取决于参数。这是我的导入过程代码    <?php    $row = 5000; // estimated row    $col = 17; // estimated col    for($i=1; $i<=$row; $i+=1){        for($j=1; $j<=$col; $j+=1){            $custno = $custno = $sheetData[$i][0];            $getId = "SELECT id from data WHERE 'custno' = $custno";            if($getId){               $update = "UPDATE data SET address = 'address 1' WHERE custno = $custno";            }else{               $insert = "INSERT INTO data (address) VALUES (address jon);            }        }    }我使用PhpSpreadsheet图书馆

Answer 1

如果您可以选择不使用 php 进行此操作，请尝试将此电子表格导出为 CSV 并使用COPY. 不会超过几秒钟。

如果您的数据库安装在本地，您只需在您选择的客户端中执行 COPY，例如pgAdmin. 检查此answer以获取更多信息。

COPY your_table FROM '/home/user/file.csv' DELIMITER ',' CSV HEADER;

请记住，postgres系统中的用户必须具有访问 CSV 文件的必要权限。检查如何在您的操作系统中执行此操作，例如chown在 Linux 中。

如果您的数据库安装在远程服务器中，您必须使用STDINviaCOPYpsql

$ cat file.csv | psql your_db -c "COPY your_table FROM STDIN;"

Answer 2

首先，尝试找出问题的根源是什么，是因为对文件的操作速度慢还是同时执行的SQL查询太多？

请记住，在循环中运行查询总是会带来性能问题。也许您可以通过在处理文件之前询问所需的数据来避免这种情况？您可能无法定义在该步骤中需要哪些数据，但获取比您需要的更多的数据可能仍然比逐个进行单独查询要快。另外，我想鼓励您限制INSERT或UPDATE查询。它们通常比SELECT一个慢。尝试为数据库写入操作收集数据并在循环后运行一次。

对于 CSV 操作，我更喜欢基本的 php 方法fgetcsv()，而str_getcsv()不是单独的库，只要文件没有过于复杂。如果您热衷于检查一些替代方案以PhpSpreadsheet查看 box.com 的Spout，它看起来很有希望，但我从未使用过。

我相信您可以通过使用PHP 生成器来提高性能，每次您必须读取文件内容时，它们都是完美的。这里有更多链接：

1. https://www.sitepoint.com/memory-performance-boosts-with-generators-and-nikiciter/

2. https://www.sitepoint.com/generators-in-php/

3. https://riptutorial.com/php/example/5441/reading-a-large-file-with-a-generator/