dongshi2588 2017-08-25 08:07
浏览 55
已采纳

使用PHP在MySQL中导入CSV

I'm trying to import huge csv (> 1Gb) files in MySQL database:

/**
 * @param $file_path
 */
private function importFileContents($file_path)
{
    $query = sprintf("LOAD DATA LOCAL INFILE '%s' 
                        INTO TABLE file_import_contents 
                        FIELDS TERMINATED BY ';'
                        LINES TERMINATED BY '\
'
                        IGNORE 1 LINES
                        (@col1, @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9, @col10, @col11, @col12, @col13, @col14, @col15, @col16, @col17, @col18, @col19, @col20, @col21) 
                        set add_params=@col1, available=@col2, category_id=@col3, currency_id=@col4, delivery=@col5, description=@col6, model=@col8, modified_time=@col9, name=@col10, oldprice=@col11, param=@col12, pickup=@col13, picture=@col14, price=@col15, sales_notes=@col16, type=@col17, type_prefix=@col18, url=@col19, vendor=@col20, vendor_code=@col21  
                    ", addslashes($file_path));

    $em = $this->getContainer()->get('doctrine.orm.default_entity_manager');
    $connection = $em->getConnection();
    $statement = $connection->prepare($query);
    $statement->execute();
}

And the problem is when I execute sql count in db after import:

SELECT COUNT(*) FROM file_import_contents;

it returns me 1 648 990 rows, but when I run command in terminal for counting rows in all csv files:

find ./ -type f -name "*csv*" -exec wc -w {} +

it returns 76 319 426 total rows... My php params are:

upload_max_filesize = 32000M max_execution_time = 300000 max_input_time = 600000 memory_limit = 1024M

...and in script:

ini_set('memory_limit', '-1');

Why is this happening and how could I upload those files entirely? Thank for any advice

  • 写回答

2条回答 默认 最新

  • dshm8998473 2017-08-25 09:59
    关注

    Ok, It's working. Error was in counting lines in terminal:

    find ./ -type f -name "*csv*" -exec wc -w {} +
    

    instead -w must be -l

    find ./ -type f -name "*csv*" -exec wc -l {} +
    

    Result 2 million lines in 102 seconds for localhost

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 我的b站在没有碰到屏幕的情况下偶尔会自动跳出进度条,就像在屏幕上点了一下一样,但我并没有点。而且视频进度并没有变。这可能是什么原因造成的?
  • ¥30 STK matlab python仿真
  • ¥15 关于IMageEnView 图标定位问题
  • ¥20 求解答(matlab)
  • ¥30 ffmpeg库使用过程中遇到的问题
  • ¥15 pyqt5 中python如何通过Qtwebchannel主动发消息给web前端
  • ¥15 关于HTML中title获取xml内容的问题
  • ¥15 fanuc机器人PRIO083数字信号未复原错误,如何解决?
  • ¥20 如何为现有电路板增加远程控制功能
  • ¥15 UE5打包失败,求解决