x

Removing white spaces to export to CSV

Hi Everyone!

I have a query I'm running to remove white spaces because TRIM was not working. The query is:

SELECT SUBSTRING(Fulldescription,1,LEN(Fulldescription) - CHARINDEX(' ', Reverse(FullDescription))) AS FullDescription 

FROM Items

This removes the white spaces and aligns the items in the CSV format with an exception of one item. If I run the query in SSMS and copy and paste the data results the spaces are no longer there. But during export the item is splitting into 3 columns, though in SSMS it is one column.

Can someone assist me in finding a way to remove trailing spaces with what I have or maybe I need to try a different way? (Minus using TRIM because those options did not work.)

Any help is greatly appreciated!
more ▼

asked Jul 11, 2012 at 06:32 PM in Default

Noonies gravatar image

Noonies
1.2k 57 64 65

can you post sample data and the field definition?
Jul 11, 2012 at 08:04 PM Pavel Pawlowski

CREATE TABLE [dbo].[Items]( [RecKey] [nvarchar](15) NOT NULL, [FullDescription] [nvarchar](200) NULL, [PrimarySupNid] [int] NULL )

INSERT INTO [dbo].[Items] ([RecKey] ,[FullDescription] ,[PrimarySupNid]) VALUES ('11135' ,'Jon Riverst/Bost/Black Tied Dyeds, YellowOra, Colored Tankts 10.8 Med' ,790)

GO

This is the item causing me problems. Where the Med is, we have the same item with Lar and Sma which also has the same issue.

Please let me know if you need anything additional. Thanks for responding so quickly.
Jul 11, 2012 at 08:19 PM Noonies
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It's because the text itself contains commas. So when you export it in comma separated file, then the application which imports the data will handle it as 3 separate columns.

You will have to use another separator (eg. semicolon or tab). For Tab delimited file you have the option directly in SSMS. When exporting select the tab delimited format.

Other option would be text qualify the strings by putting them into double quotation marks if the importing application can handle this. You can achieve this easily by the QUOTENAME function

SELECT QUOTENAME(SUBSTRING(Fulldescription, 1, LEN(Fulldescription) - CHARINDEX(' ', Reverse(FullDescription))), '"') AS FullDescription 
FROM [dbo].[Items]
more ▼

answered Jul 11, 2012 at 08:46 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

OMG...I totally didn't even think about the comma delimited part. UGH! Let me look at changing the separator. I will follow up shortly.
Jul 11, 2012 at 08:52 PM Noonies
Thanks again Pavel! Saved the day for me. I ended up using the QUOTENAME and that worked perfect.
Jul 12, 2012 at 08:47 PM Noonies

@Noonies I guess you owe accepting Pavel's answer then ;) But I must also add that QUOTENAME can handle only 128 characters and your FullDescription can hold 200. So whenever the characters would be greater than 128, the data returned would be NULL.

Also, your white space removal process is horrible and it can actually truncate the data after the last whitespace. For instance in your example 'Jon Riverst/Bost/Black Tied Dyeds, YellowOra, Colored Tankts 10.8 Med' is truncated to 'Jon Riverst/Bost/Black Tied Dyeds, YellowOra, Colored Tankts 10.8'. Consider using LTRIM/RTRIM/REPLACE whatever fulfill your needs.
Jul 13, 2012 at 06:28 AM Usman Butt
@Usman Butt - Thanks for your feedback! I had tried LTRIM/RTRIM/REPLACE prior to posting and these were not working for me so I was not sure where to go from there.
Jul 13, 2012 at 03:21 PM Noonies

@Noonies You could have shared that problem with us. May be we could have help.

But I am happy that you accepted the answer unlike some other users. This encourages the volunteers more and also help maintaining the site.
Jul 13, 2012 at 03:25 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927
x369

asked: Jul 11, 2012 at 06:32 PM

Seen: 1764 times

Last Updated: Jul 13, 2012 at 03:31 PM