I'm curious how to remove a comma (or any other character) from the middle of a string. I have a single form field that asks for someone's full name. Later this name is used in a .csv file that will be opened in Excel. If the user enters the name "First Last", it's fine. However, if the user enters "First Last, Jr" it adds another column when the .csv file is opened in Excel.

Any guidance would be greatly appreciated.

Thanks in advance!

    Depending on the exact requirements, there are several functions that can help. [man]str_replace/man is one of them.

      If you correctly create your CSV file, the comma will not matter. Simply wrap the text for that field in double quotes, and you'll be OK:

      field1,field2,"Charles Reace, Jr.",field4

      If a CSV field contains double quotes as data, then the field must be quoted and the double quotes within it must be doubled up:

      field1,"This field has ""quotes"" in it","This field has, ""quotes and commas,"" in it"

      A simple PHP function to deal with this:

      function quoteCsvField($value)
{
   if(preg_match('/[",]/', $value))
   {
      $value = '"' . str_replace('"', '""', $value) . '"';
   }
   return($value);
}

      Or, if you're using PHP 5, you can use the [man]fputcsv/man function, which automatically quotes all fields and doubles up double-quotes in the content.

        Thanks for the responses!

        I tried playing with things a little bit and I'm unsure of the proper way to take care of this. I'm not sure if I should use str_replace() or if I can clean up my csv file formatting a bit. I tried adding an additional set of quotes around the particular value that I am having troubles with, but it didn't work.

        Here is the code I am using to create the .csv file. Again, I'm trying to eliminate any comma's possibly being entered by the user creating a new column when the .csv file is opening in php. Specifically, the value from $select['name'].

        Any guidance would be greatly appreciated. Thanks!

        <?php

$selectSql = "SELECT * FROM purchase";
$selects = $db->getAll($selectSql);

foreach($selects as $select) {

$content .= $select['date'].",".$select['loanType'].",".$select['homeDescription'].",".$select['creditProfile'].",".$select['state'].",".$select['purchasePrice'].",".$select['downPayment'].",".$select['name'].",".$select['email'].",".$select['phone']."\n";

}

Header("Content-Disposition: attachment; filename=purchase.csv");
print $content;
?>
          mpar612 wrote:

          I tried adding an additional set of quotes around the particular value that I am having troubles with, but it didn't work.

          Are you sure? There are no quotes being outputted in the code you showed us above.

            I'm positive I reverted back to my original code to post.

              So can we see how you attempted to add double quotes around the values? As NogDog pointed out, this should solve any problems with commas in a CSV.

                Write a Reply...