PHP MySQL 从固定宽度的文本文件插入到表中

Question

我正在尝试编写一个cron作业，该作业将运行一个php脚本，该脚本将从具有固定列的平面（文本）文件中插入数据。

我的文件"data.txt"看起来像这样：

first_column                   second_column        third_column   

例如，第一列的宽度为 30 个字符 + 1 个空格用于与下一列分隔，第二列的宽度为 20 个字符 + 1 个分隔空格，第三列的宽度为 15 个（包括空格）。我的表格"TEST"有 3 列：第一、第二和第三列。

问题是，如何首先修剪列数据，然后将每一行插入表中？

<?php
// initial database stuff
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$file = file('/home/user/files/data.txt'); # read file into array
$count = count($file);
if($count > 0) # file is not empty
{
    $query = "INSERT into TEST(first,second,third) values";
    $i = 1;
    foreach($file as $row)
    {
        $query .= "('TRIM(SUBSTR($row,1,30))','TRIM(SUBSTR($row,32,20))','TRIM(SUBSTR($row,34,49))')";
        $query .= $i < $count ? ',':'';
        $i++;
    }
mysql_query($query) or die(mysql_error());
}
echo "File data successfully imported to database!!";
?>

Answer 1

下面是它的外观：

<?php
// initial database stuff
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$file = file('/home/user/files/data.txt'); # read file into array
$count = count($file);
// Edited to add loop back in... Silly me.
if($count > 0) # file is not empty
{
    foreach ($file as $row){
        $first=trim(substr($row,0,30));
        $second=trim(substr($row,31,20));
        $third=trim(substr($row,33,49));
        $query = "INSERT into TEST(first,second,third) values".
                 "($first,$second,$third)";
        mysql_query($query) or die(mysql_error());
    }
}
echo "File data successfully imported to database!!";
?>

考虑使用 PDO 和预处理语句，使其更干净（更安全）。

Answer 2

试试这个

<?php
// initial database stuff
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$file = file('/home/user/files/data.txt'); # read file into array
$count = count($file);
if($count > 0) # file is not empty
{
foreach($file as $key => $row)
{
    if( $key == 0 ){
        $col_1 = str_replace( " ", "" , trim(substr($row,1,30));
        $col_2 = str_replace( " ", "" , trim(substr($row,32,20));
        $col_3 = str_replace( " ", "" , trim(substr($row,34,49));
        $query = "INSERT into TEST(col_1,col_2,col_3) values";
    }
    else {
       $query .= "'TRIM(SUBSTR($row,1,30))','TRIM(SUBSTR($row,32,20))','TRIM(SUBSTR($row,34,49))')";
       $query .= $key < $count ? ',':'';
    }
}
mysql_query($query) or die(mysql_error());
}
echo "File data successfully imported to database!!";
?>

Answer 3

最后，我最终按照建议使用了PDO。它适用于中间带有逗号的字符串，并且可以跳过空行。

<?php
// configuration
$dbtype = 'sqlite';
$dbhost = 'localhost';
$dbuser = 'user_for_db';
$dbpass = 'pass_for_db';
$dbname = 'name_of_db';
// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8",$dbuser,$dbpass);  //charset optional
// open data file
$handle = fopen('/home/user/data/file.txt', 'r');
if ($handle) 
    {
    while (($buffer = fgets($handle, 4096)) !== false)
        {
        // new data
        $first      =trim(substr($buffer,0,8));
        $second     =trim(substr($buffer,9,10));
        $third      =trim(substr($buffer,20,6));
        $fourth     =trim(substr($buffer,27,100));
        $fifth      =str_replace(" , ", ", ", trim(substr($buffer,128,113)));
        $sixth      =trim(substr($buffer,240,30));
        // query
        $sql = "INSERT INTO table(column_1,column_2,column_3,column_4,column_5,column_6) VALUES (:first,:second,:third,:fourth,:fifth,:sixth)";
        $q = $conn->prepare($sql);
        $q->execute(array(  ':first'    =>$first,
                            ':second'   =>$second,
                            ':third'    =>$third,
                            ':fourth'   =>$fourth,
                            ':fifth'    =>$fifth,
                            ':sixth'    =>$sixth    ));
        }
        if (!feof($handle))
        {
        echo "Error: unexpected fgets() failn";
        }
    fclose($handle);
    echo "File data successfully imported to database!";
}
?>