February 10, 2006 at 3:24 am
Hi People,
I'd really appreciate some help here.
When I run a query on one of the DBs I work with, and display the results to GRID, it all looks lovely. I save the report and import it to EXCEL and it all goes haywire. When I open the .rpt file in a text editor, the data is mis-aligned.
There's obviously TABS and NEWLINE (CRLF) hidden in there which throws the "Data Import" process in EXCEL.
Does anyone have an exaple of how I can strip this stuff out, or failing that, a way to export to EXCEL that won't suffer the same fate?
Cheers all,
Tim
February 10, 2006 at 5:18 am
When you say "text field", do you mean a field of datatype varchar? Then use something like this:
select replace(replace(colname, char(9), ''), char(13) + char(10), '')
from table
If you mean a field of datatype text, it's more complicated. The statement below will return the first 8000 characters without newlines and tabs:
select replace(replace(cast(substring(colname, 1, 8000) as varchar), char(9), ''), char(13) + char(10), '') from table
February 10, 2006 at 6:28 am
Thanks Jesper, you is the man.
I knew of the REPLACE function, but hadn't thought about the fact that tab & newline have char values.
I really appreciate your help, many thanks and have a great weekend!
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply