Parse Tabbed File

[DaemonProjects]

Hello all,

I wanted to know or pointed to the right location on how to parse a tabbed file.

Thanks
DP 🙂

[DaemonProjects]

After reading about tabbed files with php I came up with this working code.

<table width="100%" border="0" cellspacing="3" cellpadding="6">
<?  

$readfile = file("files/Class4_MobileHome_v5.txt");
for ($k=0; $k<=count($readfile)-1; $k++) {
    $fields = split("\t",$readfile[$k]);
?>

  <tr>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[32]; ?></span></td>
    <td><span class="style3"><? echo $fields[33]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[34]; ?></span></td>
    <td><span class="style3"><? echo $fields[35]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[36]; ?></span></td>
    <td><span class="style3"><? echo $fields[37]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[38]; ?></span></td>
    <td><span class="style3"><? echo $fields[39]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[40]; ?></span></td>
    <td><span class="style3"><? echo $fields[41]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[42]; ?></span></td>
    <td><span class="style3"><? echo $fields[43]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[44]; ?></span></td>
    <td><span class="style3"><? echo $fields[45]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[46]; ?></span></td>
    <td><span class="style3"><? echo $fields[47]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[48]; ?></span></td>
    <td><span class="style3"><? echo $fields[49]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[50]; ?></span></td>
    <td><span class="style3"><? echo $fields[51]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[52]; ?></span></td>
    <td><span class="style3"><? echo $fields[53]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[54]; ?></span></td>
    <td><span class="style3"><? echo $fields[55]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[56]; ?></span></td>
    <td><span class="style3"><? echo $fields[57]; ?></span></td>
    <td bgcolor="#CCCCCC"><span class="style3"><? echo $fields[58]; ?></span></td>
    <td><span class="style3"><? echo $fields[59]; ?></span></td>
  </tr>


<?
}
?>
</table>

[DaemonProjects]

I am having trouble inserting the tabbed data into mysql.

<?
require('cfg.php'); 

// Database Connection String ------Start------
$connect = mysql_connect($hostname, $user_name, $user_pass) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
$db = mysql_select_db($database, $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");
// Database Connection String ------End------
$readfile = file("SDIDX/working/Class4_MobileHome_v5.txt");

for ($k=0; $k<=count($readfile)-1; $k++) {
    $fields = split("\t",$readfile[$k]);

$sql="INSERT INTO listings SET 
L_firstname='".$fields[0]."',
L_preferredphone='".$fields[1]."',
L_landuseaddl='".$fields[2]."',
L_restrictionsage='".$fields[3]."',
L_axles='".$fields[4]."',
L_bathstotal='".$fields[5]."',
L_boatfacility='".$fields[6]."',
L_beds='".$fields[7]."',
L_bed2dim='".$fields[8]."',
L_bed3dim='".$fields[9]."',
L_bed4dim='".$fields[10]."',
L_bed5dim='".$fields[11]."',
L_configuration='".$fields[12]."',
L_amenities='".$fields[13]."',
L_city='".$fields[14]."',
L_coolsystem='".$fields[15]."',
L_community='".$fields[16]."',
L_roomdiningdim='".$fields[17]."',
L_sqfttotal='".$fields[18]."',
L_siding='".$fields[19]."',
L_roomfamilydim='".$fields[20]."',
L_fence='".$fields[21]."',
L_floors='".$fields[22]."',
L_fireplacedesc='".$fields[23]."',
L_frontage='".$fields[24]."',
L_guesthouse='".$fields[25]."',
L_water='".$fields[26]."',
L_assocfee='".$fields[27]."',
L_assocfeeinclude='".$fields[28]."',
L_assocfeepaid='".$fields[29]."',
L_listpricehigh='".$fields[30]."',
L_streetnumdisplay='".$fields[31]."',
L_heatsystem='".$fields[32]."',
L_irrigation='".$fields[33]."',
L_roomkitchendim='".$fields[34]."',
L_agentlist='".$fields[35]."',
L_lotdim='".$fields[36]."',
L_laundry='".$fields[37]."',
L_length='".$fields[38]."',
L_uid='".$fields[39]."',
L_officelist='".$fields[40]."',
L_listpricelow='".$fields[41]."',
L_listprice='".$fields[42]."',
L_roomlivingdim='".$fields[43]."',
L_lotsize='".$fields[44]."',
L_roommasterbeddim='".$fields[45]."',
L_model='".$fields[46]."',
L_make='".$fields[47]."',
L_miscellaneous='".$fields[48]."',
L_zipcode='".$fields[49]."',
L_feeother='".$fields[50]."',
L_officename='".$fields[51]."',
L_phone='".$fields[52]."',
L_feeotherpay='".$fields[53]."',
L_pets='".$fields[54]."',
L_parking='".$fields[55]."',
L_pooldesc='".$fields[56]."',
L_parkname='".$fields[57]."',
L_patio='".$fields[58]."',
L_proptype='".$fields[59]."',
L_roof='".$fields[60]."',
L_site='".$fields[61]."',
L_spa='".$fields[62]."',
L_State='".$fields[63]."',
L_streetname='".$fields[64]."',
L_stories='".$fields[65]."',
L_subdivision='".$fields[66]."',
L_sewer='".$fields[67]."',
L_feeothertype='".$fields[68]."',
L_topography='".$fields[69]."',
L_variablerange='".$fields[70]."',
L_viewdesc='".$fields[71]."',
L_width='".$fields[72]."',
L_roomotherdim='".$fields[73]."',
L_yearbuilt='".$fields[74]."',
L_zoning='".$fields[75]."',
L_MediaSource='".$fields[76]."',
L_ListDate='".$fields[77]."',
L_Modified='".$fields[78]."',
L_PriPictByURL='".$fields[79]."',
L_spacenum='".$fields[80]."',
L_piccount='".$fields[81]."',
L_MapPage='".$fields[82]."',
L_MapColumn='".$fields[83]."',
L_MapRow='".$fields[84]."',
L_ListStatus='".$fields[85]."',
L_ClosedDate='".$fields[86]."',
L_SalesPrice='".$fields[87]."',
L_AgentSell='".$fields[88]."',
L_OfficeSell='".$fields[89]."',
L_Financing='".$fields[90]."',
L_Photo1='".$fields[91]."',
L_Photo2='".$fields[92]."',
L_Photo3='".$fields[93]."',
L_Photo4='".$fields[94]."',
L_Photo5='".$fields[95]."',
L_Photo6='".$fields[96]."',
L_Photo7='".$fields[97]."',
L_Photo8='".$fields[98]."',
L_Photo9='".$fields[99]."',
L_Photo10='".$fields[100]."',
L_Photo11='".$fields[101]."',
L_Photo12='".$fields[102]."',
L_type='mobile'"; 
mysql_query($sql, $connect) or die ("<b><center>".mysql_errno().": ".mysql_error()."</b></center>\n");

}
mysql_close($connect);
?>

Error message I receive.

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'l', L_make='Redman', L_miscellaneous='N', L_zipcode='92078', L_feeother=' at line 48

any ideas?

Thanks,
DP

[bradgrafelman]

I would recommend echo'ing out your query to visually inspect it. Then, if you still can't spot the error, paste it here for us to examine.

[DaemonProjects]

INSERT INTO listings SET L_firstname='George J. Corrigan', L_preferredphone='[phone]', L_landuseaddl='N', L_restrictionsage='A', L_axles='N', L_bathstotal='2', L_boatfacility='N', L_beds='2', L_bed2dim='10X10', L_bed3dim='0', L_bed4dim='0', L_bed5dim='0', L_configuration='H,I,B,J', L_amenities='A,B,I,J,K,M', L_city='San Marcos', L_coolsystem='Z', L_community='SMS', L_roomdiningdim='9X13', L_sqfttotal='1420', L_siding='Z', L_roomfamilydim='0', L_fence='N', L_floors='Z', L_fireplacedesc='N', L_frontage='H', L_guesthouse='N', L_water='N', L_assocfee='202', L_assocfeeinclude='A,B,I,J,K', L_assocfeepaid='A', L_listpricehigh='299000', L_streetnumdisplay='1195', L_heatsystem='F', L_irrigation='D', L_roomkitchendim='11X13', L_agentlist='601553', L_lotdim='52x70', L_laundry='G', L_length='53', L_uid='076078696', L_officelist='62977', L_listpricelow='299000', L_listprice='299000', L_roomlivingdim='13X21', L_lotsize='A', L_roommasterbeddim='13X15', L_model='Trad'l', L_make='Redman', L_miscellaneous='N', L_zipcode='92078', L_feeother='0', L_officename='Alltech Real Estate', L_phone='[phone]', L_feeotherpay='N', L_pets='B', L_parking='I', L_pooldesc='C', L_parkname='Casitas Del Amigos', L_patio='F', L_proptype='mh', L_roof='A', L_site='I', L_spa='A', L_State='CA', L_streetname='La Moree #41', L_stories='A', L_subdivision='Casitas Del Amigos', L_sewer='F', L_feeothertype='N', L_topography='E', L_variablerange='N', L_viewdesc='G', L_width='27', L_roomotherdim='9X14', L_yearbuilt='2006', L_zoning='MH', L_MediaSource='', L_ListDate='10/03/2007', L_Modified='10/31/2007', L_PriPictByURL='1000579060-1.jpg', L_spacenum='41', L_piccount='4', L_MapPage='1129', L_MapColumn='B', L_MapRow='1', L_ListStatus='act', L_ClosedDate='', L_SalesPrice='', L_AgentSell='', L_OfficeSell='', L_Financing='', L_Photo1='lr1000579060-1.jpg', L_Photo2='lr1000579060-4.jpg', L_Photo3='lr1000579060-3.jpg', L_Photo4='lr1000579060-2.jpg ', L_Photo5='', L_Photo6='', L_Photo7='', L_Photo8='', L_Photo9='', L_Photo10='', L_Photo11='', L_Photo12='', L_type='mobile'
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'l', L_make='Redman', L_miscellaneous='N', L_zipcode='92078', L_feeother=' at line 48

[DaemonProjects]

its the data im parsing. looks like there are some ' hrs in there. Going to strip them.

[bradgrafelman]

Well, regardless of what you do, you should be sanitizing all of the data with a function such as [man]mysql_real_escape_string/man so that it doesn't break your query (such as it did now).