parsing csv file

[AllenGarner]

I am trying to Upload a csv file then split it up into an an array and build a query to insert it into a mysql table I have succeeded in getting this to work with one exception when I come across a field seperated by commas and enclosed by double quotes if that field also contains double quotes inside of it it throws off my column count I need to be able to consider something like - ,"sometext"some more text"additional text", as one field or value here is what i have so far

$row = 1; 
$handle = fopen($uploadFile, "r"); 
while ( ($data = fgetcsv($handle, 10000, ","))!= FALSE ) { 
$num = count($data); 
if($row == 1){
$fields = " `id` , ";
for($i=0; $i<$num; $i++){
$fields .= "`" . addslashes(trim($data[$i])) . "` , ";
}
$fields .= "`group` , `user` , "; 
$f = substr($fields, 0, -1);
$f = substr($f, 0, -1);
$row++;
}else{
$values = " '' , "; 
for($i=0; $i<$num; $i++){ 
$values .= "'" . addslashes(trim($data[$i])) . "' , ";
}
$values .= "'personal' , '" . $_SESSION['user'] . "' , ";
$v = substr($values, 0, -1);
$v = substr($v, 0, -1); 
$sql = "INSERT INTO `addressbook` ( $f ) VALUES ( $v )";
mysql_query($sql) or die(print "<center><font size='2' face='verdana' color='#5E6B79'><b>Upload complete some entries may not have been loaded successfully!</b><br><br><a href='addressbook.php' class='light'>Back to Address Book</a></font></center></td></tr></table>");
$values = ''; 
$row++;
} 
}
fclose ($handle);

can anyone help? thanx

[dmorison]

Hi Allen,

There is a comment on the manual page for fgetcsv that recommends trying the File_CSV [pear.php.net]PEAR class - so you might want to give that a go.

Strictly speaking, any quote character that apears within a quote qualified string should be escaped; but the real problem is that there are simply no formal specifications of how a CSV file should handle such anomolies.

This is a common problem in the world of affiliate datafeeds - many are created with little care and attention; the contents of a database are simply written out to a text file without thinking about it. The same problem occurs when what you could loosly describe as an "XML" feed contains HTML markup that has just been written into the feed willy nilly!

[AllenGarner]

Unfortunately the pear class specifically specifies that it does not handle the quotes within quotes scenerio

[AllenGarner]

FYI the csv file is exported via outlook express

[ergophobe]

Is there any way you could escape the quotes on, before or just after export? I'm sort of surprised that Outlook doesn't do this.

Alternatively, is it possible to use a different delimiter on export that is not in your actual data? For example, you could use a pipe, then you would just do a search and replace to change all " to \" Then you would replace all pipes with quotes and be good to go.

[jatar_k]

you could also do replaces

if you find this "," then the quote shouldn't be replaced

but if you find " by itself then it needs to be \"

using str_replace or one of the preg functions

[ergophobe]

Though unlikely, that might still depend on his data.

The following words are French: "jeune","chat","chien"

becomes

"The following words are French: "jeune","chat","chien""

[jatar_k]

true, you would have to add "\n to the list of do not replace

[AllenGarner]

Duh Thanks guys that helps so simple

[ergophobe]

I actually have similar things come up quite often and usually find that a combination of replaces can handle a lot of different circumstances.