SQLTeam.com | Weblogs | Forums

String field after colon creating extra row when exporting to Excel


#1

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

SELECT STRING
FROM TEXTTBL

Thank you all


#2

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

IOW just change the colon to a blank


#3

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


#4

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

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

#5

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.


#6
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.


#7

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


#8

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.


#9

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


#10

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.


#11

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.


#12

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


#13

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.


#14

Thank you, Kristen