Solved

PHP strip quotes and line feeds out of array

Posted on 2016-10-19
13
39 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
  • 6
  • 6
13 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
Is there not a way just to step though the array and do a string replace?  All data is string
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please post a sample of the $results values, thanks.
0
 

Author Comment

by:mitai
Comment Utility
how do I pull out sample data from the $result item?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
:)  Ray you are the Man!!!
0
 

Author Closing Comment

by:mitai
Comment Utility
Great work!  this makes things so much easier
0
 
LVL 108

Expert Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to count occurrences of each item in an array.
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 …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now