dongyu1125 2018-08-21 14:09
浏览 591
已采纳

如何将大型csv文件拆分为多个csv文件

We downloaded .osm file from openstreetmaps gis data and converted it into .csv file through osmconvert.exe. The csv file is of 3.5 GB of size. We tried importing it to the database through heidisql. Also tried to import the file into database using below php script

$path = "../../indiacountry.csv";
    $row = 0;
    if (($handle = fopen($path, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $row++;
            $data_entries[] = $data ;

        }
        fclose($handle);
    }
    // this you'll have to expand
    foreach($data_entries as $line){

    $ts++;
    if ($ts>0)
    {
    $ft++;
 if(mysql_query("insert into mbrace_resources.street_unit_number_india(id1) values ('".str_replace ("'","",$line [0])."')") or die("the eror ".mysql_error()));

 }

      // $db->execute($line);
    }

When we first tried this script, there was memory_limit error and timeout. We changed memory_limit to 4000MB and set time limit to 0. Then tried the script again, the page was blank and continuously tried to execute the script, but not a single row got inserted into the table.

After going through all of this, we feel the only way forward was to split the csv file into multiple files.

How shall we do it.

Thanks in advance

  • 写回答

2条回答 默认 最新

  • douchujian8124 2018-08-21 15:14
    关注

    The script you show is reading the WHOLE .csv file into an in memory array. Its not surprising it wont run that will require at least 3.5gig+ of memory.

    Instead read one line from the file and apply it directly to the database.

    I am going to ignore the fact you are using the old, dangerous and deprecated mysql_ database extension for now. If you tell me you have access to mysqli_ or PDO I will willingly rewrite this for either of those API's

    $path = "../../indiacountry.csv";
    $row = 0;
    if (($handle = fopen($path, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $row++;
            $id = str_replace ("'","",$line [0]);
            mysql_query("insert into mbrace_resources.street_unit_number_india 
                        (id1) values ('$id')") 
                or die("the eror ".mysql_error());
        }
        fclose($handle);
    }
    
    echo "Finished: Added $row rows";
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误