SQLTeam.com | Weblogs | Forums

How to create a store procedure for the script below to remove spaces in txt file with pipe delimited

sql2012

#1

Hello, I tried to use SQLCMD and it still has spaces in my txt file with pipe delimited.

command use:C:\Windows>sqlcmd -S bmprolawsql01 -d prolaw -s "|" -y -i "d:\sqldata\climat.sql" -o e:\import\newmatter.txt

my output file has spaces before and after the pipe delimiter.

USE prolaw

declare @s varchar(100)='(FILE';
select clientid
,ClientSort as ClientName
,'Y' as Enabled
,'N' as HIPPA
,matternum
,case
when charindex(@s,[description])>0
then left(cast([description] as varchar(max)),charindex(@s,[description])-1)
else [description]
end as MatterName
,'Y' as Enable
,'N' as HIPPA
,Status
,CASE
WHEN areaoflaw = 'Family Law' then 'FL'
WHEN areaoflaw = 'Workers Comp' then 'WC'
WHEN areaoflaw = 'Criminal' then 'CR'
WHEN areaoflaw = 'Corporate Business' then 'CB'
WHEN areaoflaw = 'Personal Injury' then 'PI'
WHEN areaoflaw = 'Litigation' then 'LI'
WHEN areaoflaw = 'Estate Matters' then 'EM'
WHEN areaoflaw = 'Miscellaneous' then 'MI'
WHEN areaoflaw = 'Appeals' then 'AP'
WHEN areaoflaw = 'Real Estate' then 'RE'
END as areaoflaw
,Status
--- , OpenedDate
from matters
where OpenedDate > DATEADD(DAY,-16,GETDATE())
and status = 'ACTIVE'
order by Clientid
,matterid
;


#2

You probably want "-W" instead of "-y" ... however ... that will, also, remove legitimate trailing spaces (as well as those used as filler)

If your aim is to export data you might be better off with BCP, rather than SQLCMD (in particular if you want to transfer data between two MS SQL databases then using the "native" data format flag will preserve the data columns exactly).


#3

Thanks Kristen, the problem I face is that my case statement which as "When" throws up errors as BCP report that when is not an internal command.

How can I get around that?


#4

Query is a bit fragile if all included on the command line (in a quoted string). Might be easier to put the query in a Stored Procedure and then your query is just

BCP "EXEC MySproc" queryout "e:\import\newmatter.txt" -T -t| -S bmprolawsql01 -d prolaw

rather than trying to in-line your query in the command line?