Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CipherIS

ASKER

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.
Avatar of Bill Prew
Bill Prew

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
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.
Right, so in the CSV file that gets created, are the double quotes stripped off?


»bp
When I view in SQL they are there.  In excel they are not there.
Right, but what about if you open the CSV file in Noetpad, are they there?


»bp
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes.  Will try that also to see what I get.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ah, that makes sense.  You have two of the common ones, try adding CHR(9) also to remove any tabs.


»bp
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can also use convert(varbinary, fldName) I forgot about that in SQL Server.


»bp