php - 从外部 FTP 站点导入 csv 文件

Question

我有以下脚本可以从我服务器上的 csv 文件导入数据，该脚本可以正常工作。

我需要将文件的位置从我的服务器更改为需要身份验证的 FTP 服务器。 FTP 服务器上的 csv 文件名将根据生成的时间戳进行更改。导入MySQL数据库后，需要删除FTP服务器上的文件。

然后我需要使用 cron 作业安排此作业每 5 分钟运行一次。

我们将不胜感激。

谢谢。

---

<?php

/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "localhost";
$databasename = "dbname";
$databasetable = "dbtable";
$databaseusername ="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filenamewithtimestamp.csv";
/********************************/
/* Would you like to add an empty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "output.sql";
/********************************/


if(!file_exists($csvfile)) {
    echo "File not found. Make sure you specified the correct path.\n";
    exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
    echo "Error opening data file.\n";
    exit;
}

$size = filesize($csvfile);

if(!$size) {
    echo "File is empty.\n";
    exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

    $lines++;

    $line = trim($line," \t");

    $line = str_replace("\r","",$line);

    /************************************
    This line escapes the special character. remove it if entries are already escaped in the csv file
    ************************************/
    $line = str_replace("'","\'",$line);
    /*************************************/

    $linearray = explode($fieldseparator,$line);

    $linemysql = implode("','",$linearray);

    if($addauto)
        $query = "insert into $databasetable values('','$linemysql');";
    else
        $query = "insert into $databasetable values('$linemysql');";

    $queries .= $query . "\n";

    @mysql_query($query);
}

@mysql_close($con);

if($save) {

    if(!is_writable($outputfile)) {
        echo "File is not writable, check permissions.\n";
    }

    else {
        $file2 = fopen($outputfile,"w");

        if(!$file2) {
            echo "Error writing to the output file.\n";
        }
        else {
            fwrite($file2,$queries);
            fclose($file2);
        }
    }

}

echo "Found a total of $lines records in this csv file.\n All records imported";


?>

---

Answer 1

我以为我会发布解决我的问题的代码；它从外部 FTP 站点下载一个 csv 文件到我的本地服务器上。然后它将 csv 文件导入到我本地的 mysql 数据库表中。导入后，ftp 服务器上的文件将被删除。我使用 cronjob 在 cpanel 中安排这项工作。

我不能把脚本归功于我，我刚刚修改了在另一个站点上找到的脚本。我确信有一些更好的方法可以通过一些错误检查来做到这一点。但是，这是我有限的技能所能做到的最好的结果。

我希望这可以帮助 future 的访问者解决同样的问题。感谢所有帮助过我的人。这是我使用的代码:

<?php

$source = "DespGoods.csv";
$target = fopen("DespGoods.csv", "w");
$conn = ftp_connect("ftp.server.com") or die("Could not connect");

ftp_login($conn,"ftpusername","ftppassword");
ftp_fget($conn,$target,$source,FTP_ASCII);

echo "file downloaded.\n";

/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$dbhost = "localhost";
$dbname = "dbname";
$dbtable = "despgoods";
$dbusername ="dbusername";
$dbpassword = "dbpassword";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "DespGoods.csv";
/********************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment 
/* integer and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not 
/* exist in the table.
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file 
/* through ftp and change the permissions, or execute at the prompt: 
/*     touch output.sql && chmod 777 output.sql
/********************************/
$save = 0;
$outputfile = "output.sql";
/********************************/

if(!file_exists($csvfile)) {
    echo "File not found. Make sure you specified the correct path.\n";
    exit;
}

$file = fopen($csvfile,"r");
if(!$file) {
    echo "Error opening data file.\n";
    exit;
}

$size = filesize($csvfile);
if(!$size) {
    echo "File is empty.\n";
    exit;
}

$csvcontent = fread($file,$size);
fclose($file);

$con = @mysql_connect($dbhost,$dbusername,$dbpassword) or die(mysql_error());
@mysql_select_db($dbname) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {
    $lines++;
    $line = trim($line," \t");
    $line = str_replace("\r","",$line);

    /************************************
    /* This line escapes the special character. 
    /* Remove it if entries are already escaped in the csv file
    /************************************/
    $line = str_replace("'","\'",$line);
    /*************************************/

    $linearray = explode($fieldseparator,$line);
    $linemysql = implode("','",$linearray);

    if($addauto) {
        $query = "insert into $dbtable values('','$linemysql');";
    }
    else {
        $query = "insert into $dbtable values('$linemysql');";
    }
    $queries .= $query . "\n";
    @mysql_query($query);
}

@mysql_close($con);
if($save) {
    if(!is_writable($outputfile)) {
        echo "File is not writable, check permissions.\n";
    }
    else {
        $file2 = fopen($outputfile,"w");

        if(!$file2) {
            echo "Error writing to the output file.\n";
        }
        else {
            fwrite($file2,$queries);
            fclose($file2);
        }
    }
}

echo "Found a total of $lines records in this csv file.\n";

$source = "DespGoods.csv";
$target = fopen("DespGoods.csv", "w");
$conn = ftp_connect("ftp.server") or die("Could not connect");

ftp_login($conn,"ftpusername","ftppassword");
ftp_delete($conn,$source);
ftp_close($conn);

echo "file deleted";
mysql_close($con);

?>