ereg_replace on a csv file upload?

[ejwf]

Is this possible?
I want to strip some character out of a csv file prior to adding the data to mysql database.
when users have input client data they have used ' ` and other characters that are mucking up the data insert into the database.
I want to strip these out - can I use ereg_replace ?

I have tried the following code but I get the error message 'Warning: fopen(Client csv): failed to open stream: No such file or directory in /home///htdocs///upload/client_upload.php on line 26
could not open file!' - line 26 is $fp = fopen($filename,"rb") or die('could not open file!');

require_once ('../mysql_connect.php');//connect to the database

$uploadDir = '/home/***/***/htdocs/***/***/upload/';
$uploadFile = $uploadDir . $_FILES['ClientCsvFile']['name'];
print "<pre>";

if (move_uploaded_file($_FILES['ClientCsvFile']['tmp_name'], $uploadFile))
{ echo '<div align="center">
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p><b><font color="#FF0000" face="Arial, Helvetica, sans-serif" size="4">Thank You, <br> The new Client Data has been added to the database</font></b></p>
</div>';   

}
else
{
    print "Not Possible To Upload Data! Please Try Again";
}
print "</pre>";

$filename = "Client.csv"; 
$filename = ereg_replace( "(([`\'/\\.]))", " ", $filename);
$fp = fopen($filename,"rb") or die('could not open file!'); 

while (!feof($fp)){ 

$data = fgetcsv($fp,100000000); 

$ClientId = $data[0]; 
$ConcesRef = $data[1]; 
$CoName = $data[2]; 
$Addr1 = $data[3]; 
$Addr2 = $data[4]; 
$Addr3 = $data[5]; 
$Town = $data[6]; 
$County = $data[7]; 
$Postcode = $data[8]; 
$Phone = $data[9]; 
$Fax = $data[10]; 

$sql = "REPLACE INTO tblClient (ClientId, ConcesRef, CoName, Addr1, Addr2, Addr3, Town, County, Postcode, Phone, Fax) 
VALUES ('$ClientId', '$ConcesRef', '$CoName', '$Addr1', '$Addr2', '$Addr3', '$Town', '$County', '$Postcode', '$Phone', '$Fax')";
$result = mysql_query($sql) 
 or die(mysql_error());
   }
  

what should I do?

[devinemke]

first: the error you are getting says that the file you are trying to open does not exist. I assume you are trying to open the uploaded file, which may or may not be called "Client.csv".

second: you are running just the filename thru eregi_replace() not the contents of the file. anyway, you can leave those characters in the data if you properly escape them using addslashes().

I suggest this:

$fp = fopen($uploadDir . $uploadFile, 'rb') or die('could not open file!');

$fields = array(
	'ClientId',
	'ConcesRef',
	'CoName',
	'Addr1',
	'Addr2',
	'Addr3',
	'Town',
	'County',
	'Postcode',
	'Phone',
	'Fax'
);

while (($data = fgetcsv($fp, 100000000)) !== false)
{
	foreach ($fields as $key => $value)
	{
		$sql_array[] = $value . " = '" . addslashes($data[$key]) . "'";
	}
	$sql = 'REPLACE INTO tblClient SET ' . implode(', ', $sql_array);	
	$result = mysql_query($sql) or die(mysql_error());
}

[ejwf]

thanks for this, however it isn't quite working.
When I add this and run the code I only get the first line of data added to the mysql database and the error message 'Column 'Fax' specified twice' ie. whichever item is last in the array.
Any ideas ?
TIA

[devinemke]

does the number of fields in the uploaded CSV match your data structure? also, echo out the SQL is see what it looks like.

[ejwf]

thanks
the number of field and the field names are exactly the same as the database structure.

I have echo'd out the sql and it shows all 62 records as they should be, but this data isn't getting into the database.

I have had a go through mysqladmin, just to see what kind of code I should be looking at.

If I run it through this it adds all the data OK and gives the following SQL code

LOAD DATA INFILE '/tmp/php***' REPLACE INTO TABLE tblClient
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

I am thinking that perhaps with the addslashes we have put into the php, an additional slash might be signalling the end of the data after the first line (?), so I am trying to add some code to qualify exactly what we mean - like FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' -
but I am not sure where to put this or how the phrase it correctly - I have tried a couple of ways but keep getting a parse error - do you have any ideas?

TIA