SQL - Keep data in column together for exporting to excel.

In my SQL code I have the below code to remove LF and carriage returns.  However, this column, when copy and pasted to excel or exported to excel is creating additional columns for this field when I want all columns in 1 field.
	REPLACE ( REPLACE ( description, CHAR(13) , '' ) , CHAR(10) , ', ' ) AS Description 

Open in new window


Data example
Description
9/1/17 dho: Current LT is 3 wks., , *CC, LT: BONDED 25pc reels - 2wks - other suppliers for MOQ 1, AMU: 6, LPP: 286.50ea for reels - other suppliers 1: 350.00, LQP: 25      , LDP: 09.17.2015      ,


Below are going to different columns
LQP: 25
LDP: 09.17.2015      ,

Thanks
LVL 2
CipherISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Since there will be a comma in the data field now that you are building, you will need to wrap that in double quotes before opening / importing into Excel.  Try something like:

'"' + REPLACE ( REPLACE ( description, CHAR(13) , '' ) , CHAR(10) , ', ' ) + '"' AS Description 

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
That seems to work when I copy n paste from the grid results in SQL Server but not when I right click and export as CSV.
0
Bill PrewIT / Software Engineering ConsultantCommented:
What happens when you export, what does the output file look like, are the double quotes removed?

And what tool are you executing the SQL in?


»bp
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

CipherISAuthor Commented:
I'm using SQL Server Management Studio.  Testing the SQL Statement there then will call via .NET.

Looks perfect in SQL Server.

The double quotes are displayed in the column.  Like I said if i Select the data and copy into excel it seems to be fine.   Just when I right click and save as a comma delimited CSV file is the issue which makes sense.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Right, so in the CSV file that gets created, are the double quotes stripped off?


»bp
0
CipherISAuthor Commented:
When I view in SQL they are there.  In excel they are not there.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Right, but what about if you open the CSV file in Noetpad, are they there?


»bp
0
Bill PrewIT / Software Engineering ConsultantCommented:
I just did a simple test here and the CSV looked the way we want it to, with he double quotes in it. I did this:

select '"test1,test2"' as c1,'test3' as c2

and then Save As to a CSV.

CSV was:

"test1,test2",test3

And when I opened that CSV in Excel I got test1,test2 in A1, and then test3 in B1.

Isn't that what you are looking for?


»bp
0
CipherISAuthor Commented:
Yes.  Will try that also to see what I get.
0
CipherISAuthor Commented:
So, I'm digging deeper into this.  Looks like your solution is correct.

However, the app that stores data in the database allows for characters (e.g. tab, linefeed, carriage return, and who knows what else).  This seems to be what is causing me grief.   I have to find what the character are and remove them from the field.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Ah, that makes sense.  You have two of the common ones, try adding CHR(9) also to remove any tabs.


»bp
0
CipherISAuthor Commented:
Yep.  Is there syntax that will tell me what chr are in the field so I can see it?  Trying to determine what 1 of the chr's are.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Not really, easiest way might be to view the exported CSV in a hex editor.  A simple (but not terribly pretty) free one for windows is at:



»bp
0
CipherISAuthor Commented:
Figured it out.  This is part of the code

DECLARE @counter int = 10;

,CHAR(ASCII(SUBSTRING(PN, @counter, 1))) as [Character]
,ASCII(SUBSTRING(PN, @counter, 1)) as [ASCIIValue]

I would right a piece of code that would get the PN value then go character by character until counter = length of text.  But above was quick.

Problem was too many special characters in too many fields.

Thanks
0
Bill PrewIT / Software Engineering ConsultantCommented:
You can also use convert(varbinary, fldName) I forgot about that in SQL Server.


»bp
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.