Solved

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

Posted on 2007-03-19
8
188 Views
Last Modified: 2010-03-20
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!
0
Comment
Question by:colorbok
  • 4
  • 2
  • 2
8 Comments
 
LVL 11

Expert Comment

by:flipz
ID: 18749624
you should be able to pull the column out such as

select   REPLACE(ColumnName, CHAR(13), '') as NewColumnName
0
 

Author Comment

by:colorbok
ID: 18749712
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
 
LVL 16

Accepted Solution

by:
rboyd56 earned 125 total points
ID: 18749778
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
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749800
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:colorbok
ID: 18749825
That worked.   Thanks rboyd56!

Thanks for trying flipz---I don't know why that didn't work.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749874
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
 
LVL 11

Expert Comment

by:flipz
ID: 18749876
Apparently the REPLACE function doesn't work on some column datatypes - go figure!
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18749903
For text columns you have to use readtext, updatetext and writetest. It is not an easy thing to do.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now