Quote enclosed data probs in import of csv file

Mandakins

I've got a script that allows the client to import a csv file into a database table. Everything works great - unless they have a field with a comma in it. In this case the data, instead of having 20,000 in the proper field, has "20 in one field and 000" bumped into the next field. How do I get that data to stay together when my delimeter is another comma, but not print the quotation marks?

<?
$linecount = 0;
$cust_id = "\0";

// If there were no upload errors, then continue
if (!$FILES['infile']['error']) {
$filename = $FILES['infile']['tmp_name'];
}

// Make sure we're getting a csv file
if (!check_type($_FILES['infile']['name'])) { die("Filetype must be CSV.<br>"); }

// Open our new file from the temporary upload folder, or quit
// if something's wrong
$file = fopen($filename, 'r') or die("Can't open $filename.");

// Looping until end of file
while (!feof($file)) {

// These files use UNIX style newlines, so normal fgets() won't parse them right.
// I'm using this custom function instead
$buffer = getline($file, "\r");
$linecount++;

// Escape ' and '' so as to not threaten the INSERT statement's fields
$buffer = str_replace("'", "\'", $buffer);
$buffer = str_replace('"', "\"", $buffer);

// Data is comma delimited, so explode it into an array
$field = split(',', $buffer);

// Insert record, only if the jobid field is not blank
if (strlen($field[0]) > 0) { insert_record($field, $cust_id); }

}
fclose($file);

///////////////////////////////////////////////////////////
// Begin function definitions
///////////////////////////////////////////////////////////

// I got this function from comments on fgets() on php.net
function getline( $fp, $delim )
{
$result = "";
while( !feof( $fp ) )
{
$tmp = fgetc( $fp );
if( $tmp == $delim )
return $result;
$result .= $tmp;
}
return $result;
}

// Quick check to make sure the file extension is "csv"
function check_type ($filename) {
$parts = split('.', $filename);

return (strtolower($parts[count($parts) - 1]) == 'csv')?true:false;

}

// Inserts one record into the 'reports' table
function insert_record($field, $customer_id = "\0") {

// Server information taken directly from your table description

// (Connection Info is here )

// Assuming all the fields in the CSV file are in the same order as
// the MySQL database, this should insert them all.
mysql_query("INSERT INTO
		reports
	     VALUES

	     ('$field[0]',
	      '$field[1]',
	      '$field[2]',
	      '$field[3]',
	      '$field[4]',
	      '$field[5]',
	      '$field[6]',
	      '$field[7]',
	      '$field[8]',
	      '$field[9]',
	      '$field[10]',
	      '$field[11]',
	      '$field[12]',
	      '$field[13]',
	      '$field[14]',
	      '$field[15]',
	      '$field[16]',
	      '$field[17]',
	      '$field[18]',
	      '$field[19]',
	      '$field[20]',
	      '$field[21]',
	      '$field[22]',
	      '$field[23]',
	      '$field[24]',
	      '$field[25]',
	      '$field[26]',
	      '$field[27]',
	      '$field[28]',
	      '$field[29]',
	      '$field[30]',
	      '$field[31]',
	      '$field[32]',
	      '$field[33]',
	      '$field[34]',
	      '$field[35]',
	      '$field[36]',
	      '$customer_id',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      '',
	      'HPC',
	      'Total Coliforms',
	      'Fecal Coliforms',
	      'TPC',
	      'Coliform Count',
	      'E. coli Count',
	      'S. aureus Count',
	      'Mold Count',
	      'Yeast Count',
	      'B. cereus Count',
	      'Salmonella',
	      'Listeria',
	      'E. coli',
	      'Campylobacter',
	      '% Protein',
	      '% Fat',
	      '% Moisture',
	      '% Ash',
	      'Peroxide Value',
	      '% Sodium',
	      '% Salt',
	      'PCB/CHC',
	      'Aflatoxin',
	      'T2',
	      'Vomitoxin',
	      'Sacox',
	      'Flash Point',
	      'Total Organic Carbon',
	      'pH',
	      '')", $connection);

}

bigswifty

load data infile 'myfile.csv' into table (your table) fields terminated by ',' enclosed by '"'

make sure all field data is double quote enclosed and make sure to tell the db that the imported data will have enclosed data sets.
This will prevent the commas from breaking rows.

then its a simple matter to do a strip or string replace to eliminate the double quotes from the output when you do a SELECT.

or you can pre-parse the csv files and replace all delimiters with another character such as ~ or |... although you will just be doing the same thing more or less as above, just more work.

I know the first statement does not apply specifically to you, but it may help to understand that the old saying "garbage in = garbage out".

You must enforce conformity in your data or you will always be fighting this. all your csvs must(should) have all fields enclosed by double quotes. excel for instance will output files with all data enclosed, not just the lines that have a delimiter character present.
In short you cant have it both ways, enclose everything or nothing, also since you are using php those random double quotes will play hell with your scripting also... nip it in the bud, enforce conformity or you'll always be playing catchup

Mandakins

The client is saving their Excel file as a .csv and uploading it. The resulting page is the code I pasted in my original message. I will not have control over what the client uploads unless I tell them specifically what to do to prepare their file. Also, I like to make things like this as 'idiot proof' as possible. Everything is working perfectly except for the comma issue - and all that is doing is knocking the data for that row off by one field after the comma. All subsequent rows go in fine.

Are you saying that Excel will automatically enclose all the fields in quotes? If so, why are the quotes only showing for the fields that contain a comma?

Based on the existing code, where do I add the "enclosed by" part? If I were doing command line, I could use the 'load data infile' and it would be no problem, but this is code on a web page that is being hit after the client uploads the file.

If I had written this code myself I might understand it better, but at this point I am trying to get this 'glitch' fixed so the client can use it error-free.

Thanks. I appreciate you taking the time to help.