Hidden characters in fields and exporting to Excel

[wbodger]

I am working on creating an output file script that takes aggregated data and spits out an excel file (creating a data dictionary for statisticians). However, there are some hidden characters that are causing issues when exporting to Excel. Is there a way to handle (remove) those characters without explicitly knowing what they are?

Thanks!

wb

 

[bborissov]

All hidden chars are in range from CHAR(0) TO CHAR(31), so you can write a function that REPLACE() these chars with empty string.
Usually the only hiffen chars that you have in such fields are the CR + LF (CHAR(10) + CHAR(13)). Excel didn't like the CHAR(13). It uses only CHAR(10) as end of the line. So first you can try first this:

SELECT REPLACE(YourCharField,CHAR(13),'') AS YourCharField
FROM YourTable
WHERE ....

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wbodger]

Thank you, I will try that!

wb

 

[markros]

Take a look at this blog post

http://beyondrelational.com/modules.../cleanse-all-character-fields-in-a-table.aspx

(this removes certain bad characters from all char data columns) and this

http://beyondrelational.com/modules/2/blogs/78/posts/11141/remove-bad-characters-from-a-string.aspx

(this leaves only 'good' characters)

PluralSight Learning Library

 

[wbodger]

thanks!