从外部FTP站点导入csv文件 [英] import csv file from external FTP Site

问题描述

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

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

然后，我需要使用cron作业安排这项工作，每5分钟运行一次。 / b>

任何协助都将值得赞赏。

谢谢。

< hr>

 <？php 
 
 / *************** ***************** / 
 / *代码位于http://legend.ws/blog/tips-tricks/csv-php-mysql-import/ 
 / *编辑下面的条目以反映适当的值
 / ******************************** / 
 $ databasehost =localhost; 
 $ databasename =dbname; 
 $ databasetable =dbtable; 
 $ databaseusername =username; 
 $ databasepassword =password; 
 $ fieldseparator =，; 
 $ lineseparator =\\\
; 
 $ csvfile =filenamewithtimestamp.csv; 
 / ******************************** / 
 / *您想添加一个这些记录开始时是否为空字段？ 
 / *如果第一个字段为auto_increment整数
 / *且csv文件没有诸如记录之前的空字段的表，那么这非常有用。 
 / *将1设为是，0设为否。注意：如果您不确定，请不要设置为1。 
 / *如果这个额外的字段在表中不存在，这可以将数据转储到错误的字段中
 / ********************* *********** / 
 $ addauto = 0; 
 / ******************************** / 
 / *您想保存mysql查询文件中？如果是，请将$ save设置为1. 
 / *文件的权限应设置为777.可以通过ftp和
 / *上传示例文件更改权限，或者在提示符下执行：touch output .sql&& chmod 777 output.sql 
 / ******************************** / 
 $ save = 0; 
 $ outputfile =output.sql; 
 / ******************************** / 
 
 
如果（！file_exists（$ csvfile））{
 echo找不到文件，请确保指定了正确的路径。 
出口; 
} 
 
 $ file = fopen（$ csvfile，r）; 
 
 if（！$ file）{
 echo打开数据文件时出错。\\\
; 
出口; 
} 
 
 $ size = filesize（$ csvfile）; 
 
 if（！$ size）{
 echoFile is empty.\\\
; 
出口; 
} 
 
 $ csvcontent = fread（$ file，$ size）; 
 
 fclose（$ file）; 
 
 $ con = @mysql_connect（$ databasehost，$ databaseusername，$ databasepassword）或die（mysql_error（））; 
 @mysql_select_db（$ databasename）或die（mysql_error（））; 
 
 $ lines = 0; 
 $ queries =; 
 $ linearray = array（）; 
 
 foreach（split（$ lineseparator，$ csvcontent）as $ line）{
 
 $ lines ++; 
 
 $ line = trim（$ line，\t）; 
 
 $ line = str_replace（\r，，$ line）; 
 
 / ************************************ 
这个线逃脱特殊字符。如果条目已经在csv文件中转义，则将其删除
 *********************************** * / 
 $ 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。 \\\
; 
 
 @mysql_query（$ query）; 
} 
 
 @mysql_close（$ con）; 
 $ b $ if（$ save）{
 
 if（！is_writable（$ outputfile））{
 echo文件不可写，检查权限。\ n ; 
} 
 
 else {
 $ file2 = fopen（$ outputfile，w）; 
 
 if（！$ file2）{
 echo写入输出文件时出错。\ n; 
} 
 else {
 fwrite（$ file2，$ queries）; 
 fclose（$ file2）; 
 
 
 
 
 $ b echo在此csv文件中找到总共$行记录。 
 
 
？> 
  

---

解决方案

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

我不能赞扬脚本，我刚刚修改了另一个站点上的脚本。我确信有一些更好的方法可以做一些错误检查。然而，这是我用我有限的技能可以做到的最好的。

我希望这可以帮助未来的访问者解决同样的问题。感谢所有那些帮助我的人。
以下是我使用的代码：

 <？php 
 
 $ source = DespGoods.csv; 
 $ target = fopen（DespGoods.csv，w）; 
 $ conn = ftp_connect（ftp.server.com）或死（无法连接）; 
 
 ftp_login（$ conn，ftpusername，ftppassword）; 
 ftp_fget（$ conn，$ target，$ source，FTP_ASCII）; 
 
回显文件已下载。\ n; 
 
 / ******************************** / 
 / *代码at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/ 
 / *编辑下面的条目以反映适当的值
 / ******** ************************ / 
 $ dbhost =localhost; 
 $ dbname =dbname; 
 $ dbtable =despgoods; 
 $ dbusername =dbusername; 
 $ dbpassword =dbpassword; 
 $ fieldseparator =，; 
 $ lineseparator =\\\
; 
 $ csvfile =DespGoods.csv; 
 / ******************************** / 
 / *您想添加一个这些记录的开头是空字段吗？ 
 / *如果您的表的第一个字段为auto_increment 
 / *整数且csv文件在记录之前没有空字段，那么这非常有用。 
 / *将1设为是，0设为否。注意：如果您不确定，请不要设置为1。 
 / *如果该额外字段不存在
 / *，则可以将数据转储到错误的字段中。 
 / ******************************** / 
 $ addauto = 0; 
 / ******************************** / 
 / *您想保存mysql查询文件中？如果是，则将$ save设置为1. 
 / *文件的权限应该设置为777.可以通过ftp上传示例文件
 / *并更改权限，或者在提示符下执行：
 / * touch output.sql&& chmod 777 output.sql 
 / ******************************** / 
 $ save = 0; 
 $ outputfile =output.sql; 
 / ******************************** / 
 
 if（！file_exists （$ csvfile））{
 echo找不到文件，确保你指定了正确的路径。 
出口; 
} 
 
 $ file = fopen（$ csvfile，r）; 
 if（！$ file）{
 echo打开数据文件时出错。\\\
; 
出口; 
} 
 
 $ size = filesize（$ csvfile）; 
 if（！$ size）{
 echoFile is empty.\\\
; 
出口; 
} 
 
 $ csvcontent = fread（$ file，$ size）; 
 fclose（$ file）; 
 
 $ con = @mysql_connect（$ dbhost，$ dbusername，$ dbpassword）或die（mysql_error（））; 
 @mysql_select_db（$ dbname）或者死（mysql_error（））; 
 
 $ lines = 0; 
 $ queries =; 
 $ linearray = array（）; 
 
 foreach（split（$ lineseparator，$ csvcontent）as $ line）{
 $ lines ++; 
 $ line = trim（$ line，\t）; 
 $ line = str_replace（\r，，$ line）; 
 
 / ************************************ 
 / *这行代码逃避特殊字符。 
 / *如果条目已经在csv文件中转义，则将其删除
 / **************************** ******** / 
 $ line = str_replace（'，\'，$ line）; 
 / ************************************* / 
 
 $ linearray = explode（$ fieldseparator，$ line）; 
 $ linemysql = implode（'，'，$ linearray）; 
 $ b $ if if（$ addauto）{
 $ query =insert into $ dbtable values（''，'$ linemysql'）;; 
} 
 else {
 $ query =insert into $ dbtable values（'$ linemysql'）;; 
} 
 $ queries。= $ query。 \\\
; 
 @mysql_query（$ query）; 
} 
 
 @mysql_close（$ con）; 
 if（$ save）{
 if（！is_writable（$ outputfile））{
 echoFile is not writable，check permissions.\\\
; 
} 
 else {
 $ file2 = fopen（$ outputfile，w）; 
 
 if（！$ file2）{
 echo写入输出文件时出错。\ n; 
} 
 else {
 fwrite（$ file2，$ queries）; 
 fclose（$ file2）; 
} 
} 
} 
 
 echo在此csv文件中找到总共$行记录。 
 
 $ source =DespGoods.csv; 
 $ target = fopen（DespGoods.csv，w）; 
 $ conn = ftp_connect（ftp.server）或死（无法连接）; 
 
 ftp_login（$ conn，ftpusername，ftppassword）; 
 ftp_delete（$ conn，$ source）; 
 ftp_close（$ conn）; 
 
回显文件已删除; 
 mysql_close（$ con）; 
 
？> 
  

I have the below script to import data from a csv file on my server, the script works correctly.

I need to change the location of the file from my server to an FTP server which requires authentication. The csv file name on the FTP server will cahnge according to the time stamp it was generated. After importing into the MySQL database, the file on the FTP server needs to be deleted.

I then need to schedule this job with cron jobs to run every 5 minutes.

Any assistance will be appreciated.

Thanks.

---

<?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";


?>

---

解决方案

I thought I would just post the code that solved my issue; it downloads a csv file from an external FTP site onto my local server. It then imports the csv file into my local mysql database table. Once imported the file on the ftp server is deleted. I schedule this job in cpanel with cronjob.

I cannot take credit for the script, I have just modified a script found on another site. I'm sure there are better ways of doing this with some error checking. However, this is the best I could do with my limited skills.

I hope this assists future visitors with the same issue. Thanks to all those that assisted me. Here is the code I used:

<?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);

?>

这篇关于从外部FTP站点导入csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！