I’ve been doing a bit of work that involves pulling records from a database, placing them in a .csv file and emailing them through to my client on a daily basis.
(Who knows, I might even get around to posting the script up here for the enjoyment of all one day 
)
For now, we’ll share the relevant snippets so that this fix makes sense:
$out = ‘”Title”,”First Name”,”Last Name”,”Address 1″,”Address 2″,”Address 3″,”City”,”State”,”Postal Code”, “Email Address”,”Contact Number”,’;
$out .= “\n”;
The above code is used to generate the relevant lines of the .csv file (in this case it’s generating headers).
Each comma separated item is wrapped in doulbe quotes - the reason? Because we’ve got names in there, and I didn’t want Scarlet O’Hara breaking my PHP with the apostrophe in her surname!
Finally, we have ‘\n’ as a line break for the end of the line.
Now, when I generated my .csv file, and opened it up in Scalc (OpenOffice.org’s Excel equivalent - same problem happens in Excel, I assure you) although most of the fields displayed absolutely fine (i.e. just the content, without the double quotes wrapping them), some fields were still wrapped in double quotes.
Now, this poses a problem for my client, who uses this data in a mail merge to snail mail a lot of publicity to his customers. Dear Miss “O’Hara”, looks a bit strange at the top of a letter! So, we had to work out the solution.
As it happens, the fix is not a fix to a bug or a problem, but merely a resolution to my own occasionally sloppy coding*. In this case, the solution is staring you in the face in the above code snippet - a round of applause for those who spotted it right away.
You see, if you follow a comma with a space when generating a .csv, then this leads to the double quotes being displayed when you open the csv file up. In the above example, there is a space before the email address header, meaning that the email address column in the spreadsheet was called: “Email Address” complete with quotes.
The correct code should be:
$out = ‘”Title”,”First Name”,”Last Name”,”Address 1″,”Address 2″,”Address 3″,”City”,”State”,”Postal Code”,”Email Address”,”Contact Number”,’;
$out .= “\n”;
Amazing what an impact these subtle differences have!
* In my defence, rather than own up to sloppy coding, I’m going to defend myself by saying that Mrs Hughes always taught me that a comma is followed by a single space, a full stop by two spaces, in computer class when I was 15. Some things, you learn just a little too well!
0 Responses to “Fix: remove unwanted quote marks from PHP generated .csv file”