在 PHP 中解析逗号分隔文件然后添加到数据库

Question

这可能是一个硬汉，所以你会获得奖励积分！

这是一个 .TXT 文件，而不是 CSV

我正在尝试将以下逗号分隔文件解析为可读表格，然后将所有内容导入我的表格中。这是我得到的信息：

这是我从中导入的“模板”。 （这是文件中的第一行）：

    "Public/Private","Record Manager","Company","Contact","Address 1","Address 2","Address 3","City","State","Zip","Country","ID/Status","Phone","Fax","Home Phone","Mobile Phone","Pager","Salutation","Last Meeting","Last Reach","Last Attempt","Letter Date","Title","Assistant","Last Results","Referred By","User 1","User 2","User 3","User 4","User 5","User 6","User 7","User 8","User 9","User 10","User 11","User 12","User 13","User 14","User 15","Home Address 1","Home Address 2","Home City","Home State","Home Zip","Home Country","Alt Phone","2nd Contact","2nd Title","2nd Phone","3rd Contact","3rd Title","3rd Phone","First Name","Last Name","Phone Ext.","Fax Ext.","Alt Phone Ext.","2nd Phone Ext.","3rd Phone Ext.","Asst. Title","Asst. Phone","Asst. Phone Ext.","Department","Spouse","Record Creator","Owner","2nd Last Reach","3rd Last Reach","Web Site","Ticker Symbol","Create Date","Edit Date","Merge Date","E-mail Login","E-mail System"

这是人们通常输入的示例：

    "Public","John Doe”,"Einstein Construction","Bill Gates","3441 State Route 1","","","Somecity","CA","15212","","","724-555-2135","","","","","Jill","","4/18/2011","11/23/2009","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","Bill","Gregor","235","","","","","","","","","","Tom Jones","SuperMart","","","www.website.com","","11/14/2009","4/19/2011","","",""

这是我在数据库（MySQL）中的字段：（显然没有美元符号。）

        $rep 
        $date 
        $account 
        $areacode 
        $number 
        $address1 
        $address2 
        $city 
        $state 
        $zip 
        $country 
        $fax 
        $descmaker1 
        $descmaker2 
        $title 
        $email 
        $cvendor 
        $cequipment 
        $leaseexp1 
        $leaseexp2
        $leaseexp3 
        $leaseexp4 
        $leaseexp5
        $leaseexp6 
        $volume
        $notes  
                    $lastchange

这是我需要将它们映射到的内容。 （我的字段在右侧，文件在左侧）

                   Record Manager -> $rep 
(record manager i'd rather use our variable which is = $session->userinfo['fullname']
        Create Date -> $date (can the "/" be removed so its MMDDYYYY)
        Company -> $account 
        Phone (can you explode the areacode off?) -> $areacode 
        Phone (and put the number after the areacode here?)-> $number 
        Address 1 -> $address1 
        Address 2 -> $address2 
        City -> $city 
        State -> $state 
        Zip -> $zip 
        Country -> $country 
        Fax -> $fax 
        Salutation -> $descmaker1 
        $descmaker2 
        Title -> $title 
        Email Login -> $email 
                    Edit Date -> $lastchange

我现在拥有的（实际上不起作用）如下。它显示的数据非常混乱。

    <?php


$a = file_get_contents( "upload/contacts.txt" );
$a = str_replace( array( "\r\n", "\t") , array( "[NEW*LINE]" , "[tAbul*Ator]" ) , $a);

print "<table border=\"1\">";
foreach( explode( "[NEW*LINE]" , $a ) AS $lines){
echo "<tr>";
foreach( explode( "[tAbul*Ator]" , $lines ) AS $li ) {
echo "<td>";
echo $li ;
}
echo "</tr>";

}

echo "</table>";


?>

Answer 1

$file = fopen('mass-parse.txt', 'r');

while ($line = fgets($file)) {
    list(   $rep, $date, $account, $areacode, $number,
            $address1, $address2, $city, $state, $zip, $country,
            $fax, $descmaker1, $descmaker2, $title, $email,
            $cvendor, $cequipment, $leaseexp1, $le, $leaseexp3, $leaseexp4, $leaseexp5, $leaseexp6,
            $volume, $notes, $lastchange) = explode(',', str_replace('"','',$line));

    // do stuff with variables 
}

Answer 2

为了读取 CSV 文件（它是一个，无论 .txt 扩展名如何），您可以使用此代码片段，而不是繁琐的手动读取和拆分：

$csv = array_map("str_getcsv", file("upload/contacts.txt"));

更重要的是，您可能想要调查 LOAD DATA INFILE，因为MySQL通常可以直接导入此类数据结构。

LOAD DATA INFILE 'upload/contacts.txt'
INTO TABLE contacts
(rep, date, account, areacode, number, address1, city, ...)

（您必须注意将文件中的出现次数与正确的数据库列名称相匹配！）

Answer 3

有关使用 php 解析 csv 数据文件的信息，请参阅 http://php.net/manual/en/函数.fgetcsv.php