Solved

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

Posted on 2007-03-19
8
190 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 16

Expert Comment

by:rboyd56
ID: 18749800
0
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

789 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