Removing boxes from Excel spreadsheet--data pulled from SQL View

I have a SQL view that I'm pulling into Excel using an ODBC connection/Microsoft Query.   My issue is that the notes field has boxes/squares wherever there were carriage returns in the SQL notes field.

Is there a way in SQL to strip those out in my view before bringing them into Excel?   Or is there a function I could write in a macro to do the same thing with the data is refreshed?

Thanks!
colorbokAsked:
Who is Participating?
 
rboyd56Connect With a Mentor Commented:
Try this in a macro in Excel:

Worksheets("Sheet1").Columns("I").Replace What:=vbCrLf, Replacement:="?"

Should be self explanatory, the vbCrLf is the VB code for a carriage return and just replace the ? with whatever you want in place of the carriage return.

This came from:

http://www.gossamer-threads.com/forum/General_C8/Databases_and_SQL_F36/Delete_Carriage_return_query_P259718/
0
 
flipzCommented:
you should be able to pull the column out such as

select   REPLACE(ColumnName, CHAR(13), '') as NewColumnName
0
 
colorbokAuthor Commented:
I receive the following error when I run that REPLACE function:

Server:  Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

Please advise.   Thanks!
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
colorbokAuthor Commented:
That worked.   Thanks rboyd56!

Thanks for trying flipz---I don't know why that didn't work.
0
 
rboyd56Commented:
The replae function does not work on a SQL Server text column. It will only work on a char, varchar, nchar or nvarchar column
0
 
flipzCommented:
Apparently the REPLACE function doesn't work on some column datatypes - go figure!
0
 
rboyd56Commented:
For text columns you have to use readtext, updatetext and writetest. It is not an easy thing to do.
0
All Courses

From novice to tech pro — start learning today.