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

PHPMySQL Server

Last Comment

Ray Paseur

8/22/2022 - Mon

Ray Paseur

10/19/2016

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.

mitai

10/19/2016

ASKER

Is there not a way just to step though the array and do a string replace? All data is string

Ray Paseur

10/19/2016

Please post a sample of the $results values, thanks.

Your help has saved me hundreds of hours of internet surfing.

fblack61

mitai

10/19/2016

ASKER

how do I pull out sample data from the $result item?

Ray Paseur

10/19/2016

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.

mitai

10/19/2016

ASKER

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Ray Paseur

10/19/2016

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.

mitai

10/19/2016

ASKER

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"
}

ASKER CERTIFIED SOLUTION

Ray Paseur

10/19/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

mitai