Export build wrong csv File / Linebreak after primary_adress_street

[eagleone]

Hello Folks,
i have a really strange problem.

I have a Sugar CE 4.51g instaaltion with over 50.000 Leads inside. In the beginnig everything works fine with importing and exporting the Leads but since last Dezember, the last two Weeks of the last Year I have a Problem with the Export of 3 or 4 Users from about over 100. The Leads from this 3 or 4 Users have a linebreak into the csv Export File and everytime after the "column" "primary_adress_street".

I changed my Lead.php SQL Query so that I only get this colums i want to. (I did this today long time after i get the problem) and so i found out, that i can solve the problem, when i drop the colum with the street. If i do so everything works fine again.
And i noticed, that this users have "UTF-8" as Export Standard. Every else User has the "ISO-8859-1" Standard.

So maybe that was the reason for the wrong data into the DB but i want to ask if somebody can help me and tell me if i´m right with my idea and the second, i need an idea or solution to fix the damaged primary street Entrys in the DB. That are than 2500...

I REALLY NEED URGENT HELP !!! PLEASE HELP ME !!!

[dpatech]

The data is correct because Sugar allows multiple lines separated by CRLF in the primary_address_street field. Unfortunately they do not account for this in the export. You can fix this in the Leads.php create_export_query function by referencing the primary_address_street fields explicitely using the syntax shown below. This will remove all CRLF values and replace them with a single space so you can export the data correctly. To do this, you will have to take the reference to "Leads.*" if you haven't already done this and replace it with a reference to each field that you want.


replace(leads.primary_address_street,"\r\n"," " ) as primary_address_street

[eagleone]

Hello dpatech,

thank you for your help.

But i need a little more help.

The code i changed with help of "markku" is:

$query = "SELECT
leads.date_entered, leads.date_modified, leads.assigned_user_id, leads.first_name, leads.last_name, leads.do_not_call, leads.phone_home, leads.phone_mobile, leads.phone_work, leads.email1, leads.primary_address_postalcode, leads.primary_address_street, leads.primary_address_city, leads.alt_address_postalcode, leads.alt_address_street, leads.alt_address_city,

users.user_name assigned_user_name";

what exactly i have to do ???

I don´t know where i have to place your code.

[dpatech]

Ok, change the query to this....you need to use the "replace" function on both the primary and alt street addresses as shown below.

$query = "SELECT
leads.date_entered, leads.date_modified, leads.assigned_user_id, leads.first_name, leads.last_name, leads.do_not_call, leads.phone_home, leads.phone_mobile, leads.phone_work, leads.email1, leads.primary_address_postalcode,
replace(leads.primary_address_street,"\r\n"," " ) as primary_address_street,
leads.primary_address_city, leads.alt_address_postalcode,
replace(leads.alt_address_street,"\r\n"," " ) as alt_address_street,
leads.alt_address_city,
users.user_name assigned_user_name";