PHP strip quotes and line feeds out of array

I have code that pulls data from infusionsoft api and writes it to a csv file.  the issue is the data isn't all that clean in the database and some strings have double quotes surrounding the data.  Other strings have a line feed character in the data.  When it writes out to the csv and i try to bring it into excel these characters effect the data.  See code below.   what i want to do is after the data is read into the array, step through the array and remove the quotes and line feeds before it moves to the csv.  Can someone help me with this?  My code is below
do {
  if(in_array('Id', $object_fields)){
    $results = Infusionsoft_DataService::queryWithOrderBy($object, array('Id' => '%'), 'Id', true, 500, $page );
  } else {
    $results = Infusionsoft_DataService::queryWithOrderBy($object, array($object_fields[0] => '%'), $object_fields[0], true, 500, $page );
  }

  foreach($results as $result) {
    fputcsv($csv_file, $result->toArray(), "^", "\"");
  }
  $page++;
} while(count($results) > 0);

Open in new window

mitaiAsked:
Who is Participating?
 
Ray PaseurCommented:
I'm thinking something like this might be helpful.  Obviously I can't test it without having your database, but I think it's right in principle.
// THIS IS LINE 8 IN THE ORIGINAL CODE SNIPPET
foreach($results as $result) {
    $arr = $result->toArray();
    foreach ($arr as $key => $str)
    {
        $str = str_replace('"', NULL, $str); // REMOVE DOUBLE QUOTES
        $str = str_replace("\n", ' ', $str); // COLLAPSE NEWLINE TO BLANK
        $str = trim($str); // TRIM WHITESPACE FROM ENDS
        $arr[$key] = $str;
    }
    fputcsv($csv_file, $arr);
}

Open in new window

0
 
Ray PaseurCommented:
This is a data-dependent problem, but we don't have the data to test any proposed solution.  Please post a sample of the $results values, thanks.
0
 
mitaiAuthor Commented:
Is there not a way just to step though the array and do a string replace?  All data is string
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
Please post a sample of the $results values, thanks.
0
 
mitaiAuthor Commented:
how do I pull out sample data from the $result item?
0
 
Ray PaseurCommented:
You can use something like this:
echo '<pre>';
var_dump($results);

Open in new window

We do not need all of it - just enough to see the data elements that are troublesome.

Also, is this UTF-8?  If it is, you might need a byte-order mark for Excel.
0
 
mitaiAuthor Commented:
there are multiple tables that I use this for  here is one sample

is there a way to check each element in the $results (or result) and see if the object is a string, then do the remove quotes and linefeeds.  I can run a sample on my contact but it will take a while as there are 400+ thousand of them
0
 
Ray PaseurCommented:
Hmm... Has this application ever been used to produce a CSV file?  And what is the return value from $result->toArray() method?  I am guessing that it would be the array inside the data property, which is protected, so we cannot access it directly.  Would be good to confirm that.
0
 
mitaiAuthor Commented:
Here is the results from one of the impacted records in the contact file.  you will notice that the one field () has Initials=AA then has a line feed character.  This is causing excel to drop the remaining part of this record to the next line.  note I have sanitized the data here so if something doesn't look right with a string it has been replaced with XXXXX

  [2]=>
  object(Infusionsoft_Contact)#91 (5) {
    ["customFieldFormId"]=>
    int(-1)
    ["fields":protected]=>
    NULL
    ["table":protected]=>
    string(7) "Contact"
    ["data":protected]=>
    array(78) {
      ["Address1Type"]=>
      NULL
      ["Address2Street1"]=>
      string(19) "5815 N XXXXXlia Ave"
      ["Address2Street2"]=>
      string(5) "Apt 2"
      ["Address2Type"]=>
      NULL
      ["Address3Street1"]=>
      NULL
      ["Address3Street2"]=>
      NULL
      ["Address3Type"]=>
      NULL
      ["Anniversary"]=>
      NULL
      ["AssistantName"]=>
      NULL
      ["AssistantPhone"]=>
      NULL
      ["BillingInformation"]=>
      NULL
      ["Birthday"]=>
      NULL
      ["City"]=>
      string(7) "CXXXXXo"
      ["City2"]=>
      string(7) "CXXXXXo"
      ["City3"]=>
      NULL
      ["Company"]=>
      string(7) "AXXXXXe"
      ["AccountId"]=>
      int(0)
      ["CompanyID"]=>
      int(0)
      ["ContactNotes"]=>
      string(14) "Initials = AA
"
      ["ContactType"]=>
      string(4) "null"
      ["Country"]=>
      string(13) "United States"
      ["Country2"]=>
      string(13) "United States"
      ["Country3"]=>
      NULL
      ["CreatedBy"]=>
      int(1)
      ["DateCreated"]=>
      string(17) "20120806T16:20:18"
      ["Email"]=>
      string(21) "aXXXXXXXXXX@gmail.com"
      ["EmailAddress2"]=>
      NULL
      ["EmailAddress3"]=>
      NULL
      ["Fax1"]=>
      NULL
      ["Fax1Type"]=>
      string(4) "Work"
      ["Fax2"]=>
      NULL
      ["Fax2Type"]=>
      NULL
      ["FirstName"]=>
      string(14) "AXXXXXl XXXXXur"
      ["Groups"]=>
      string(111) "102,104,108,142,205,213,291,359,361,365,375,411,448,542,725,747,765,901,1043,1068,1149,1305,1323,1365,1409,1624"
      ["Id"]=>
      int(10)
      ["JobTitle"]=>
      NULL
      ["LastName"]=>
      string(6) "XXXXX"
      ["LastUpdated"]=>
      string(17) "20161017T15:54:49"
      ["LastUpdatedBy"]=>
      int(-1)
      ["Leadsource"]=>
      string(27) "www.fXXXXXcXXXXXer.com"
      ["LeadSourceId"]=>
      int(69)
      ["MiddleName"]=>
      NULL
      ["Nickname"]=>
      NULL
      ["OwnerID"]=>
      int(0)
      ["Password"]=>
      NULL
      ["Phone1"]=>
      string(14) "(773) 802-XXXX"
      ["Phone1Ext"]=>
      NULL
      ["Phone1Type"]=>
      string(4) "Work"
      ["Phone2"]=>
      NULL
      ["Phone2Ext"]=>
      NULL
      ["Phone2Type"]=>
      string(4) "Work"
      ["Phone3"]=>
      NULL
      ["Phone3Ext"]=>
      NULL
      ["Phone3Type"]=>
      NULL
      ["Phone4"]=>
      NULL
      ["Phone4Ext"]=>
      NULL
      ["Phone4Type"]=>
      NULL
      ["Phone5"]=>
      NULL
      ["Phone5Ext"]=>
      NULL
      ["Phone5Type"]=>
      NULL
      ["PostalCode"]=>
      string(5) "XXXXX"
      ["PostalCode2"]=>
      string(5) "XXXXX"
      ["PostalCode3"]=>
      NULL
      ["ReferralCode"]=>
      NULL
      ["SpouseName"]=>
      NULL
      ["State"]=>
      string(2) "IL"
      ["State2"]=>
      string(2) "Il"
      ["State3"]=>
      NULL
      ["StreetAddress1"]=>
      string(19) "XXXXX MaXXXXXa Ave"
      ["StreetAddress2"]=>
      string(5) "Apt 2"
      ["Suffix"]=>
      NULL
      ["Title"]=>
      NULL
      ["Username"]=>
      NULL
      ["Validated"]=>
      string(2) "-1"
      ["Website"]=>
      NULL
      ["ZipFour1"]=>
      NULL
      ["ZipFour2"]=>
      NULL
      ["ZipFour3"]=>
      NULL
    }
    ["appPoolAppKey":protected]=>
    string(22) "XXXXX.com"
  }
0
 
mitaiAuthor Commented:
:)  Ray you are the Man!!!
0
 
mitaiAuthor Commented:
Great work!  this makes things so much easier
0
 
Ray PaseurCommented:
Great!  Thanks for the points and thanks for using E-E!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.