• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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
0
CipherIS
Asked:
CipherIS
  • 8
  • 7
5 Solutions
 
Bill PrewCommented:
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
 
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 PrewCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 PrewCommented:
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 PrewCommented:
Right, but what about if you open the CSV file in Noetpad, are they there?


»bp
0
 
Bill PrewCommented:
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 PrewCommented:
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 PrewCommented:
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 PrewCommented:
You can also use convert(varbinary, fldName) I forgot about that in SQL Server.


»bp
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now