MYSQL自动使用mysqlimport从文件夹插入csv文件 [英] MYSQL automatically insert csv files from folder using mysqlimport

问题描述

我想在指定时间自动从指定的「资料夹」汇入大约3000个CSV档案。
每天CSV文件将被更新以包含新数据。

我理解我应该使用命令行工具mysqlimport和--replace选项，

解决方案

 加载数据本地infile'uniq。 cbv到表tblUniq（field1，field2，field3）
以'，'结束的字段
''''包含
以'\\\
'结束的行
  

这是一个可选的解决方案，你唯一需要的是一个PHP \Perl \Python脚本，

PHP代码：

 <？php 
 
 $ basedir =/ path / to / dir
 if（$ handle = opendir（$ basedir））{
 while（false！== （$ entry = readdir（$ handle）））{
 $ query =LOAD DATA LOCAL INFILE $ basedir $ entry 
 INTO TABLE tableName 
 FIELDS 
 TERMINATED BY'，'
可选地由'\'''附加
 ESCAPED BY'\\'
由'\\\
'终止的行
 IGNORE 1 LINES）; 
 if（DEBUG）{echo $ query。 \\\
; } 
 if（！mysql_query（$ query））{
 die（'MySQL error：'。mysql_error（））; 
} 
} 
} else {
 echo无法打开$ basedir; 
} 
？> 
  

I would like to import around 3000 CSV files from a specified "folder" automatically at a designated time. Everyday the CSV files will be updated to include new data.

I understand that I should use the command line tool "mysqlimport" and --replace option, (as sometimes old data will be altered)

解决方案

load data local infile 'uniq.csv' into table tblUniq(field1, field2, field3)
fields terminated by ','
enclosed by '"'
lines terminated by '\n'

This is an optional solution, the only thing you'll need is an PHP\Perl\Python script to itreate over all of the files, and insert them one by one

PHP code:

<?php

    $basedir = "/path/to/dir"
    if ($handle = opendir($basedir)) {
      while(false !== ($entry = readdir($handle))) {
        $query = "LOAD DATA LOCAL INFILE $basedir$entry
                    INTO TABLE tableName
                    FIELDS
                    TERMINATED BY ','
                    OPTIONALLY ENCLOSED BY '\"'
                    ESCAPED BY '\\'
                    LINES TERMINATED BY '\n'
                    IGNORE 1 LINES");
        if(DEBUG) { echo $query . "\n"; }
        if(!mysql_query($query)) {
          die('MySQL error: ' . mysql_error());
        }
      }
    } else {
      echo "Could not open $basedir";
    }
    ?>

这篇关于MYSQL自动使用mysqlimport从文件夹插入csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！