Solved

Need row output delimeter, encaps, escape correctly for csv

Posted on 2009-03-29
7
249 Views
Last Modified: 2012-05-06
I am outputting some data from mysql into csv

Some of the columns are misaligned which tells me that it needs to be encapsulated or delimited correctly

This is what i need for the output

Field Delimiter      , (comma)
Field Encapsulation      " (double quotes)
Escaped by \ (backslash)




$data = "$billing_first_name,$billing_last_name,$billing_address1,$billing_city,$billing_state,$billing_postal_code,$billing_email,$cc_number,$cc_expire" . "\r\n";

Open in new window

0
Comment
Question by:pdheady
  • 3
  • 3
7 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 24013474
This function may help:
http://us3.php.net/manual/en/function.fputcsv.php

Let me know if you try that and still have any questions, ~Ray
0
 
LVL 1

Author Comment

by:pdheady
ID: 24013503
Ya i was lookin at that function but i cant get it to work. PHP error log says i need the 2nd parameter ($data) into an array. How do I do this? Here is what I have so far.


$q = "my query here";
 
$fp = fopen('/path/to/my/file/file.csv','w');
 
$query = mysql_query($q);
 
while ($row = mysql_fetch_array($query)) {
 
$billing_first_name = $row["billing_first_name"];
$billing_last_name = $row["billing_last_name"];
$billing_address1 = $row["billing_address1"];
$billing_city = $row["billing_city"];
$billing_state = $row["billing_state"];
$billing_postal_code = $row["billing_postal_code"];
$billing_email = $row["billing_email"];
$cc_number = $row["cc_number"];
$cc_expire = $row["cc_expire"];
$billing_phone = $row["billing_phone"];
$billing_country = $row["billing_country"];
 
$data = "$billing_first_name,$billing_last_name,$billing_address1,$billing_city,$billing_state,$billing_postal_code,$billing_email,$cc_number,$cc_expire" . "\r\n";
 
fputcsv($fp, $data, $sSeparator=',', $sEnclosure='"');
 
}

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 24013570
I think you might want to just pass the $row variable to the fputcsv() function.  If you have the query set up right in $q, the fields will be ordered correctly in the resulting output.

HTH, ~Ray
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 1

Author Comment

by:pdheady
ID: 24013621
Ya, I did it this way tho below and it works. Looks like it is only encapsulating in double quotes when it needs to...is this how it's supposed to function?

Here's how i setup the array.

$data[1] = $orders_id;
$data[2] = $customers_id;
$data[3] = $billing_first_name;
$data[4] = $billing_last_name;
$data[5] = $billing_address1;

Example row output in csv, notice not all columns are encapsulated with double quotes. All records seem to be this way and encapsulation is sporadic.

1234,33455,John,Doe,"123 Main St",boston,ma,12345,test@example.net,1111222233334444,0110," 123-456-7890",US
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 total points
ID: 24013652
Yes, it will only encapsulate when it needs to.  If you need to encapse all of them, it would be a manual process in your script, and you could just put the vars together with the quotes and delimiters, ended by a newline character, then write with fputs() instead of fputcsv().
0
 
LVL 1

Author Comment

by:pdheady
ID: 24013749
cool thanks alot
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question