[RESOLVED] trap for unprintable char Excel upload, break out of while loop

[jrough]

I wrote a php script to upload csv files and do mysql INSERTS.
I have tested my program on 4 or 5 files.

All of them upload except one. I looked at
the file and don't see any stray commas or apostrophe's in the cells.
I think there may be some unprintable control characters.

Does someone have a regular expression I could use to trap for those?
What would the characters be I am looking for?
I was trying to figure out what I'm trapping for.
I looked at this ascii characters page.
http://www.w3schools.com/tags/ref_ascii.asp
I'm guessing
line feed

carriage return

line feed

Or would I be looking for Excel characters like?
verticle tab
If you know one specific to Excel csv's it would be helpful we have a lot of uploading to do.

[bradgrafelman]

How are you inserting the data? Are you using the appropriate escaping mechanism for your database, e.g. [man]mysql_real_escape_string/man ?

Also, what is the error message returned by the SQL server for the one that doesn't work?

[jrough]

This is the SQL error:
Invalid query:Column count doesn't match value count at row 1
INSERT INTO('myvaluex', 'myvaluey',... )

I got this error on the other mistakes which were a comma in the text of one of the columns
and apostrophe's in another but I fixed it using fgetcsv() function but I don't know what this error is. I tried escaping sData
and $value and I got slashes in all the values. and it stopped in the first 100 or so records.
There are approx 3000 records in the csv file.

foreach ($res as $key =>   $value  {
	     	      	$sData.=",'" . str_replace("'","''",$value) . "'";

         	}
         	$sData=substr($sData,1);
         	$sql="INSERT INTO inventory(control_id, bu, cart_id, $hdr) VALUES ($controlID, '$bu', '$cart_id',$sData;";
            echo "$sql<br />";

thanks,

[NogDog]

If you are using MySQL, I believe you'd want something like:

foreach ($res as $key =>   $value  {
                       $sData.=",'" . mysql_real_escape_string($value) . "'";

             }

[jrough]

I can't see what it is but I get a syntax error when I change this line to what you said:
sData.=",'" . mysql_real_escape_string($value) . "'";
when I run the upload script it stops on the first INSERT.

[jrough]

it may be that the error is not in this line but with taking out the csv ","s in the middle of the csv cell. When that is removed this line can't process. If that is the case I may have to figure out how to take that out with fgetcsv ?

[bradgrafelman]

Post a fresh copy of this section of the code after you tried implementing NogDog's solution.

[jrough]

Okay, here is the whole file handle. It works aon all csv files except one unexplicably.
I did not use mysql_real_escape_string because is an internal conversion and all files are internal excel spreadsheets. I used csv format to upload so I had to trap for commas and apostrophe's in the cells. I did try the commented out line using mysql_real_escape_string.
I got this error:
rror: syntax error, unexpected T_CONCAT_EQUAL in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\tools\cart_inventory_form\uploader4.php on line 66
Line 66 is the mysql_real_escape_string line.

$fh = fopen($target_path, 'r+') or die("Can't open file");
//$filesize=filesize($target_path);
       $i=0;
       while(!feof($fh))  {
             $res = fgetcsv($fh, "," );
             $sData='';
             if ( (empty($res)) ) { break; }

         if ( $i==0 ) {

            foreach ($res as $key => $value)  {
     		$hdr.=",$value";

         	}
            $hdr=substr($hdr,1);

         }else{
         //  for ( $t=0;$t>count($res);$t++) {
            foreach ($res as $key =>   $value)  {
     	  //   	$sData.=",'" . str_replace("'","''",$value) . "'";
            sData.=",'" . mysql_real_escape_string($value) . "'";

         	}
         	$sData=substr($sData,1);
         	$sql="INSERT INTO cart_order.inventory(control_id, bu, cart_id, $hdr) VALUES ($controlID, '$bu', '$cart_id',$sData)";
            echo "$sql<br />";
           // echo "<b>" . $sData . "</b><br>";

           $result=mysql_query($sql);
           if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br>$sql");}

         }
     $i++;
   }
    //        $aLines[$i]=$line;
    //        $i++;
//}
fclose($fh);

THANKS!

[bradgrafelman]

jrough wrote:

I did not use mysql_real_escape_string because is an internal conversion and all files are internal excel spreadsheets.

So? All data used in SQL queries must be properly and systematically escaped, else you should expect problems.

jrough wrote:

I did try the commented out line using mysql_real_escape_string.

The line you have now is missing a $ before the variable name.

[jrough]

thanks so much. I didn't see that. I don't know how but I didn't.:rolleyes::rolleyes:🙂
I have one other question since we are in this file handle.

I had to put the break in or it wouldn't break out of the while at the end of the file.
Do you know why? The break seems like it is in the wrong place but it works.

while(!feof($fh)) {
$res = fgetcsv($fh, "," );
$sData='';
if ( (empty($res)) ) { break; }

[bradgrafelman]

Get rid of the call to feof() altogether, as you don't need it:

PHP Manual wrote:

fgetcsv() returns NULL if an invalid handle is supplied or FALSE on other errors, including end of file.

The manual page for [man]fgetcsv/man shows a code example where fgetcsv() is used in the while() condition.

[jrough]

many happy returns :-)
it seems to work without it, I assume you meant to use the while($fh){
}
without the feof
i'm trying to do this perfect since it is a large operation.
I will mark this one closed.

[bradgrafelman]

jrough wrote:

I assume you meant to use the while($fh){
}
without the feof

No, I meant moving the call to fgetcsv() into the condition of the while loop (as shown in the coding example from the manual).

[jrough]

thanks alot for clearing that up.