Home › Forums › implementation › Implementation discussion ›

Exported CSV data has extra fields

Submitted by Guest on Wed, 06/02/2013 - 20:46
in

I built a clientEmailAddress report (see attached).  The SQL is:

select c.party_id as id, c.description as email, REPLACE(REPLACE(REPLACE(e.name, '\n', ''), '\r', ''),char(0),'') as name, REPLACE(REPLACE(REPLACE(e.description, '\n', ''), '\r', ''),char(0),'') as address from contacts as c join entities as e on c.party_id=e.entity_id
 where c.arch_short_name = 'contact.email' and e.arch_short_name='party.customerperson' and e.active=1;

If I run the SQL query (in say the MySQL Workbench), I get the data as expected, ie

 45805 aaaa.test[at]bigpond[dot]com  Aaatest,Aaaaa 123 Aaaa Street Shau Kei Wan Hong Kong Island  - 1234 5678
 

When the report is run, the printed data also has things as expected.

However, if I click the Export button, then the resulting csv file has extra fields as follows:

CID,Email,Name,Address
45805,aaaa.test[at]bigpond[dot]com ,"Aaatest,Aaaaa",
,,,123 Aaaa StreetShau Kei Wan Hong Kong Island  - 1234 5678
 

Note that the header line shows 4 fields, but a) there extra empty fields in the data lines between the Name and Address fields; b) there is a newline character after the comma following the Name field, and the Address data is not enclosed in quotes.

I think that this problem has to do with my use of the description field from the entities table without any removal of all non-printing characters.  However, as you can see from the SQL I have done my best to strip newlines and nulls from both the name and address fields.

Any ideas?

Regards, Tim G

‹ 15 concurrnet users Client Information Back-Ups ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Exported CSV data has extra fields

Submitted by tanderson on Thu, 07/02/2013 - 15:20.

It might be related to non-printing characters. I've adapted the following SQL from http://stackoverflow.com/questions/11535350/remove-all-or-particular-non...

   delimiter $$
drop function if exists mysql_replaceAllNonPrintableCharacters $$
CREATE FUNCTION mysql_replaceAllNonPrintableCharacters (data varchar(1024))
    RETURNS varchar(1024)
BEGIN
    declare i int default 0;
    declare finaldata varchar(1024) default '';
    set finaldata:=data;
    while i<31
        do set finaldata:=replace(finaldata,char(i),'');
        set i := i+1;
    end while;
    RETURN finaldata;
END $$

delimiter ;

 

 You use it as follows:

  
 select c.party_id as id,
    c.description as email,
    mysql_replaceAllNonPrintableCharacters(e.name) as name,
    mysql_replaceAllNonPrintableCharacters(e.description) as address
from contacts as c
join entities as e on c.party_id=e.entity_id
where c.arch_short_name = 'contact.email'
    and e.arch_short_name='party.customerperson' and e.active=1;

You probably want to replace '\n' and '\r' with space however.

 

-Tim A

Re: Exported CSV data has extra fields

Submitted by tanderson on Thu, 07/02/2013 - 15:27.

Better yet, try using the convert function:

 select c.party_id as id,
    c.description as email,
    convert(e.name using ascii) as name,
    convert(e.description using ascii) as address
from contacts as c
join entities as e on c.party_id=e.entity_id
where c.arch_short_name = 'contact.email'
    and e.arch_short_name='party.customerperson' and e.active=1;

-Tim A

Syndicate content