SQLTeam.com | Weblogs | Forums

String field after colon creating extra row when exporting to Excel

Hello all,

I have a string field that contains long text. The issue is when I convert that field to an excel, if a text has colon in between the string, the next text/string after the colon goes to the second row in excel. How do I update this field so when I export it to an excel, it stay on one row?

Here is my sample query


Thank you all

select replace(string, ':', ' ') from texttbl

IOW just change the colon to a blank

Thanks gbritton, but the same issue still there when I export it to excel. the text after colon goes to next row on excel.

I think there is perhaps a Line Break in the text-string?

select replace(replace(string, CHAR(13), ' ') , CHAR(10), ' ') from texttbl
1 Like

Hi Kristen,

I think there is perhaps a Line Break in the text-string? and that is correct.

I tried and still doing the same issue. Text after colon goes to next row.

Select quotename(string, char(34)) from texttbl;

If you wrap the column in double-quotes - Excel will treat that column as a single cell and will not wrap the results to another cell or row.

interesting, thanks. Do you, also, have to double-up any existing embedded double-quotes? or does Excel leave-alone anything not obviously a "wrapper"?

Not sure - but I have seen issues with wrapping in Excel due to several different characters. Commas are one that I see causing the most issues.

Hi Jeff, the quotename(string, char(34)) worked, but wont work with long string.. Thanks Jeff

How are you getting the data "out" and then into Excel?

if you are Copy & Pasting from a SQL tool then it might be that you have reached the max column length set (in CONFIG) for that SQL tool.

Not sure what you mean...are you getting an error or is it truncating or something else? Excel does have a limitation on how much data can exist in a single cell - if you are exceeding that limit then you need to figure out how to reduce the size of the column.

1 Like

Query from Kristen select replace(replace(string, CHAR(13), ' ') , CHAR(10), ' ') worked for me! Thanks Kristen

OK, but you've then lost all the line breaks in the text. You may not want them I suppose, but I'd fine a different way to "transport" the data into Excel so that the data wasn't modified in that way - i.e. if someone Copy & Pastes a single cell out of XLS they will have the benefit of the original line breaks.

1 Like

Thank you, Kristen