crlf causes problems in array functions

[Vic Spainhower]

Hello,

I am exporting a table from MS Access which is then imported into a MySQL
table. I am using a TransferText command in Access to export the data to a
TAB delimeted file. Then using the following PHP routine, it is imported
into the MySQL table. One of the fields in the Access data can contain
imbedded CRLF which causes the following code to fail with the error "Column
count doesn't match value count at row 1"

If the last field in the data file doesn't contain an imbedded CRLF the code
works properly. I've tried stripping the CRLF with $line =
trim($fcontents[$i],"\r\n"); but that doesn't work.

Can someone tell me why this doesn't work?

Thanks,

Vic


$fcontents = file ('./'.$facilitiesNa me);
for($i=0; $i<sizeof($fcon tents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode("\t", $line);
$sql = "insert into $tableFacilitie s values ('".
implode("','", $arr) ."')";
mysql_query($sq l);
//echo $sql ."<br>\n";
if(mysql_error( )) {
echo mysql_error() ."<br>\n";
}
}

[Pedro Graca]

Re: crlf causes problems in array functions

Vic Spainhower wrote:

I am exporting a table from MS Access which is then imported into a MySQL
table. I am using a TransferText command in Access to export the data to a
TAB delimeted file. Then using the following PHP routine, it is imported
into the MySQL table. One of the fields in the Access data can contain
imbedded CRLF which causes the following code to fail with the error "Column
count doesn't match value count at row 1"
>
If the last field in the data file doesn't contain an imbedded CRLF the code
works properly. I've tried stripping the CRLF with $line =
trim($fcontents[$i],"\r\n"); but that doesn't work.
>
Can someone tell me why this doesn't work?

<snip>

To insert a line break into a MySQL column you need to specify it as

'line 1\nline 2'

Your $fcontents array will *NOT* be ready to be imported to MySQL even
after exploding on tabs. If your file contains these two lines (one
with an embedded line break)

1[TAB]"Pedro[ENTER]Graca"[TAB]"Portugal"
2[TAB]"Vic Speinhower"[TAB]"Austria"

$fcontents will have *three* elements

1[TAB]"Pedro[ENTER]
Graca"[TAB]"Portugal"[ENTER]
2[TAB]"Vic Speinhower"[TAB]"Austria"[ENTER]

and your script will try to make *three* INSERTs.
You need to either reformat your Excel's TransferText or deal with the
file differently in PHP.