Query Result To Excel with Messed Up Rows (SSMS 2012)


When you export your query result from SSMS 2012 (not SSMS 2008) to a text file or copy and paste the result (with header), you may run into problem with columns (such as varcahr(max)…) that include Tab — char(9), Line feed — char(10) or Carriage return — char(13). You have garbled rows due to these invisible chars.

A quick fix is to remove them by using nested REPLACE functions in your SELECT query.
Here is and example:


--Sample code
--

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),''), CHAR(13),'') ,CHAR(9),'')  as yourcolumn

--
 

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),”), CHAR(13),”) ,CHAR(9),”) as yourcolumn

If you want to remove them forever, you can use an UPDATE command to update these columns in your source table.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s