Solved

PHP strip quotes and line feeds out of array

Posted on 2016-10-19
13
89 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 110

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 110

Expert Comment

by:Ray Paseur
ID: 41850495
Please post a sample of the $results values, thanks.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

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

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 110

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 110

Accepted Solution

by:
Ray Paseur earned 500 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 110

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 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