Parse Comma Delimited File in PHP then Add to DB

This may be a toughie so you get bonus points!

THIS IS A .TXT FILE NOT CSV

I am trying to parse the following comma delimited file into a readable table, and then import everything into my table. Here's the info I got:

Here's the "template" of what I am importing from. (This is the first line in the file):

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

Here's a sample of what someone would normally enter:

    "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","","",""

Here's the fields I have in the database (MySQL): (obviously without the dollar signs.)

        $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

Here's what I need them mapped to.. (My field on right, the files on the left)

                   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

What I have right now (that really doesn't work) is below. It shows the data in a big jumble.

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


?>

---

$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 
}

---

For reading in the CSV file (it is one, regardless of .txt extension) you can use this snippet instead of the cumbersome manual reading and splitting:

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

Whatsmore, you might want to investigate LOAD DATA INFILE, as MySQL can often import such data structures directly.

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

(You have to take care with matching the occurence in the file to the right database column names!)

---

For parsing csv data files using php, see http://php.net/manual/en/function.fgetcsv.php