crlf causes problems in array functions

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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?



  $fcontents = file ('./'.$facilitiesName);
  for($i=0; $i<sizeof($fcontents); $i++) {
      $line = trim($fcontents[$i]);
      $arr = explode("\t", $line);
      $sql = "insert into $tableFacilities values ('".
                  implode("','", $arr) ."')";
      //echo $sql ."<br>\n";
      if(mysql_error()) {
         echo mysql_error() ."<br>\n";

Re: crlf causes problems in array functions

Vic Spainhower wrote:
Quoted text here. Click to load it

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)

    2[TAB]"Vic Speinhower"[TAB]"Austria"

$fcontents will have *three* elements

    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.

Site Timeline