Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

PHP strip quotes and line feeds out of array

Posted on 2016-10-19
13
Medium Priority
?
210 Views
Last Modified: 2016-10-19
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

0
Comment
Question by:mitai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41850425
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
 

Author Comment

by:mitai
ID: 41850492
Is there not a way just to step though the array and do a string replace?  All data is string
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41850495
Please post a sample of the $results values, thanks.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:mitai
ID: 41850543
how do I pull out sample data from the $result item?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41850564
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
 

Author Comment

by:mitai
ID: 41850599
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41850644
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
 

Author Comment

by:mitai
ID: 41850648
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 41850659
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
 

Author Comment

by:mitai
ID: 41850688
:)  Ray you are the Man!!!
0
 

Author Closing Comment

by:mitai
ID: 41850691
Great work!  this makes things so much easier
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41850695
Great!  Thanks for the points and thanks for using E-E!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

618 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